Hello all,
I urgently need to run a query in SQL Developer today but one of my 'case... when' clauses which I had to modify slightly is causing the query to ask for a Substitution Variable. I have never seen this before and can't see any fault in my code so I'm confused and getting frustrated why this problem has suddenly appeared today on the reporting run.
The original clause works fine:
,case when lower(class_name) like ('%funding note%')
then 0
else orig_bal
end orig_bal_amd
I altered it to this:
,case when geo_segment_name in ('NorthAmer')
then case when bus_grp_typ in ('CMBS')
then case when c_bond_type_desc not in ('P&I')
then c_notional_amount
else orig_bal
end
else case when lower(class_name) like ('%funding note%')
then 0
else orig_bal
end
end
else orig_bal
end orig_bal_amd
or this:
,case when geo_segment_name in ('NorthAmer')
and bus_grp_typ in ('CMBS')
then case when c_bond_type_desc not in ('P&I')
then c_notional_amount
else orig_bal
end
else case when geo_segment_name in ('NorthAmer')
and lower(class_name) like ('%funding note%')
then 0
else orig_bal
end
end orig_bal_amd
also strangely this, which reverts the above to be syntactically the same as the first simpler (working) cause, is also causing the substitution problem.
,/*case when geo_segment_name in ('NorthAmer')
and bus_grp_typ in ('CMBS')
then case when c_bond_type_desc not in ('P&I')
then c_notional_amount
else orig_bal
end
else */case --when geo_segment_name in ('NorthAmer')
when lower(class_name) like ('%funding note%')
then 0
else orig_bal
end orig_bal_amd
-- end orig_bal_amd
does anyone have any idea why this is happening? i really need to get this data run and I have no idea what is causing this issue.
thank you very much in advance for any ideas.
jon
Edited by: berlinhammer on 04-Jan-2011 05:17