none
T-SQL: Using CASE Statement in WHERE Clause (Multiple conditions)

    Question

  • 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.

    ....bob sutor

    My Code:

    WHERE

      Field1 = 'Regional'

        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')


    Bob Sutor

    Monday, August 12, 2013 5:49 PM

Answers

  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

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

    Your first problem is that there is no CASE statement in SQL. This is a declarative language and we have a CASE expression. Expressions return scalar values. The CASE works by first finding the data type of the THEN and ELSE clause to use for the result. The WHEN clauses are tested in left-to-right order and the first TRUE is executed. 

    Your next is that you do not know that columns are not fields, so your skeleton code uses a bad word that shows your mindset. Also, why do you think that “condition_#” is a clear, precise, helpful data element name? 

    >> The WHERE Clause has three conditions. Condition1 AND either Condition2 or Condition must be met. I use @in_low_cohesion_flg to determine whether Condition2 or Condition3 must be met. << 

    This has nothing to with SQL; this poor software engineering. A well-designed module of code is coherent; it has one entry an one exit point, and it performs one well-defined task. What you have is a module with flag cohesion; it is controlled by external modules. Read Yourdon, DeMarco, Constantine, et al. The fact that the name of the module has an “or” in it is sign of bad programming. We call these thing “Automobiles, Squid and Lady Gaga” code. 

    CREATE PROCEDURE Operations_Or_Admin_Report 
     (@in_low_cohesion_flg VARCHAR(15))
    AS 
    SELECT ..
      FROM ..
     WHERE organizational_level = 'Regional'
       AND CASE WHEN @in_low_cohesion_flg = 'Operations'
                AND store_city_name = 'Seattle' 
                AND store_status = 'Primary'
                THEN 'T'
                WHEN @in_low_cohesion_flg = 'Admin'
                AND office_city_name = 'Portland'
                THEN 'T' ELSE 'F' END = 'T';

     I also disagree with Saeid Hasan. Since the WHEN clauses evalaute in order, you can force optimizations. 

    --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
    Tuesday, August 13, 2013 12:12 AM

All replies

  • Your question doesn't seem to be clear. Can you post the DDL?

    Known is a DROP, Unknown is an OCEAN.

    Monday, August 12, 2013 6:22 PM
  • CASE in tsql is not quite the same as that in other languages.  You need something like:  

    where Field1 = ...
    and (
    (@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
    Monday, August 12, 2013 8:11 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:

    USE AdventureWorks2008R2
    go
    DECLARE @Variable1 NVARCHAR(128) = 'Admin'
    SELECT *
    FROM Person.Address
    WHERE City = 'Bothell'
    AND 
    (
    	( 
    		CASE WHEN @Variable1 = 'Operations' THEN StateProvinceID END = 79
    		AND 
    		CASE WHEN @Variable1 = 'Operations' THEN AddressLine1 END LIKE '%s%'
    	)
    		OR CASE WHEN @Variable1 = 'Admin' THEN AddressLine1 END LIKE '%st.%'
    )


    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
    Monday, August 12, 2013 8:27 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

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

    Your first problem is that there is no CASE statement in SQL. This is a declarative language and we have a CASE expression. Expressions return scalar values. The CASE works by first finding the data type of the THEN and ELSE clause to use for the result. The WHEN clauses are tested in left-to-right order and the first TRUE is executed. 

    Your next is that you do not know that columns are not fields, so your skeleton code uses a bad word that shows your mindset. Also, why do you think that “condition_#” is a clear, precise, helpful data element name? 

    >> The WHERE Clause has three conditions. Condition1 AND either Condition2 or Condition must be met. I use @in_low_cohesion_flg to determine whether Condition2 or Condition3 must be met. << 

    This has nothing to with SQL; this poor software engineering. A well-designed module of code is coherent; it has one entry an one exit point, and it performs one well-defined task. What you have is a module with flag cohesion; it is controlled by external modules. Read Yourdon, DeMarco, Constantine, et al. The fact that the name of the module has an “or” in it is sign of bad programming. We call these thing “Automobiles, Squid and Lady Gaga” code. 

    CREATE PROCEDURE Operations_Or_Admin_Report 
     (@in_low_cohesion_flg VARCHAR(15))
    AS 
    SELECT ..
      FROM ..
     WHERE organizational_level = 'Regional'
       AND CASE WHEN @in_low_cohesion_flg = 'Operations'
                AND store_city_name = 'Seattle' 
                AND store_status = 'Primary'
                THEN 'T'
                WHEN @in_low_cohesion_flg = 'Admin'
                AND office_city_name = 'Portland'
                THEN 'T' ELSE 'F' END = 'T';

     I also disagree with Saeid Hasan. Since the WHEN clauses evalaute in order, you can force optimizations. 

    --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
    Tuesday, August 13, 2013 12:12 AM
  • Bob 

    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/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, August 13, 2013 4:08 AM
  • 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.

    Regards,

    ....bob


    Bob Sutor

    Tuesday, August 13, 2013 2:29 PM
  • 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

    Tuesday, August 13, 2013 3:50 PM