Baonerges Baonerges - 2 months ago 7
SQL Question

CASE in WHERE clause with array

In my program the user has 4 options, "ALL","FK","FK2","FK3". The below code does not work because of the Array.

WHERE
JOBOP.CC_CODE in
CASE :P4_MACHINES
WHEN 'ALL' THEN ('FK','FK2','FK3')
ELSE :P4_MACHINES
END


Can someone help me form this clause properly or tell me if it is not possible please?

Just to reiterate my goal in other terms,

IF :P4_MACHINES = 'ALL' THEN
JOBOP.CC_CODE in ('FK','FK2','FK3')
ELSE
JOBOP.CC_CODE = P4_MACHINES
END

Answer

Hmmm . . . Is this what you want?

where (:P4_MACHINES = 'ALL' and  JOBOP.CC_CODE in ('FK','FK2','FK3'))
                             or (JOBOP.CC_CODE = :P4_MACHINES)