Where

Answered by:

I trying to use a Case Statement in the Where Clause and I'm having difficulties.

The Where Clause has three conditions. Condition1 AND either Condition2 or Condtion3 must be met. I use @Variable1 to determine whether Condition2 or Condition3 must be met. When @Variable1 = "Operations" then Condition2 applies when @Variable1 = "Admin" then Condition3 applies:

1. Condition1 Field1 = 'Regional'


2. Condition2 When @Variable1 = "Operations": Field2 = 'Seattle' AND Field3 = "Primary'


3. Condition3 When @Variable1 = "Admin": Field4 = 'Portland'

Thanks for you help.

AND ((CASE Varible1 WHEN 'Operations' THEN Field2 END = 'Seattle'

AND CASE Varible1 WHEN 'Operations' THEN Field3 END = 'Primary')

OR CASE Varible1 WHEN 'Admin' THEN Field4 END = 'Portland')

WHERE organizational_level = 'Regional'

AND CASE WHEN @in_low_cohesion_flg = 'Operations'

AND store_city_name = 'Seattle'

AND store_status = 'Primary'

WHEN @in_low_cohesion_flg = 'Admin'

AND office_city_name = 'Portland'

THEN 'T' ELSE 'F' END = 'T';

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

• Marked as answer by ConstPM Tuesday, August 13, 2013 2:29 PM

All replies

Known is a DROP, Unknown is an OCEAN.

CASE in tsql is not quite the same as that in other languages. You need something like:

(@var1 = 'Operations' and Field2 = 'Seattle'and Field3 = 'Primary') or (@var1 = 'Admin' and Field4 = 'Portland)

There are alternative ways to accomplish this which might be worth exploring if you find the performance lacking on this approach.

• Proposed as answer by Chris Sijtsma Monday, August 12, 2013 8:31 PM

Fisrt of all it's not good idea to use case in where clause, because of complexity and performance problems. Its better to use dynamic search conditions. By the way if you still want a sample, try this please:

If This post is helpful post, please vote it. If This post is the answer to your question, please Propose it as answer. Thanks so much for your feedback.

• Edited by Saeid Hasani Monday, August 12, 2013 8:29 PM


• Proposed as answer by Saeid Hasani Tuesday, August 13, 2013 2:35 PM

WHERE organizational_level = 'Regional'

AND CASE WHEN @in_low_cohesion_flg = 'Operations'

AND store_city_name = 'Seattle'

AND store_status = 'Primary'

WHEN @in_low_cohesion_flg = 'Admin'

AND office_city_name = 'Portland'

THEN 'T' ELSE 'F' END = 'T';

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

• Marked as answer by ConstPM Tuesday, August 13, 2013 2:29 PM

I have seen lots of such queries ( EXPRESSIONS in WHERE clause) that performs badly , and rewriting then with IF ..ELSE block improved it dramatically..

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

Thank you so much for all the great information from the responses. My apologies to everyone for lack of proper protocol.

Thanks especially to Celko for the great theoretical and practical direction. I will get one of his books. However, Celko is probably the last person on the planet I would accept advice on the issue of politeness, however, I appreciate his effort and expertise.

I was able to correct my code accordingly.

However, Celko is probably the last person on the planet I would accept advice on the issue of politeness, however, I appreciate his effort and expertise.

I have a tee shirt that reads: "Social Skills? We don't need no stinkin ' social skills!" :)

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Microsoft is conducting an online survey to understand your opinion of the Msdn Web site. If you choose to participate, the online survey will be presented to you when you leave the Msdn Web site.