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.