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

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 2

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)
)