CODEWITHSUNDEEP
mysql
Yanick Lafontaine
Yanick Lafontaine

Reputation: 190

MySQL with IFNULL in where clause

I have MySQL table with this row :

+----------+--------+------+-------+
| list_uid | sku    | qty  | verif |
+----------+--------+------+-------+
|       49 | 024522 | 10   | 8     |
+----------+--------+------+-------+

I try to select this row with this query :

SELECT
  *
FROM
  ae_rf_list_picked
WHERE
  `qty` != 0 AND
  IFNULL(`verif`, 0) < `qty` AND 
  `sku` = '024522' AND 
  `list_uid` = 49

I don't understand why this row is not return by this query. If I remove this part

IFNULL(verif, 0) < qty AND

the query return the row.

Upvotes: 0

Views: 1221

Answers (1)

xQbert
xQbert

Reputation: 35323

Perhaps you need to cast verif and qty to numeric values

Assuming no decimals and < 10 characters...

SELECT *
FROM ae_rf_list_picked
WHERE `qty` != 0 
  AND cast(IFNULL(`verif`, 0) as Numeric(10)) < cast(`qty` as numeric(10))  
  AND `sku` = '024522' 
  AND `list_uid` = 49

Upvotes: 2

Related Questions