How do I assign a value to a variable using a select statement and use it in a SQL*Plus script, something like this?
VARIABLE FullCatCode VARCHAR2(7)
exec :FullCatCode := (SELECT CatCode from draw_catcodes where series = 123 and base = 158);
SELECT :FullCatCode || '-' || Other stuff... from table_name...
EDIT: @AlexPoole, Sorry for the imprecise problem statement. I am extracting 50 columns of data from a 280K record dataset. I am assigning sequence nextval's to each record based on a substring from a secondary table (draw_catcodes). I am running the SELECT statement in SQL*Plus, spooling to a CSV file. The SQL now looks like this (testing your second recommendation below):
COLUMN CatCode NEW_VALUE FullCatCode;
SELECT
(CASE d.image WHEN 0 THEN 'NoImage.pdf'
ELSE lower(d.prefix || lpad(d.series,3,0) || lpad(d.base,3,0) || lpad(d.suffix,2,0) || lpad(d.rev,2,0) || '.pdf') END) as "Filename",
(SELECT EngDiscipline from draw_catcodes c where d.series = c.series and d.base = c.base) as "EngDiscipline",
(SELECT CatCode from draw_catcodes c where d.series = c.series and d.base = c.base),
SELECT &FullCatCode || '-' ||
lpad((CASE substr(&FullCatCode,0,3)
WHEN 'AEG' THEN (SELECT AEG_seq.NEXTVAL FROM DUAL)
WHEN 'ARY' THEN (SELECT ARY_seq.NEXTVAL FROM DUAL)
WHEN 'BBR' THEN (SELECT BBR_seq.NEXTVAL FROM DUAL)
ELSE 0
END),6,0) as "ItemID",
upper(d.prefix || '-' || lpad(d.series,3,0) || '-' || lpad(d.base,3,0) || '-' || lpad(d.suffix,2,0)) as "LegacyID",
...
from tablename where ...
I started with a Stored Function that 'Upserted' a table of sequences (can't perform DML from a Function), tried a Stored Procedure with an OUT variable (couldn't call the procedure from within the SQL SELECT), now trying sequences in a CASE statement (can't figure out how to use variables as described above)... Any suggestions would be much appreciated!
EDIT: @AlexPoole, Since the variables won't work in this context, I just SELECT the value I'm after directly, then use a CASE statement to specify the proper sequence. The syntax is not right though, as I am getting an ORA-00933: SQL command not properly ended error on the next line:
SELECT ((SELECT CatCode from draw_catcodes c where d.series = c.series and d.base = c.base) || '-' ||
lpad((CASE substr((SELECT CatCode from draw_catcodes c where d.series = c.series and d.base = c.base),0,3)
WHEN 'AEG' THEN AEG_seq.NEXTVAL
WHEN 'ARY' THEN ARY_seq.NEXTVAL
WHEN 'BBR' THEN BBR_seq.NEXTVAL
...
WHEN 'SPR' THEN SPR_seq.NEXTVAL
WHEN 'SRL' THEN SRL_seq.NEXTVAL
ELSE 0
END),6,0)) as "ItemID" FROM DUAL,
upper(d.prefix || '-' || lpad(d.series,3,0) || '-' || lpad(d.base,3,0) || '-' || lpad(d.suffix,2,0)) as "LegacyID",
EDIT: @AlexPoole, I added the JOIN and cleaned up the SELECT but now get an ORA-02287: sequence number not allowed here
(c.CatCode || '-' ||
(SELECT lpad(
(CASE substr(c.CatCode,0,3)
WHEN 'AEG' THEN AEG_seq.NEXTVAL
WHEN 'ARY' THEN ARY_seq.NEXTVAL
WHEN 'BBR' THEN BBR_seq.NEXTVAL
WHEN 'BSY' THEN BSY_seq.NEXTVAL
...
WHEN 'SDR' THEN SDR_seq.NEXTVAL
WHEN 'SLC' THEN SLC_seq.NEXTVAL
WHEN 'SLD' THEN SLD_seq.NEXTVAL
WHEN 'SMS' THEN SMS_seq.NEXTVAL
WHEN 'SPP' THEN SPP_seq.NEXTVAL
WHEN 'SPR' THEN SPR_seq.NEXTVAL
WHEN 'SRL' THEN SRL_seq.NEXTVAL
ELSE 0 END ),6,0) FROM DUAL)) as "ItemID",
...
FROM md_draw d
join draw_catcodes c on d.series = c.series and d.base = c.base
order by lpad(d.series,3,0), lpad(d.base,3,0), lpad(d.suffix,2,0);
Any suggestions?
EDIT: @AlexPoole, You're right, I removed the subquery containing the sequence call as you detailed it, but still get the ORA-02287: sequence number not allowed here error:
SELECT
(CASE d.image WHEN 0 THEN 'NoImage.pdf'
ELSE lower(d.prefix || lpad(d.series,3,0) || lpad(d.base,3,0) || lpad(d.suffix,2,0) || lpad(d.rev,2,0) || '.pdf') END) as "Filename",
c.EngDiscipline as "EngDiscipline",
c.CatCode || '-' || lpad(CASE substr(c.CatCode,0,3)
WHEN 'AEG' THEN AEG_seq.NEXTVAL
WHEN 'ARY' THEN ARY_seq.NEXTVAL
WHEN 'BBR' THEN BBR_seq.NEXTVAL
...
WHEN 'SPP' THEN SPP_seq.NEXTVAL
WHEN 'SPR' THEN SPR_seq.NEXTVAL
WHEN 'SRL' THEN SRL_seq.NEXTVAL
ELSE 0 END,6,'0') as "ItemID",
upper(d.prefix || '-' || lpad(d.series,3,0) || '-' || lpad(d.base,3,0) || '-' || lpad(d.suffix,2,0)) as "LegacyID",
...
FROM md_draw SAMPLE (1) d
join draw_catcodes c on d.series = c.series and d.base = c.base
order by c.ccProgram, lpad(d.series,3,0), lpad(d.base,3,0), lpad(d.suffix,2,0);