Search code examples
sqloracle-databaseoracle-apex

Oracle sql (and Apex) case when in where clause

Can you help me with one conditions with case when in where clause? I have tried build something like that, but apex interface returned error:

where
tmp.employee_name in
(case when :APP_USER = 'xxx.xxx' THEN ('yyy.yyy', 'aaa.aaa')
      when :APP_USER = 'ddd.ddd' THEN ('xxx.xxx', 'uuu.uuu')
end)

Apex returned error like: "ORA-20999: Failed to parse SQL query! ORA-06550: line 17, column 85: ORA-00907: missing right parenthesis" When I used only one value like THEN ('yyy.yyy') everything is ok, but I need use few variables.

Thanks for help!

Solution

  • A CASE expression returns a scalar value and not an expression (or list of expressions).

    Instead, use AND and OR:

    WHERE (   :APP_USER = 'xxx.xxx'
      AND tmp.employee_name in ('yyy.yyy', 'aaa.aaa')
      )
OR    (   :APP_USER = 'ddd.ddd'
      AND tmp.employee_name in ('xxx.xxx', 'uuu.uuu')
      )