All Forums Database
sbadnikar 4 posts Joined 12/08
22 Mar 2015
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.

dnoeth 4628 posts Joined 11/04
23 Mar 2015

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

 

Dieter

sbadnikar 4 posts Joined 12/08
23 Mar 2015

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.

dnoeth 4628 posts Joined 11/04
23 Mar 2015

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.

Dieter

bhartiya007 29 posts Joined 03/14
21 May 2015

Hi Dnoeth,
Could you please explain this helper table here?
Thank You!

@Amit

dnoeth 4628 posts Joined 11/04
21 May 2015

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
 

Dieter

You must sign in to leave a comment.