Struggling with Where Case statement 2

[dianemarie]

I always have trouble with these. I'm trying to specify order types via a parameter in the Where statement.

case @OrderType
when 'Exchange' then lo.list_order_rentexch in ('Exchange','Partial Exchange')
when 'No Exchange' then lo.list_order_rentexch in ('online','insert','rental','database')
when 'All' then lo.list_order_rentexch in ('exchange','partial exchange','online','insert','rental','database')
else ''

I'm getting "incorrect syntax near the keyword 'in'. Any help would be appreciated, also any good web references on CASE statements. Thank you.

 

[RiverGuy]

I'm not sure you can use the WHERE/IN with the CASE statement like that. I could be wrong, and you might want to ask in the SQL Server Programming forum (or whichever database you are using).

However, here is a SQL Server solution to run your query as you want:

DECLARE @t1 TABLE (WhereValue VARCHAR(100))
IF @OrderType = 'Exchange' OR @OrderType = 'All'
BEGIN
  INSERT INTO @t1 SELECT 'Exchange'
  INSERT INTO @t1 SELECT 'Partial Exchange'
END
IF @OrderType = 'No Exchange' OR @OrderType = 'All'
BEGIN
  INSERT INTO @t1 SELECT 'online'
  INSERT INTO @t1 SELECT 'insert'
  INSERT INTO @t1 SELECT 'rental'
  INSERT INTO @t1 SELECT 'database'
END

SELECT YourTable.*
FROM YourTable
INNER JOIN @t1 t ON YourTable.list_order_rentexch = t.WhereValue

 

[dianemarie]

Hi RG, I'm a little fuzzy here, but I think I see the general overview. I'm going to go try it out and I'll let you know how it goes. Or...I'll be bugging you with more questions. Thank you for the direction.

 

[RiverGuy]

Here's the overview.

An IN statement is kind of like a shorthand for a list of ORs. That is one way to filter out data.

Another way to filter out data is to INNER JOIN one table on another. So if you say, "return all rows where there is a match this other table," you are accomplishing the same end result by filtering your data.

The code above makes use of a table variable. You can create a table variable on the fly in sql server, and it does not get saved in your database. It exists only in your query, and you can use it as you would a physical table in your query.

So what the code above does is create a table variable with a column to hold your filter values.

If your @OrderType is Exchange, then the table variable is filled with the filter values for Exchange. If it's No Exchange, then it's filled with the values for those.

If @OrderType is All, then it is filled with both sets values. If @OrderType is anything else, then it is not filled at all.

So you end up with a table variable filled with values you want to use as your filter. You then INNER JOIN that table variable to your physical table to apply your filter.

 

[dianemarie]

Thank you for taking the time to explain this. It's very helpful and informative and it worked great for me. I do a lot of this (record selection using a parameter). I did eventually get it to work using a CASE expression in my Where statement but it seemed clunky, so I went with your solution. I couldn't get it to work at first until I copied it straight from your post to my query. I must have typed something wrong somewhere and just missed it. It's a good feeling to expand my knowledge a little and makes re-writing all these Crystal reports in SSRS a little easier. Again, many thanks.

 

[dianemarie]

Hey RG, I have another scenario where I think this might work. Every order record has three date fields, ie, each date is a separate column in the ListOrder table: OrderDate, ShipDate, InvoiceDate. I have to create a parameter @DateType that allows the user to pull the records by any of these 3 dates. Last time I had this I gave up and created separate reports. We do this in Crystal quite easily in the select criteria formula but I'm having trouble in SSRS, because I'm typically doing it in conjunction with a date range via parameter as well (start date and end date). I'm thinking I can populate the temp table with the appropriate date field, then link to...wait, that's not going to work. I have no way of knowing which field to link it to. If you have any thoughts on this that would be great. Meanwhile I will bang around the Help systems and Google.

 

[RiverGuy]

I would code that query like the following:

SELECT * FROM YourTable
WHERE
(@DateType = 'OrderDate' AND OrderDate = @DateParameter)
OR
(@DateType = 'ShipDate' AND ShipDate = @DateParameter)
OR
(@DateType = 'InvoiceDate' AND InvoiceDate = @DateParameter)

 

[dianemarie]

OK, this looks too simply beautiful to be true, but...I've been pleasantly surprised by Tek-Tip gurus in the past. I'll let you know...

 

[dianemarie]

Worked like a charm. Thanks RG. (I'll be back...)