Skip to Main Content
  • Questions
  • How to return multiple columns in case statement

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Eva.

Asked: April 10, 2018 - 11:27 am UTC

Last updated: November 25, 2021 - 3:22 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

I have query like:

select case when 1 in (1,2,3) then
(select 'abc' as "name 1",'xyz' as "name 2" from dual)
else 'pqr' end from dual; 


How can I return two columns if a condition is satisfied in case?

and Connor said...

The short answer is you can't. The longer answer is you can do things like:

- Concatenate all the columns into one with a known separator. Then split them out afterwards
- Load the values to an XML document and extract out as needed

select  case
          when 1 in ( 1, 2, 3 ) then 
            xmltype ( cursor (
              select 'abc' as "name 1", 'xyz' as "name 2"
              from   dual
              )
            )
          else xmltype ( cursor ( 
            select 'pqr' as "name 1"
            from   dual
            )
          )
        end
from   dual;

CASEWHEN1IN(1,2,3)THENXMLTYPE(CURSOR(SELECT'ABC'AS"NAME1",'XYZ'AS"NAME2"FROMDUAL))ELSEXMLTYPE(CURSOR(SELECT'PQR'AS"NAME1
------------------------------------------------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
  <ROW>
    <name_x0020_1>abc</name_x0020_1>
    <name_x0020_2>xyz</name_x0020_2>
  </ROW>
</ROWSET>


and so on.

But these are all fiddly. It's generally easier to have two case expressions with the second returning null in the else:

select  case
          when 1 in ( 1, 2, 3 ) then 
            'abc' 
          else 'pqr' 
        end "name 1",
        case
          when 1 in ( 1, 2, 3 ) then 
            'xyz'
          else null 
        end "name 2"
from   dual;

name 1   name 2   
abc      xyz  

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Or conditional joins

Duke Ganote, April 10, 2018 - 4:39 pm UTC

select object_id
     , nvl(a."name 1",b."name 1") as "name 1"
     , nvl(a."name 2",b."name 2") as "name 2"
  from dba_objects 
  left outer
  join (select 'abc' as "name 1",'xyz' as "name 2" from dual) a
    on mod(object_id,2) = 1
  left outer
  join (select 'pqr' as "name 1",NULL as "name 2" from dual) b
    on mod(object_id,2) = 0
 where object_id < 25
  order by 1;

 OBJECT_ID nam nam
---------- --- ---
         6 pqr    
         7 abc xyz
         8 pqr    
         9 abc xyz
        12 pqr    
        13 abc xyz
        14 pqr    
        15 abc xyz
        16 pqr    
        17 abc xyz
        23 abc xyz


Chris Saxon
April 10, 2018 - 5:00 pm UTC

Yep, you could do that too.

LATERAL joins

Kim Berg Hansen, November 22, 2021 - 9:53 am UTC

I often see what what devs want is a "scalar subquery" that returns multiple columns, like this:

select
   d.deptno, d.dname
 , case
      when d.dname = 'SALES' then
         (
            -- DOES NOT WORK
            select count(*) as cnt_emp, max(sal) as max_sal
            from scott.emp e
            where e.deptno = d.deptno
         )
   end as sales_info
from scott.dept d
order by d.deptno;


Which does not work - it raises error ORA-00913: too many values.

That's where LATERAL in my opinion is a great alternative, like this (using classic Oracle (+) syntax):

select
   d.deptno, d.dname
 , sales_info.cnt_emp, sales_info.max_sal
from scott.dept d, LATERAL (
   select count(*) as cnt_emp, max(sal) as max_sal
   from scott.emp e
   where d.dname = 'SALES'
   and e.deptno = d.deptno
)(+) sales_info
order by d.deptno;


Or the same using the ANSI syntax OUTER APPLY:

select
   d.deptno, d.dname
 , sales_info.cnt_emp, sales_info.max_sal
from scott.dept d
OUTER APPLY (
   select count(*) as cnt_emp, max(sal) as max_sal
   from scott.emp e
   where d.dname = 'SALES'
   and e.deptno = d.deptno
) sales_info
order by d.deptno;


Both work fine and give the result that is the intention of the non-working scalar subquery version above.

Lateral inline views are not much used in the wild, but work great for cases like this.
Connor McDonald
November 23, 2021 - 7:03 am UTC

Indeed.

I'm an "old school" Oracle join-er but LATERAL and OUTER APPLY are underused and undervalued

Lateral / OUTER APPLY

Dieter, November 23, 2021 - 12:48 pm UTC

Thank you Kim!

for pointing out the outer join syntax for the lateral join: the (+) after the subquery.
I had not figured out how to use this, so had to resort to using OUTER APPLY, even when I'm trying to change to Oracle SQL syntax from ANSI syntax.

In our team we used to work with diverse RDBMS mostly using ANSI syntax, but we decided for our current (pure Oracle) project to switch to Oracle syntax due to issues in the query transformation, as published by Jonathan Lewis:

https://jonathanlewis.wordpress.com/?s=ANSI+Standard

Thanks to the ASKTOM team for your excellent work
Chris Saxon
November 23, 2021 - 2:00 pm UTC

You're welcome!

Note - if you find issues with ANSI SQL in Oracle Database, please raise bugs

Dieter: Re: Lateral outer join

Kim Berg Hansen, November 25, 2021 - 12:55 pm UTC

Hi, Dieter

Glad that the (+) syntax is helpful for you.

To be honest, I can't recall if I found it in the docs or what. It isn't really shown in the doc for SELECT (at least I can't find it now.)

The use of (+) for calling table functions is documented in the "table_collection_expression" syntax chart: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__I2065746

OUTER APPLY in the "cross_outer_apply_clause" syntax chart https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__BABJHDDA is documented to work on either table_reference or collection_expression.

Maybe I have just been guessing that if OUTER APPLY worked both on a TABLE() call and an inline view, then (+) syntax ought also to work (which it does.) But I can't help thinking that I have seen it in docs somewhere, I just can't find it. (Might have been something I saw while researching for chapter 1 of my book, which is all about lateral inline views ;-))

Another point, personally I wouldn't switch totally to Oracle traditional syntax just because there still are a few query transformation bugs.

It's true there were quite a few such bugs in the early versions that supported ANSI syntax, but in the newest releases I personally haven't had any issues. Sure, Jonathan can point you to "a few odd cases", but it's oddities that is not very likely you'll hit (at least in my opinion.)

I use ANSI syntax all over and haven't any problems with it. But of course it's your choice - the database supports both.
Chris Saxon
November 25, 2021 - 3:22 pm UTC

Thanks for your thoughts Kim - I'm unable to locate LATERAL (+) in the docs either!

Response to Kim

Dieter, November 26, 2021 - 3:44 pm UTC

Hi, Kim!

OK, it seems I wasn't plain stupid by missing the (+) in the syntax diagram :-)

I'll stick to Oracle Joins for the current project anyway: I'm a freelancer currently working for an "Oracle only since 1999" team,
where some of the team members display some reluctance of adapting ANSI style and would criticize me, if I don't comply
(even when we had been working together on and off since our time at the university some 25 years ago, and
Daniel Hochleitner from the Oracle APEX team might also give some anecdotes).

But anyway, they own the project and so they set the coding standards ...

Dieter

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.