Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Case statement with <> condition Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2014 8:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 175, Visits: 1,399
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?
Post #1568498
Posted Wednesday, May 7, 2014 8:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 12,923, Visits: 12,342
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1568511
Posted Wednesday, May 7, 2014 8:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:30 PM
Points: 12,895, Visits: 32,089
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1568512
Posted Wednesday, May 7, 2014 9:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 12,923, Visits: 12,342
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1568525
Posted Wednesday, May 7, 2014 9:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:53 AM
Points: 6,917, Visits: 6,978
Well subject to indexes and performance testing I do this
;with cte (ID) AS (
SELECT ID
FROM Test1 T1
WHERE @p_flag = 1
AND T1.moveFlag = 26
UNION ALL
SELECT ID
FROM Test1 T1
WHERE @p_flag = 0
AND T1.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.

Post #1568555
Posted Wednesday, May 7, 2014 9:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:27 AM
Points: 333, Visits: 742
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:

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

Post #1568565
Posted Wednesday, May 7, 2014 10:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 12,923, Visits: 12,342
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:

[quote]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/


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1568570
Posted Wednesday, May 7, 2014 10:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:27 AM
Points: 333, Visits: 742
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:

[quote]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/



...might want parens arount that inner OR condition, I spose
Post #1568597
Posted Wednesday, May 7, 2014 10:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 12,923, Visits: 12,342
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:

[quote]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/



...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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1568605
Posted Thursday, May 8, 2014 12:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 175, Visits: 1,399
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...
Post #1568776
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse