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