Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

select statement with case based on procedure returning boolean

chrissy2Mar 2 2009 — edited Mar 2 2009
Hi all,

I try to build a select statement with a case in where condition.
SELECT COL_ID, COL_TITLE, COL_COMMENT,
           ( CASE WHEN COL_IT = :USER_ID THEN 'Y' 
                       WHEN COL_USER = :USER_ID THEN 'Y'
                       WHEN REGEXP_LIKE(COL_IT_ADD, '(^|:)(' || :USER_ID || ')(:|$)') THEN 'Y'
                       ELSE 'N' END ) MY_COL,
FROM   MY_TABLE
WHERE  ( CASE WHEN MY_PACKAGE.AUTHORIZE('IT') = TRUE THEN 'Y'
                       WHEN MY_PACKAGE.AUTHORIZE('IT') = FALSE AND COL_USER = :USER_ID THEN 'Y'
                       WHEN MY_PACKAGE.AUTHORIZE('IT') = TRUE AND  REGEXP_LIKE(COL_IT_ADD, '(^|:)(' || :USER_ID || ')(:|$)') THEN 'Y'
                       ELSE   'N' END ) = 'Y'
The case for computing the column value is working fine. Running this query without the where condition is also working.
But with the condition I get the following error: SQL Error: ORA-00920: invalid relational operator 00920. 00000 - "invalid relational operator"
The procedure MY_PACKAGE.AUTORIZE(pValue) is returning a boolean. Is it possible to use a case statement in WHERE condition for computing a value and checking for this? If not is there another way to do something like this in a query?

Thanks for your help
chrissy
This post has been answered by 21205 on Mar 2 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 30 2009
Added on Mar 2 2009
6 comments
1,847 views