locked
CASE statement in where clause RRS feed

  • Question

  • Dear All,

    I am trying to write a case statement in where clause and facing some issues. I want to run a condition based on value of a variable . But my query is giving errors .My example is as follows:

    DECLARE @capID int
    SET @capID =(select  capID from products where pkid=1)

    SELECT * from products where name='ABC'
    AND
    CASE WHEN @capID>88 THEN
    getdate() BETWEEN sales_begin AND sales_end
    END

    I mean that I want to run the case statement only when @capID>88. if its less than 88 than it doesnt do anything.


     


    Sunday, August 24, 2014 12:41 PM

Answers

  • You can do like below

    DECLARE @capID int
    SET @capID =(select  capID from products where pkid=1)
    
    SELECT * from products where name='ABC'
    AND (getdate() BETWEEN sales_begin AND sales_end
    OR @capID <= 88)
    END

    It will work fine so far as you've a single record with pkid = 1 in products table


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, August 24, 2014 1:20 PM

All replies

  • Hi I believe you dont need to use case DECLARE @capID int SET @capID =(select capID from products where pkid=1) SELECT * from products where name='ABC' AND (@capID>88 AND ......) Not sure what you want to do with sales_begin and sales_end, but BETWEEN is not used correctly

    Javier Villegas | @javier_villhttp://sql-javier-villegas.blogspot.com/

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Sunday, August 24, 2014 12:50 PM
  • You can do like below

    DECLARE @capID int
    SET @capID =(select  capID from products where pkid=1)
    
    SELECT * from products where name='ABC'
    AND (getdate() BETWEEN sales_begin AND sales_end
    OR @capID <= 88)
    END

    It will work fine so far as you've a single record with pkid = 1 in products table


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, August 24, 2014 1:20 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. You have no idea. 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. Ignorance is fine, but why do you have bad manners? The rules are posted at the front of the forum! 

    >> I am trying to write a CASE statement in WHERE clause and facing some issues. <<

    The first issue is that SQL has no CASE statement! We have a CASE expression; you never read any book on SQL, have you? Almost everything wrong. :(

    >> DECLARE @cap_id INTEGER;
    SET @cap_id =(SELECT cap_id FROM Products WHERE pk_id = 1);<<

    SQL is a declarative language. We do not use local variables, so @cap_id is bad code; use the expression instead.  Your “pk_id” looks like a primary key of some kind, but the product_name is a nightmare; the “pk_” affix is meta-data! Remember the first week of RDBMS classes?  never mix data and meta-data. 

    We do not use getdate() anymore; that was UNIX/Sybase in the 1980's. The 

    CASE is not a control flow statement! Why did you think that would work? Without any DDL or specs, here is my guess: 

    SELECT * -- this is bad, use a column list
      FROM Products 
     WHERE product_name = 'ABC'
       AND (CURRENT_TIMESTAMP AS DATE)
           BETWEEN sales_begin_date AND sales_end_date
       AND cap_id > 88;



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

    Sunday, August 24, 2014 2:23 PM