June 12, 2017 at 11:03 pm
SELECT *
FROM Table1 JOIN Table2 JOIN Table3
WHERE CASE @Fruit
WHEN 'Apples' THEN Table1.Fruit = 'Apples'
WHEN 'Citrus' THEN Table2.Food = 'Oranges' OR Table2.Food = 'Lemons'
WHEN 'Other' THEN Table1.Fruit != 'Apples' AND Table2.Food != 'Oranges' AND Table2 != 'Lemons'
ENDJune 13, 2017 at 12:18 am
dan.rowe0898 - Monday, June 12, 2017 11:02 PMI'm trying to figure out how to use a case statement in a where clause (with !=), here is an idea of what I am trying to do:
SELECT *
FROM Table1 JOIN Table2 JOIN Table3
WHERECASE @Fruit
WHEN 'Apples' THEN Table1.Fruit = 'Apples'
WHEN 'Citrus' THEN Table2.Food = 'Oranges' OR Table2.Food = 'Lemons'
WHEN 'Other' THEN Table1.Fruit != 'Apples' AND Table2.Food != 'Oranges' AND Table2 != 'Lemons'
END
I can't find anything online similar to this, does it look right?
Suggest you start by reading this article: Catch-all queries by Gail Shaw
😎
These types of queries are normally an indicator of poor database design.
June 13, 2017 at 12:42 am
Hi Dan,
I don't think it's possible to do the structure you're suggesting - if I understand things correctly, SQL doesn't behave the way you expect it to with regards to boolean expressions.
Your CASE statement needs to return a value, but you have it returning an condition, which it won't automatically evaluate.
Here's a similar example, in some languages this would return TRUE or 1, in SQL it is invalid.select 1=1
The CASE statement also expects a simple expression after the THEN (variable/column reference etc.), not a condition
I recommend using the below format, which is a more standard style.
WHERE
@Fruit = 'Apples' AND Table1.Fruit = 'Apples'
OR @Fruit = 'Citrus' AND (Table2.Food = 'Oranges' OR Table2.Food = 'Lemons')
OR @Fruit = 'Other' AND Table1.Fruit != 'Apples' AND Table2.Food != 'Oranges' AND Table2.Food != 'Lemons'
The closest I can get to your sample is below, where the CASE statement returns a value, which is then compared against another value to determine whether to include or exclude the given row, however I see this as less intuitive than the above.
CASE
WHEN @Fruit = 'Apples' AND Table1.Fruit = 'Apples' THEN 1
WHEN @Fruit = 'Citrus' AND (Table2.Food = 'Oranges' OR Table2.Food = 'Lemons') THEN 1
WHEN @Fruit = 'Other' AND Table1.Fruit != 'Apples' AND Table2.Food != 'Oranges' AND Table2.Food != 'Lemons' THEN 1
END = 1
The post Eirikur has mentioned has good advice as to your general approach, I also recommend giving that a read.
Andrew
June 13, 2017 at 9:47 am
Please post DDL and inserts.
June 13, 2017 at 9:59 am
Probably the typical way to do that using a CASE expression would be:
WHERE
1 = CASE @Fruit
WHEN 'Apples' THEN CASE WHEN Table1.Fruit = 'Apples' THEN 1 ELSE 0 END
WHEN 'Citrus' THEN CASE WHEN Table2.Food = 'Oranges' OR Table2.Food = 'Lemons' THEN 1 ELSE 0 END
WHEN 'Other' THEN CASE WHEN Table1.Fruit != 'Apples' AND Table2.Food != 'Oranges' AND Table2 != 'Lemons' THEN 1 ELSE 0 END
END
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 13, 2017 at 10:03 am
Are you comparing to one column or multiple columns? You use Table1.Fruit and Table2.Food even when those appear to represent the same attribute. Please post a most detailed example using DDL, sample data and expected results. Read how to do it in the articles linked in my signature.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply