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

Question

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;

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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