Conditional NOT NULL case SQL

Question

I am trying to compute a field, and I want it to behave differently depending on whether one of the columns is 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 correct syntax?

Answer 1

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 NOT NULL are used.

Answer 2

 case when reply.replies IS NOT NULL ... 

You cannot compare NULL with regular (arithmetic) comparison operators. Any arithmetic comparison with NULL will return NULL, even NULL = NULL or NULL <> NULL will return NULL.

Use IS or IS NOT instead.

Answer 3

You do not need a case argument 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 response.replies is null, the expression is a shortcut for NULL
IFNULL then takes the second parameter (1) and gives this as a result when this happens.

In other cases, when you need to compare with NULL, this will help you work with MySQL.

Answer 4

You can make a CASE Check Null expression

 SELECT MAX(id+1), IF(MAX(id+1) IS NULL, 1, MAX(id+1)) AS id FROM `table_name`;