ORA-00920 Invalid Relational Operator @Prompt with Case

building semantic layer
1

I have the following @prompt with a case statement:

Syntax:
@Select(Dealer\Dealer Name) IN (SELECT case when @prompt(‘Enter Dealer Name(s) or type ALL’,‘C’,‘LOV Objects\Dealer Name’,multi,free)=‘ALL’ then @Select(Dealer\Dealer Name) else @prompt(‘Enter Dealer Name(s) or type ALL’,‘C’,‘LOV Objects\Dealer Name’,multi,free) end FROM DUAL)

When I parse the object I get the ORA-00920 Invalid Relational Operator error :confused: . I have search BO knowlege base and BOB, but have not found a solution. The BO KB recommends adding “eval_without_parenthesis=Y” to my Oracle .prm file, but this does not work.

I am using the case statement to allow the use of “ALL”. I know you don’t need the case statement, but I understand that the indexes are ignored unless the case statement is used (something I have not verified just trusting a co-worked who claims they’ve tested this theory).

Can anyone help me understanding why this prompt condition is not parsing?

Thanks,
Kim

kim snead (BOB member since 2002-08-21)

2

I’ve never seen it done this way.

2 suggestions:
:arrow_forward: Get rid of the @select stuff. It can cause problems sometimes.
:arrow_forward: Get rid of the CASE statement. A simple OR will do the trick just fine.

Steve Krandel :us: (BOB member since 2002-06-25)

3

Getting rid of the @select and replacing it with the tablename.column or simply the class\object did not resolve anything.

Are you familiar with the indexes being ignored if the “simple OR” was used for the ALL prompt? This is the only reason why I was told to do it this way, otherwise I would have stuck with the “simple OR”

Thanks,
Kim

kim snead (BOB member since 2002-08-21)