Hello,


I have written a query in command line like

select * from employee_table where Occupation in ['{?Occup}']

Here is the Employee_table

Eid	Name	Occupation
1	John	Manager
2	Pam	Clerk
3	Julie	Manager
4	Jacob	Clerk
5	Suzie	Assistant

So now Manager falls int Category 'Senior' and Everything else is 'Junior' like the clerk and Assistant.


select * from employee_table where Occupation in [ '{?Occup}']


I added the following values to default list of Parameter {?Occup}

Manager

Clerk, Assistance.


So The prompt would have a list of values

Manager
Clerk, Assistant

This would work if user selects Manager from the list and executes Because

select * from employee_table where Occupation in [ 'Manager']

The above query would return results

But doesnot work for 'Clerk, Assistant' , reason being

select * from employee_table where Occupation in [ 'Clerk, Assistant']

This wouldnot return any values.

Is there a possibility for

select * from employee_table where Occupation in [ 'Clerk', 'Assistant'];


If I created a report by dragging the tables , I would have written a formula like

If {?Occup} = 'Junior'
then  Occupation in [ 'Clerk, Assistant']
else if
If {?Occup} = 'Senior'
then  Occupation  = 'Manager
else 'True'

Then in the select expert I would have selected the formula to be True.


Just because I wrote the SQL query in command Prompt this method seems not to work.

Is there any way my requirement being met?

I tried decode

select * from employee_table where Occupation in [   (DECODE('{?Occup}','Junior',('Clerk, Assistant'),'{?Occup}')]

Doesnot work as decode doesnot return multiple values.

Please advise.

Thanks
Nammu