Yanick Lafontaine Yanick Lafontaine - 1 month ago 11
MySQL Question

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.

Answer

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
Comments