Case statement with <> condition

Question

Post reply

Case statement with <> condition

Megha P

SSCommitted

Points: 1976
More actions
May 7, 2014 at 8:22 am

#293079

I have query something like this ..i haven't posted here original quary
IF (@p_flag = 1)
BEGIN
SELECT ..
...
FROM Test1 T1
INNER JOIN Test2 T2 ON T1.ID = T2.ID
WHERE T.moveFlag = 26
END
ELSE
BEGIN
SELECT ..
...
FROM Test1 T1
INNER JOIN Test2 T2 ON T1.ID = T2.ID
WHERE T.moveFlag <> 26
END
I come up with this
SELECT ..
...
FROM Test1 T1
INNER JOIN Test2 T2 ON T1.ID = T2.ID
WHERE 1 = CASE WHEN @p_flag = 1
THEN CASE WHEN moveFlag = 26
THEN 1
ELSE 0
END
ELSE CASE WHEN moveFlag <> 26
THEN 1
ELSE 0
END
END
Any other optimized way to make this in one statement?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

How about this?

SELECT [Columns]

FROM Test1 T1

INNER JOIN Test2 T2 ON T1.ID = T2.ID

WHERE CASE @p_flag

WHEN 1 THEN moveFlag = 26

ELSE moveFlag > 26 OR moveFlag < 26

END

Notice I modified the second predicate to two portions. This is to allow any index on moveFlag to be a seek instead of a scan.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 2

well, the WHERE statement is still a "catch all" query, but isn't this functionally identical?

WHERE 1 = CASE WHEN @p_flag = 1 OR moveFlag = 26

THEN 1

ELSE 0

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Lowell (5/7/2014)
well, the WHERE statement is still a "catch all" query, but isn't this functionally identical?
WHERE 1 = CASE WHEN @p_flag = 1 OR moveFlag = 26
THEN 1
ELSE 0
END

Wouldn't this not return any rows when @p_flag = 0? This would eliminate all the rows in the original second query.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

David Burrows SSC Guru Points: 65142 More actions · Answer 4

Well subject to indexes and performance testing I do this

;with cte (ID) AS (

SELECT ID

FROM Test1 T1

WHERE @p_flag = 1

ANDT1.moveFlag = 26

UNION ALL

SELECT ID

FROM Test1 T1

WHERE @p_flag = 0

ANDT1.moveFlag <> 26)

SELECT ..

FROM cte T1

JOIN Test2 T2 ON T1.ID = T2.ID

Far away is close at hand in the images of elsewhere.
Anon.

gbritton1 SSCertifiable Points: 6522 More actions · Answer 5

Sean Lange (5/7/2014)

How about this?

SELECT [Columns]

FROM Test1 T1

INNER JOIN Test2 T2 ON T1.ID = T2.ID

WHERE CASE @p_flag

WHEN 1 THEN moveFlag = 26

ELSE moveFlag > 26 OR moveFlag < 26

END

how did you make this work? I copied and pasted this into SSMS, set up test tables but it won't parse. I get:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

gbritton1 (5/7/2014)

Sean Lange (5/7/2014)
How about this?
SELECT [Columns]
FROM Test1 T1
INNER JOIN Test2 T2 ON T1.ID = T2.ID
WHERE CASE @p_flag
WHEN 1 THEN moveFlag = 26
ELSE moveFlag > 26 OR moveFlag < 26
END
how did you make this work? I copied and pasted this into SSMS, set up test tables but it won't parse. I get:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.

Ahh you are correct. It will not work as posted. This is some fallout from not having ddl and sample data to work with.

This should work...of course it is untested for the same reason I didn't test my previous post.

WHERE (@p_flag = 1 AND moveFlag = 26)

OR (@p_flag <> 1 --if this is a bit I would use = 0 instead of <> 1

AND moveFlag > 26 OR moveFlag < 26)

This is similar to a catch-all type of query. Gail has an excellent article about that type of query here. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

gbritton1 SSCertifiable Points: 6522 More actions · Answer 7

Sean Lange (5/7/2014)

gbritton1 (5/7/2014)
Sean Lange (5/7/2014)
How about this?
SELECT [Columns]
FROM Test1 T1
INNER JOIN Test2 T2 ON T1.ID = T2.ID
WHERE CASE @p_flag
WHEN 1 THEN moveFlag = 26
ELSE moveFlag > 26 OR moveFlag < 26
END
how did you make this work? I copied and pasted this into SSMS, set up test tables but it won't parse. I get:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.
Ahh you are correct. It will not work as posted. This is some fallout from not having ddl and sample data to work with.
This should work...of course it is untested for the same reason I didn't test my previous post.
WHERE (@p_flag = 1 AND moveFlag = 26)
OR (@p_flag <> 1 --if this is a bit I would use = 0 instead of <> 1
AND moveFlag > 26 OR moveFlag < 26)
This is similar to a catch-all type of query. Gail has an excellent article about that type of query here. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]

...might want parens arount that inner OR condition, I spose

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

gbritton1 (5/7/2014)

Sean Lange (5/7/2014)
gbritton1 (5/7/2014)
Sean Lange (5/7/2014)
How about this?
SELECT [Columns]
FROM Test1 T1
INNER JOIN Test2 T2 ON T1.ID = T2.ID
WHERE CASE @p_flag
WHEN 1 THEN moveFlag = 26
ELSE moveFlag > 26 OR moveFlag < 26
END
how did you make this work? I copied and pasted this into SSMS, set up test tables but it won't parse. I get:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.
Ahh you are correct. It will not work as posted. This is some fallout from not having ddl and sample data to work with.
This should work...of course it is untested for the same reason I didn't test my previous post.
WHERE (@p_flag = 1 AND moveFlag = 26)
OR (@p_flag <> 1 --if this is a bit I would use = 0 instead of <> 1
AND moveFlag > 26 OR moveFlag < 26)
This is similar to a catch-all type of query. Gail has an excellent article about that type of query here. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
...might want parens arount that inner OR condition, I spose

Only if we want it to be correct. 😉 Good catch!!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Megha P SSCommitted Points: 1976 More actions · Answer 9

Sean Lange's code works perfectly ..with index seek

WHERE (@p_flag = 1 AND moveFlag = 26)

OR (@p_flag <> 1 --if this is a bit I would use = 0 instead of <> 1

AND moveFlag > 26 OR moveFlag < 26)

Thank you all...

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

Megha P (5/8/2014)
Sean Lange's code works perfectly ..with index seek
WHERE (@p_flag = 1 AND moveFlag = 26)
OR (@p_flag <> 1 --if this is a bit I would use = 0 instead of <> 1
AND moveFlag > 26 OR moveFlag < 26)
Thank you all...

With the exception of the logic flaw.

WHERE (@p_flag = 1 AND moveFlag = 26)

OR (@p_flag <> 1 --if this is a bit I would use = 0 instead of <> 1

AND (moveFlag > 26 OR moveFlag < 26))

Just need the extra set or parenthesis there. Glad that works for you and thanks for letting us know.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Megha P SSCommitted Points: 1976 More actions · Answer 11

yes, i have added parenthesis ..that's why it was working ..