CASE statement in where clause

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.

 

Answer 1

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_vill | http://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

Answer 2

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

Answer 3

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