Geek asks and answers

CASE statement in SQL WHERE clause

I am trying to extract data from a table where I use a condition CASEin a sentence WHERE, and currently I am using the 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 he returns 0for totaland NULLfor purchase.

+5
source share
2 answers

From your comment .

I want to use Case Statement, could clarify me about random statament in where clause

You can use the operator CASEin the WHEREfollowing way:

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 have to use ELSE 1=1. otherwise you will not get the desired result. See this SQLFiddle for more details.

+16
source

, CASE . , WHERE. , - :

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

More articles:


All Articles