Conditional Not Null Case Sql
I Am Trying to Calculate a Field and I Want It to Behave Differently Depending on If One of the Columns Happens to Be Null. I Am Using Mysql Case When Reply...
I am trying to calculate a field and I want it to behave differently depending on if one of the columns happens to be null. I am using MySQL
CASE
WHEN reply.replies <> NULL THEN
24/((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(qcr.LAST_MOD_TIME)+3600)/3600)*(ces.EXPERT_SCORE+2.5*scs.SIMILARITY)*(EXP(-reply.replies))
ELSE 1
END as ANSWER_SCORE
Is this the right syntax?
5 Answers
You need to have when reply.replies IS NOT NULL
NULL is a special case in SQL and cannot be compared with = or <> operators. IS NULL and IS NOT NULL are used instead.
case when reply.replies IS NOT NULL ...
You can't compare NULL with the regular (arithmetic) comparison operators. Any arithmetic comparison to NULL will return NULL, even NULL = NULL or NULL <> NULL will yield NULL.
Use IS or IS NOT instead.
You don't need a case statement for this.
Use the IFNULL function
IFNULL(24/((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(qcr.LAST_MOD_TIME)+3600)/3600)
*(ces.EXPERT_SCORE+2.5*scs.SIMILARITY)*(EXP(-reply.replies)), 1) as ANSWER_SCORE
If reply.replies is null, the expression is shortcut to NULL
IFNULL then takes the 2nd parameter (1) and gives that as a result when it happens.
For other cases where you do need to compare to NULL, this will help you to work with MySQL.
You can make a CASE Statement Checking Null
SELECT MAX(id+1),
IF(MAX(id+1) IS NULL, 1, MAX(id+1))
AS id
FROM `table_name`;
it would be best if you used is not null in SQL syntax, the '<>' is means the column has value to compare with other sentences.
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
