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;

Yes they can. see documentation here: http://docs.oracle.com/cd/A97630_01/appdev.920/a96624/04_struc.htm — Taku_, Jun 12 '15 at 18:58

sstan · Accepted Answer · 2015-06-12T19:21:00.113

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.

Thanks for the feedback, I will try this out :) – Miguel Jun 12 '15 at 19:18 — Miguel, Jun 12 '15 at 19:18

score 2 · Answer 2 · answered Jun 12 '15 at 19:13

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.

score 0 · Answer 3 · answered Jun 12 '15 at 19:15

0

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

answered Jun 12 '15 at 19:15

tbone

15,107
3
33
40

I was leaning towards doing a left outer join, thanks for the feed back :) – Miguel Jun 12 '15 at 19:17

score 0 · Answer 4 · answered Dec 21 '20 at 14:59

0

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

answered Dec 21 '20 at 14:59

JustinasPHP

1
1

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

4 Answers4

TEST

TEST