CODEWITHSUNDEEP
mysql
Gowri
Gowri

Reputation: 16835

How to use case in where clause

I want to get only status = 1 records. But I don't have status column in my table. So I derived the value using CASE... WHEN... THEN. But when I try to use case in where clause, It shows syntax error.

my query

SELECT SQL_CALC_FOUND_ROWS *,
    CASE
        WHEN quantity > num_used AND (CURDATE() BETWEEN coupon_start_date AND coupon_end_date) THEN '1'
        ELSE '0'
    END AS STATUS
FROM
    table_coupon_code
WHERE
    (CASE
        WHEN quantity > num_used AND (CURDATE() BETWEEN coupon_start_date AND coupon_end_date) THEN '1'
        ELSE '0'
    END AS STATUS) = '1' AND coupon_status <> '2'

How can I do this ?

Upvotes: 6

Views: 27248

Answers (4)

Adam Fowler
Adam Fowler

Reputation: 1751

Nobody Suggested the HAVING Clause?

This allows you to query the selected columns, instead of the actual results. Great for using case and function calls.

Upvotes: 1

rabudde
rabudde

Reputation: 7722

remove AS STATUS from where clause

SELECT SQL_CALC_FOUND_ROWS * ,
  CASE WHEN quantity > num_used AND (CURDATE( ) BETWEEN coupon_start_date AND coupon_end_date)
  THEN '1'
  ELSE '0'
  END AS STATUS
FROM table_coupon_code
WHERE 
CASE WHEN quantity > num_used AND (CURDATE( ) BETWEEN coupon_start_date AND coupon_end_date)
  THEN '1'
  ELSE '0'
  END = '1'
AND coupon_status <> '2'

But your CASE is really unnecessary. Just use your CASE condition as stand-alone WHEREcondition, like 

[...]
WHERE quantity > num_used AND
CURDATE( ) BETWEEN coupon_start_date AND coupon_end_date AND
coupon_status <> '2'

Upvotes: 16

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

I suppose you have some other, more complicated query, as the one you have provided is equivalent to:

SELECT SQL_CALC_FOUND_ROWS * ,
       '1' AS STATUS
FROM table_coupon_code
WHERE quantity > num_used
  AND CURDATE() BETWEEN coupon_start_date AND coupon_end_date
  AND coupon_status <> '2'

Upvotes: 0

Olaf
Olaf

Reputation: 31

If you do not want to repeat the case statement you could wrap the select in a subselect or make a view. Subselect is something like 

select status 
  from (select case 
          when zip like '4321%' then 1 else 0 end as status
          from adr 
       ) t 
 where status = 1;

Upvotes: 3

Related Questions