multiple values in where clause

Hi

In report parameters i want to give options in where clause

for ex.

where Name = 'Abc'

1. I can do it i f i need to give a name in where clause
2. I will not write where clause if i want all names

Now my query is if i want both options in list box of report parameter what i need to do
by both options i mean to say
option 1 particular name
option 2 All

this all contains all name

suppose my list box has three two options
1. All
2. Particular name

1. if i select all then it should display all results where name in(All)
2. If i selecct particular name it should display records wheere name = particular name.

Somebody please help me.

Thanks
Rajat

Find more posts tagged with

Comments

Jenkinsj5

If I understand your question, you want to offer the option to return all or just one customer.

You can do this by using two like parameters, the default on both is set to wildcard (%). So it returns '% AND %', or it returns '% AND "Selection"', which is one.

If your question is more complex, please clarify it and maybe someone with more skills then I can help you.

select CUSTOMERNAME,
CUSTOMERNUMBER,
PHONE
from CLASSICMODELS.CUSTOMERS

Where CUSTOMERNAME like ?
And CUSTOMERNAME like ?

I attempted to attach my example report, but the upload has to the Forum has failed a couple times.

Let me know if you heed the demo report or if your problem is more complex.

wwilliams

Can you do it in your SQL statement? You don't clarify whether or not this is a scripted dataset.

something like this?
select * from person where personid like (case ? when 'ALL' then '%' else '%'||?||'%' end)

Hans_vd

You would need to create two data set parameters and bind both of them to the same report parameter.

Then you write your query as wwilliams suggests, or - if you don't need the like operator, I didn't read that in your quesition - do it like this:

SELECT * FROM TABLE WHERE (NAME = ? OR ? = 'All')

bricombs

Good Day
I created a small sample report using Classic Models as the data source. I just use a single like clause in my Query and created a Report Parameter that allows you to pick a single state or all states.

I have attached my sample.

Enjoy
Brian