Conditional Where using a Parameter

How do I construct a select with a conditional where:

If DefaultWH is not blank I want to add a "AND part".

SELECT DISTINCT Name
FROM Warehouse
WHERE (Cono = @.Company)

CASE WHEN

@.DefaultWH' <> ' ' THEN

AND (@.DefaultWH = whseid)

END

Here is something that I have done. Rather than use a blank, set it to null and use an isnull.

Example:

If @.DefaultWh = ''
Begin
Set @.DefaultWh = NULL
End

Select Distinct Name
from Warehouse
Where (Cono = @.Company)
and isnull(@.DefaultWH, WhseID) = WhseID

Hope that helps!

BobP

|||

Try :

SELECT DISTINCT Name
FROM Warehouse
WHERE ((Cono = @.Company) and (@.DefaultWH='')) or ((Cono = @.Company) and (@.DefaultWH=whseid))

Hope it work's...

|||

Neith of the examples worked. I need to have the query run in 1 of 2 formats

Parameter 1 = select company

Parameter 2 = select DefaultWH

Parameter 3 =

SELECT DISTINCT Name
FROM Warehouse
WHERE (Cono = @.Company) < === from Parameter 1

or

SELECT DISTINCT Name
FROM Warehouse
WHERE (Cono = @.Company) AND (@.DefaultWH = whseid) <===From Parameter 1 and 2

I need to be able to drop or include the "AND part" based on the value of Parameter 2

Parameter 1 runs and I pick a company#

Parameter 2 runs and it returns DefafultWH as a blank (access to all warehouses) or a value (limited to that warehouse) based on the Company# enter for Parameter 1.

If Parameter 2 is blank then run Parameter 3 -- Select without the AND

If Parameter 2 is not blank then run Parameter 3 -- Select with the AND part

It just cant be that hard to to. I just have not been that this that long to figure it out.

|||

My example would work in both scenarios.

This is what it does:

The ISNULL syntax returns the value of @.DefaultWH if it is NOT null, and the value of WhseID if it is. So, If parameter2 is blank, change it to null, and the AND clause will look like this:

Parameter1 = 'ABC Company'

Parameter2 = blank (Unselected)

Select Distinct Name
from Warehouse
Where Cono = 'ABC Company'
and WhseID = WhseID

So this effectively removes the AND. The only caveat to this: If WhseID can be NULL, then you would need to add one more statement.

Select Distinct Name
From Warehouse
Where Cono = @.Company
And (WhseID = isnull(@.DefaultWH,WhseID) or WhseID is null)

You have to add the "WhseID is null" clause because null does not "=" null, it only IS null.

Feel free to email me at bobp1339
at
yahoo

BobP

|||

Where does the code go

If I insert on the generic query screeen is says that @.DefaultWH is not declared or defined

If I insert the following into the generic code screen a

=Code.GetSQL()

and then insert the code in the Report code section I get errors there to.