Can Oracle PL/SQL CASE statement include a SELECT query?

18.1k views Asked by At

I'm trying to do something similar to this:

CASE
 WHEN number IN (1,2,3) THEN 'Y' ELSE 'N' END;

Instead I want to have a query in the place of the list, like so:

CASE
 WHEN number IN (SELECT num_val FROM some_table) THEN 'Y' ELSE 'N' END;

I can't seem to get this to work. Also, here is an example of the query.

    SELECT number, (CASE
     WHEN number IN (SELECT num_val FROM some_table) THEN 'Y' ELSE 'N' END) AS YES_NO 
    FROM some_other_table;
4

There are 4 answers

0
sstan On BEST ANSWER

Yes, it's possible. See an example below that would do what you are intending. The difference is that it uses EXISTS instead of IN.

SELECT a.number, 
       (CASE WHEN EXISTS (SELECT null FROM some_table b where b.num_val = a.number)
             THEN 'Y'
             ELSE 'N'
        END) AS YES_NO 
    FROM some_other_table a;

EDIT: I confess: I like the answers given by the others better personally.

However, there will be a difference between this query and the others depending on your data.

If for a value number in the table some_other_table you can have many matching entries of num_val in the table some_table, then the other answers will return duplicate rows. This query will not.

That said, if you take the left join queries given by the others, and add a group by, then you won't get the duplicates.

0
Bob Jarvis - Слава Україні On

I suggest using an OUTER JOIN instead of trying to use a subquery in a CASE expression:

SELECT t.NUMBER, 
       CASE
         WHEN s.NUM_VAL IS NOT NULL THEN 'Y'
         ELSE 'N'
       END AS YES_NO
  FROM SOME_OTHER_TABLE t
  LEFT OUTER JOIN SOME_TABLE s
    ON s.NUM_VAL = t.NUMBER

Best of luck.

1
tbone On

Seems like you just need to join the tables and do a decode.

with x as
(
    select 1 as num from dual
    union
    select 2 as num from dual
    union
    select 3 as num from dual
),
y as 
(
    select 1 as num from dual
    union
    select 2 as num from dual
    union
    select 4 as num from dual
)
select x.num, decode(y.num, null, 'N','Y') as yes_no
from x
left outer join y on (x.num = y.num)

Output:

NUM YES_NO
1   Y
2   Y
3   N
0
JustinasPHP On

You can use subquery in case statement:

select case dummy when 'X' then (select 'TRUE' from dual) else 'FALSE' end TEST from dual;

TEST

TRUE

select case (select 'XXX' from dual) when 'XXX' then 'TRUE' else 'FALSE' end TEST from dual;

TEST

TRUE