Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

CASE Statement in SQL WHERE clause

Tags:

mysql

case

I'm trying to fetch data from table where I'm using a CASE condition in the WHERE clause and currently I'm using following query:-

SELECT count(enq_id) AS total, sum(purchase_amount) AS purchase
FROM temp_stock
WHERE purchase_date <> '0000-00-00'
AND purchase_date < '2012-08-01'
AND (

STATUS = 'Sold'
OR STATUS = 'In Stock'
OR STATUS = 'Ref'
)
AND CASE WHEN (

STATUS = 'Sold'
)
THEN delivery_date >= '2012-08-01'
END

But it returns 0 for total and NULL for purchase.

like image 859
guri Avatar asked Sep 13 '12 06:09

guri


People also ask

Can we use case in WHERE clause in SQL?

CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.

Can we write CASE statement in WHERE clause?

Another way to use the Case Statement is within the WHERE clause. There, it may be utilized to alter the data fetched by a query based on a condition. Within that context, the Case Statement is ideally suited to both static queries, as well as dynamic ones, such as those that you would find inside a stored procedure.

Which is a case WHERE the SQL?

The SQL CASE ExpressionThe CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

How do you write a CASE statement in SQL?

SQL Case Statement Syntax Then for a single condition you can write the keyword WHEN followed by the condition that has to be satisfied. After that comes the keyword THEN and the value for that condition, like WHEN <condition> THEN <stuff> . This can then be followed by other WHEN / THEN statements.


2 Answers

From your comment.

I want to use Case Statement, could u pls clarify me about case statament in where clause

You can use CASE statement in WHERE like this:

SELECT count(enq_id) AS total, sum(purchase_amount) AS purchase
FROM temp_stock
WHERE purchase_date <> '0000-00-00'
AND purchase_date < '2012-08-01'
AND (    STATUS = 'Sold'
      OR STATUS = 'In Stock'
      OR STATUS = 'Ref')
AND CASE STATUS 
         WHEN 'Sold' 
         THEN delivery_date >= '2012-08-01'
         ELSE 1=1
    END

Here you need to use ELSE 1=1. otherwise you will not get desired result. For more explanation see this SQLFiddle

like image 200
Himanshu Jansari Avatar answered Oct 15 '22 04:10

Himanshu Jansari


I don't think that CASE can work that way. What you want is a slightly more complex expression as your WHERE clause. Probably something like this:

SELECT count(enq_id) AS total, sum(purchase_amount) AS purchase
FROM temp_stock
WHERE purchase_date <> '0000-00-00'
  AND purchase_date < '2012-08-01'
  AND (
     (STATUS = 'Sold' AND delivery_date >= '2012-08-01')
   OR STATUS = 'In Stock'
   OR STATUS = 'Ref'
 )
like image 24
Ian Clelland Avatar answered Oct 15 '22 04:10

Ian Clelland