July 18, 2010 at 8:15 am
Hi,
i would like to compare a value in the Where clause
if it is
SELECT * FROM Tbl1
WHERE TYPE = CASE @TYPE WHEN 1 THEN 1
WHEN 2 THEN 2
ELSE
Here i stuck when it is not 1 or 2 it should take all.
how to go about it
thanks,
Ami
July 18, 2010 at 8:28 am
-- Option 1
SELECT *
FROM Tbl1
WHERE 1 =
CASE
WHEN @Type IN (1,2) AND [Type] = @Type THEN 1
WHEN @Type IN (1,2) THEN 0
ELSE 1
END;
-- Option 2
SELECT *
FROM Tbl1
WHERE @Type IN (1,2)
AND [Type] = @TYPE
UNION ALL
SELECT *
FROM Tbl1
WHERE @Type NOT IN (1,2);
-- Option 3
IF (@Type IN (1,2))
BEGIN
SELECT *
FROM Tb11
WHERE [Type] = @Type;
END;
ELSE
BEGIN
SELECT *
FROM Tb11;
END;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 18, 2010 at 2:32 pm
Anamika, has one of the solutions provided by Paul worked for you?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 19, 2010 at 3:32 am
Hi Paul,
It's working. Actually I found a round about way & accomplished.
declare @t1 table(type int)
if @type =0
begin
insert into @t1 values (1)
insert into @t1 values (2)
end
else
insert into @t1 select @type
select * from tbl1
where Type in (select type from @t1)
your code avoids temp tables and gives if there is any other values ( type is 3,4 etc...)
I can't understand the logic behind
WHERE 1 =
CASE
WHEN @Type IN (1,2) AND [Type] = @Type THEN 1
WHEN @Type IN (1,2) THEN 0
ELSE 1
END;
in case @Type is 1 also it leads to 1=1?
could you please explain it?
any links / papers also appreciated much.
Thanks,
Regards,
Ami
July 19, 2010 at 3:58 am
Anamika (7/19/2010)
I can't understand the logic behind
WHERE 1 =
CASE
WHEN @Type IN (1,2) AND [Type] = @Type THEN 1
WHEN @Type IN (1,2) THEN 0
ELSE 1
END;
in case @Type is 1 also it leads to 1=1?
Hey Ami,
Yes that example would benefit from a bit of extra explanation 🙂
The idea is to return a row if the result of the CASE expression returns 1. (I guess that bit is easy.)
The first test in the CASE statement is only true if (@Type = 1 or @Type = 2) *AND* if @Type matches the value in the [Type] column for this row. So, this first test will return 1 if @Type is 1 and the [Type] column contains 1 *OR* if @Type is 2 and the [Type] column contains 2.
If the first test does not pass, the second test is checked. If @Type is 1 or 2, the CASE returns 0. This fails the outer comparison with 1, so the current row will not be output. The trick is to realise that test 1 must have failed to get to test 2. So, if @Type is 1 or 2, we know that the value for [Type] in the current row does not match @Type - if it did, test 1 would have passed! We need to check that @Type is 1 or 2 again, because it is still possible for @Type to contain some other value (like 3 or 4 for example) at this stage.
If both test 1 and test 2 fail, we hit the ELSE part. To get here, @Type cannot be 1 or 2, since either test 1 or test 2 would have passed. In this case, we always want to return every row.
It's actually a lot harder to explain that in English than I thought 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 19, 2010 at 8:15 am
Hi,
I don't think it needs to be that complicated. Does this work:
SELECT * FROM Tbl1
WHERE TYPE = CASE @TYPE WHEN 1 THEN 1
WHEN 2 THEN 2
ELSE TYPE END
Regards,
Mike
July 19, 2010 at 8:23 am
mthurber (7/19/2010)
I don't think it needs to be that complicated. Does this work...
Hey Mike,
Yes, but only if the [type] column contains no NULLs. That information was not provided, so the previous solutions could not assume no NULLs.
There are very many solutions to this type of problem.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 19, 2010 at 8:25 am
mthurber (7/19/2010)
Hi,I don't think it needs to be that complicated. Does this work:
SELECT * FROM Tbl1
WHERE TYPE = CASE @TYPE WHEN 1 THEN 1
WHEN 2 THEN 2
ELSE TYPE END
Regards,
Mike
If you like minimalistic approach then this would win:
SELECT * FROM Tbl1
WHERE TYPE = CASE WHEN @TYPE IN (1, 2) THEN @TYPE
ELSE TYPE
END
However such using of CASE WHEN in WHERE clause will always lead to table/clustered index scan.
Using Paul's Option 3 would potential perform index seek for @Type IN (1,2) therefore it will be faster...
July 19, 2010 at 8:30 am
Hey Eugene,
That requires [type] to be defined NOT NULL too. You're quite right about the performance thing - part of the reason I provided three different solutions up front (and there are more!) is because of issues like that, parameter sniffing, and query plan re-use issues...and so on and so on...
For what it's worth, I try to avoid this sort of construction in code, it's normally a sign you're trying to do something dumb, and need to re-think a bit. Erland Sommarskog's site is a great resource for these sorts of problems.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 19, 2010 at 10:31 pm
Thanks to Eugene and mthurber nice and easy way of achieving it.
by the way, Mr. Paul, i still don't understand the logic behind it.
SELECT * FROM Tbl1
WHERE 1 = 1
If I execute the above I get all the values, but magically if i do it your set of code even though it gives 1 for
SELECT *
FROM @T
WHERE 1 =
CASE
WHEN @Type IN (1,2) AND [Type] = @Type THEN 1
WHEN @Type IN (1,2) THEN 0
ELSE 1
END;
the first case and the ELSE part I get the exact result sets. how?
Even though it checks the [Type] values in the CASE statements it returns '1'.
So, how the query has been logically implemented?
Thanks,
Regards,
July 19, 2010 at 10:47 pm
Anamika (7/19/2010)
Thanks to Eugene and mthurber nice and easy way of achieving it.by the way, Mr. Paul, i still don't understand the logic behind it.
SELECT * FROM Tbl1
I think Paul has done a good job of explaining the logic. If you still don't understand the logic, i would ask you to take some time and study the code yourself and try to understand the logic. Its not that difficult to understand and you will not have easy solutions to your problems always. Its always good if you have more than one way to solve your problem.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 20, 2010 at 2:57 am
i'm trying to get what Paul is saying. but in first case and else part it is giving 1.
where 1 =1 satisfies i get all the rows.
by the way
for the following criteria how it'll work?
say the type is varchar
When i get exact values in the type then filter on the same.
When i get 'all' get all the values inclusive of nulls
when i get 'others' then the info should be compared with the list of values against a table
SELECT * FROM Tbl
WHERE 1 =
CASE
WHEN @Type IN (SELECT TYPE FROM @s-2) AND [Type] = @Type THEN 1
WHEN @Type IN (SELECT TYPE FROM @s-2) THEN 0
ELSE 1
END;
but not getting the exact results?
Thanks,
Ami
July 20, 2010 at 6:15 am
You still haven't got clearly what Paul explained. The Where Clause is not evaluated for the whole table at one go, It is evaluated on a row by row basis. Read the explanation carefully and i am sure you will understand. Its might be tricky at first but once you understand it will be easy. I have tried to explain the same with an example, hope its usefull
DECLARE@Type VARCHAR(5)
DECLARE@tbl_Table TABLE
(
TypeVARCHAR(1),
ColVARCHAR(10)
)
SET@Type = '1'
INSERT@tbl_Table
SELECT'1', 'A' UNION ALL -- This passes the fist condition ( 1 = 1 ) and is hence returned
SELECT'1', 'B' UNION ALL -- This passes the fist condition ( 1 = 1 ) and is hence returned
SELECT'2', 'C' UNION ALL -- For this row ( [Type] = @Type fails ) and hence first condition fails, but the second condition passes( 1 = 0 ) and is hence not returned
SELECT'2', 'D' -- For this row ( [Type] = @Type fails ) and hence first condition fails, but the second condition passes( 1 = 0 ) and is hence not returned
SELECT *
FROM @tbl_Table
WHERE 1 =
CASE
WHEN @Type IN (1,2) AND [Type] = @Type THEN 1
WHEN @Type IN (1,2) THEN 0
ELSE 1
END;
-- Now, try setting the value of @Type to say '3' and both the first and second conditions fail for each and every row and you will get all the rows
This should work for your second question
SELECT *
FROM Tbl
WHERE 1 =
CASE
WHEN @Type IN (1,2) AND [Type] = @Type THEN 1
WHEN @Type IN (1,2) THEN 0
WHEN @Type = 'All' THEN 1
WHEN @Type = 'Others' AND [Type] IN ( SELECT TYPE FROM @s-2 ) THEN 1
ELSE 0
END;
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply