Using Case statement instead of Union

Database

Using Case statement instead of Union

I have a table which has data like:

C1|C2|C3|C4

a|||

|b||

||c|

|d|e|

I want output like


C

---

a

b

c

d

e

I do not want to use union and tried using case statement.

CASE WHEN C1 IS NOT NULL THEN C1

WHEN C2 IS NOT NULL THEN C2

WHEN C3 IS NOT NULL THEN C3

WHEN C4 IS NOT NULL THEN C4

END

This gave me

C

---

a

b

c

d

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.

5 REPLIES
Junior Contributor

Re: Using Case statement instead of 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

select 
case
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

Re: Using Case statement instead of Union

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.

Junior Contributor

Re: Using Case statement instead of Union

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.

Enthusiast

Re: Using Case statement instead of Union

Hi Dnoeth,

Could you please explain this helper table here?

Thank You!

Junior Contributor

Re: Using Case statement instead of Union

Hi Amit,

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