CODEWITHSUNDEEP
sqloracle-database
Nick
Nick

Reputation: 127

case when then IN

below code does not work, please advise.

SELECT *
 FROM TBL_REASON_MAP A
WHERE case when V_ID = 3 then 'A' else to_char(a.type_id) end IN
      case when V_ID = 3 then 'A' else '(2,3)' end;

below is OK:

SELECT *
  FROM TBL_REASON_MAP A
 WHERE case when  V_ID = 3 then 'A' else to_char(a.type_id) end IN
       case when  V_ID = 3 then 'A' else '2' end;

note: V_ID is a variable to be passed a value in plsql block. If V_ID = 3 then it will return all data available, but if it's not it will return data with a.type_id in (2,3) only.

e.g. V_ID = 4

enter image description here

Upvotes: 0

Views: 128

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

The simplest logic is:

WHERE ( V_ID = 3 OR a.type_id IN (2, 3) );

There is no need for a CASE expression.

Upvotes: 0

Nick
Nick

Reputation: 127

So far the solution Ive got is below:

Declare

    V_ID number;

begin

    V_ID := 4;

    SELECT *
      FROM TBL_REASON_MAP A
     WHERE case when V_ID = 3 then 'A' else to_char(a.type_id) end >=
           case when V_ID = 3 then 'A' else '2' end
       and case when V_ID = 3 then 'A' else to_char(a.type_id) end !=
           case when V_ID = 3 then 'A' else '0' end;

END;

Upvotes: 0

APC
APC

Reputation: 146229

Presumably a.type_id contains values like 2, 3, 4 etc. The first case() returns a string of (2,3). It's a single value so the IN becomes an equality test. 2 does not equal '(2,3). Therefore the first code does not work. Whereas the second case() returns a single value 2 which can be matched to a value in a.type_id.

"anyway if V_ID = 3 then it will return all data available, but if its not, it will return data with a.type_id in (2,3)"

As other people have commented this is easy to implement with regular Boolean operations without the need for case() at all:

SELECT *
 FROM TBL_REASON_MAP A
WHERE V_ID = 3 
or (V_ID != 3 
    and a.type_id  IN (2,3));

Upvotes: 1

Related Questions