Returning multiple columns using Case in Select Satement in Oracle

Question

I have sceanrio where I need to return values ​​from different subqueries based on the condition in the main selection. I tried to use Case, but the problem is that Case does not support multiple columns. Is there any work with this, or is there any other way to achieve this.

My script in a simplified request

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 is another way to write it, which may address problems accessing the second table more time 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';

, , ; DUAL . SELECT a, b FROM otherTable WHERE someCondition. , WHERE.

Answer 2

, , , :

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';

, , , .

Answer 3

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

, , - (2 ) , : 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 4

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

Answer 5

The best approach for me is to use REGEXP_REPLACE. Have one row returned from the case statement, and in the external query, select the tokenize statement of the rows in different fields.