IS NULL in CASE in predicates

tell me pls, that’s real – use the CASE construct, where the expression with IS NULL/IS NOT NULL will be returned in TNEN? I have a procedure in a package that receives an input parameter and it is passed to the selector in cursor, which I want to set one of the predicates:

  select t1...
         t2...
  from test t1,
       test t2
  where t1.flg = 'N'
  AND t2.field = 'supertest'
  AND CASE 
            WHEN input_parm = 'Y' THEN t1.id IS NOT NULL
            WHEN input_parm = 'N' THEN t1.id IS NULL
            WHEN input_parm IS NULL THEN
            ELSE t1.id = input_parm
            END

input_parm – is the parameter entering the procedure

if input_parm=Y then the t1.id field is not equal null
if input_parm=N then the t1.id field is equal null
if input_parm is null then this this condition should not be taken into condition, those selector should be both from IS NULL and IS NOT NULL
if input_parm IS NOT NULL and !=’Y’ and !=’N’ then t1.id=input_parm

In the example the compiler swears at IS NULL after THEN. Can you help me plz?

Answer

The predicates of a CASE expression (i.e. what follows THEN and ELSE) must be literal values, not boolean expressions. You may refactor your CASE expression as:

AND
    (input_parm = 'Y' AND t1.id IS NOT NULL) OR
    (input_parm = 'N' AND t1.id IS NULL) OR
    input_parm IS NULL OR
    t1.id = input_parm

Advertisement

Answer