Oracle Apex - Case within a where statement

user_13552281 0
Viewed 106

I'm having issues while running the following query (interactive report / simplified the query cause I'm sure the issue is with the case statement):

select 
v.manager_email 
from my_table  v
where 

(
case 
when :P28_ACT_AS_ROLE_H = 'Director' then v.director_email = :P28_ACT_AS_H 
when :P28_ACT_AS_ROLE_H = 'Admin'        then v.manager_email  = nvl(:P28_ACT_AS_H, 
v.manager_email)
when :P28_ACT_AS_ROLE_H = 'Manager'  then v.manager_email  = :P28_ACT_AS_H

end
)

The error is this one: ORA-20999. Does someone know why this is happening? (:p28 items are calculated via computations and work perfectly)

Thanks!

oracle-apex oracle sql
2 Answers

Don't use a case when boolean logic suffices:

where (:P28_ACT_AS_ROLE_H = 'Director' and v.director_email = :P28_ACT_AS_H) or
      (:P28_ACT_AS_ROLE_H = 'Admin' and v.manager_email = nvl(:P28_ACT_AS_H, v.manager_email)) or
      (:P28_ACT_AS_ROLE_H = 'Manager' and v.manager_email  = :P28_ACT_AS_H)

The specific reason in your example is that Oracle doesn't recognize a boolean expression as a valid return value for a case, resulting in a syntax error

user_11440350

Or, with CASE:

where nvl(:P28_CT_AS_H, v.manager_email) =
      case when :P28_ACT_AS_ROLE_H = 'Director' then v.director_email
           when :P28_ACT_AS_ROLE_H = 'Admin'    then v.manager_email
           when :P28_ACT_AS_ROLE_H = 'Manager'  then v.manager_email
      end
user_90979060
Related