How to inlcude 'ALL' in my Query?

Hi,

I have a List box which has multiple values as parameters. Also, the user can choose a value "ALL' which would mean all values in the list box need to be
considered.

Ok, This field is used in my query. So, How do I setup my query so It can take
in a single value or ALL.

Can it be done in a single Query?

Thanks
Nurani Sivakumar

Find more posts tagged with

Comments

mwilliams

Hi Nurani,

To get all values, you'd just have to change the SQL to not have the where clause with the parameter value.

nuraniuscc

How do I do this? Is there any way to "Toggle" between having the specific parameter in the query and having a generic version.

Any exisiting posts that talk about it might help + your suggestions.

Thanks
Nurani Sivakumar

mwilliams

Nurani,

In your dataSet, you could use a generic select statement that would select all the values. Then, in the beforeOpen method of your dataSet, you could add a where statement with the parameter values chosen by the user if they don't select "All". This would allow you to then handle both cases with 1 dataSet.

nuraniuscc

Hi Mike,

I thought this is interesting.

Ok, When you are using an ALL condition in your WHERE clause, we normally
create a generic SQL.

How does the ORDER BY, GROUP BY clause work with this?

i.e.,

1) Generic SQL with Order BY
2) In the "BeforeOpen" Event I have the "ALL" condition for the WHERE clause

Thanks
Nurani Sivakumar

mwilliams

Nurani,

You could have a generic query without the Order by portion and edit the query in the beforeOpen script to include just the Order by portion without the parameter if 'All' is chosen.

i.e. this.queryText = this.queryText + " Order by...";

nuraniuscc

Mike,

I have "WHERE" clause in the Script. I want to have the GROUP BY and
ORDER BY clause as well for each of them.

So, Should I have 2 statements one after the other or can I merge them all in 1.

OK, If I have BOTH, should I have 2

this.queryText = this.queryText + Where ...
this.queryText = this.queryText + Group By .....
this.queryText = this.queryText + Order By .....

Is that Right?

Thanks
Nurani Sivakumar

mwilliams

Nurani,

If you have a generic query set up like:

select blah
from blah

You should be able to handle everything else in the beforeOpen script. If they choose 'All', you should be able to do:

this.queryText = this.queryText + " Group by blah Order by blah";

If they choose a different value, you should be able to do:

this.queryText = this.queryText + " where blah = param["blah"] Group by blah Order by blah";

I'm pretty sure you won't have to do each one in its own step.