I have a table which has data like:
I do not want to use union and tried using case statement.
I dont want to miss out on the value e in C3 column because C2 satisfied the condition first.
Please suggest a way to have all values in one column without using union.
Why don't you want utilize UNIONs?
For CASE you need to cross join to a table containing one row per column, e.g. n = 1,2,3,4
when helper.n = 1 then C1
when helper.n = 2 then C2
when helper.n = 3 then C3
when helper.n = 4 then C4
end as C
from tab cross join helper
where C is not null
Thanks for your reply @dnoeth !
I didn't want to use union because I was running out of spool space while using 3 unions.
I'll try to optimise the union query and collect some stats to see how it goes.
Collect Stats will not help for UNION (or CASE).
Do you really need a UNION instead of UNION ALL?
This will add a DISTINCT step with a lot of overhead, distribution & sort.
And you must add DISTINCT to the CASE version, too.
Or you simply ask your DBA to increase your spool limit.
helper is a table with numbers in it, this might be a generic on with number between 0 and whatever_max_you_need or in the above case exactly 4 rows with numbers 1,2,3,4