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!
Advertisement
Answer
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