returning multiple columns using Case in Select Satement in Oracle

Question

I have a sceanrio where i need to retreive values from different sub queries based on a condition in a main select statement. i was trying to use Case, but the problem is that Case does not support multiple columns. Is there any work around to this, or is there any other way to acheive this.

My scenario in a simplified query

select col1,col2,
case when col3='E01089001' then 
        (select 1,3 from dual)
    else
        (select 2,4 from dual)
end
from Table1
where col1='A0529';

Answer 1

If each case only allows one column, then you probably need two cases:

select col1,col2,
case when col3='E01089001' then 
    (select 1 from dual)
else
    (select 2 from dual)
end,
case when col3='E01089001' then 
    (select 3 from dual)
else
    (select 4 from dual)
end
from Table1
where col1='A0529';

I hope I don't need to tell you that this sort of stuff doesn't scale very well when the database tables become large.

Answer 2

Case does support multiple columns in the conditional check

CASE WHEN A=X AND B=Y THEN ... END

What you are trying to do in your example is return a table (2 columns) into a resultset that expects one column: col1, col2, (col3,col4).

You need to return them separately: col1, col2, col3, col4

select
col1,
col2,
case when col3='E01089001' then (select 1 from dual) else (select 3 from dual) end,
case when col3='E01089001' then (select 2 from dual) else (select 4 from dual) end
from Table1 where col1='A0529';

Answer 3

Here's another way of writing it which may address concerns about accessing the second table more times than necessary.

select col1,col2,
case when col3='E01089001' then 1 else 2 end,
case when col3='E01089001' then 3 else 4 end
end
from Table1, dual
where col1='A0529';

Your example uses an obviously simplified subquery, so this version looks kind of silly as-is; there's no reason to join with DUAL at all. But in your real query you presumably have a subquery like SELECT a, b FROM otherTable WHERE someCondition. So you would want to use the actual column names instead of numeric literals and the actual table name instead of dual, and move the subquery conditions into the final WHERE clause.

Answer 4

A quick and dirty solution.

select dummy,substr(c,1,instr(c,',')-1) c1,substr(c,instr(c,',')+1) c2
from (
select dummy,
case when dummy='X' then 
        (select 1||','||3 from dual)
end c
from (select * from dual)
)