Conditional NOT NULL case SQL

Question

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?

Thunell

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

Kastroprauxel

Sure it can be compared using =, < and <>. The result of the comparison is NULL. Agree that three-valued boolean logic is not for everybody, but you can't say 'it cannot be compared'. – Alienage

while this shows how to use CASE, the code in question doesn't even need a case – Flyback

Answer 2

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.

Duomo

Answer 3

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.

Flyback

Answer 4

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`;

Forgive

Answer 5

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.

Peeples