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.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.