Invalid Relational Operator

[Former Member]

Hi All,

In my query, I'm trying to set up my Free-Hand SQL query to accept a prompt and either check the column against the values in the prompt, or if the prompt is equal to XXXX just display all:

select *

from invoice

where company_code in @prompt('Enter Company Code(s) separated by a semicolon or XXXX:', 'a', {' '}, multi, free) or @prompt(''Enter Company Code(s) separated by a semicolon or XXXX:', 'a', {' '}, multi, free) = 'XXXX'

When I pass a single company code, or XXXX the query executes fine. When i enter multiple company code values, I get the error message:

Exception: DBD, ORA-00920: invalid relational operator

State: N/A

and the really odd thing is if i remove everything after the OR - the query will accept mulitple or single codes with no issue.

Any advice?

Thanks!

[Former Member · Accepted solution]

Mathieu,

Just had a quick look at the condition:

where company_code in @prompt('Enter Company Code(s) separated by a semicolon or XXXX:', 'a', {' '}, multi, free) or @prompt(''Enter Company Code(s) separated by a semicolon or XXXX:', 'a', {' '}, multi, free) = 'XXXX' 

You use an 'IN' in combination with an equal to (=) in the same condition. Might be working if you only enter one value, but I can understand the error when you enter multiple values.

Just check if you change the '=' to 'in' (for the XXXX part of the condition) that that might be solving your problem.

Rgds,

Harry

[Former Member]

Thanks for your help! Although i did have to make one minor change. I had to switch the order of the XXXX portion of the where statement to ...

'XXXX' IN @prompt......

Thanks again!