returning multiple columns using Case in Select Satement in Oracle

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

From stackoverflow

• 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.

  Dinesh Manne : I wanted to avoid that approach since it would mean executing the select statement multiple times which will be a performance impact, another method which i was looking to was treat the two queries as tables and then join them with the main table

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

  Dinesh Manne : Thats is what i thought initially, but the problem with that is you need to execute the same select statement multiple times which will be a performance impact

• 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.

  Dinesh Manne : I kind of already implemented something similar to what you are saying. My sub queries have different table sets(each sub query had atleast 2 tables) ... (More in next comment)

  Dinesh Manne : (Continued from Previous) ... what i did was to treat the sub queries as tables and did outer joins across the main query and sub queries, and then used case in the main query,the only disadvantage of this approach was that you hit both the sub queries even when you know that data does not exist.

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