Conditional NOT NULL case SQL

sql mysql null mysql-error-1064
Spencer picture Spencer · Jan 27, 2011 · Viewed 81.3k times · Source

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?

Answer

Joe Stefanelli picture Joe Stefanelli · Jan 27, 2011

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.

Related questions

Checking for an empty field with MySQL

I've wrote a query to check for users with certain criteria, one being they have an email address. Our site will allow a user to have or not have an email address. $aUsers=$this->readToArray(' SELECT `userID` FROM `…

Read More
MySQL CONCAT returns NULL if any field contain NULL

I have following data in my table "devices" affiliate_name affiliate_location model ip os_type os_version cs1 inter Dell 10.125.103.25 Linux Fedora cs2 inter Dell 10.125.103.26 Linux Fedora cs3 inter Dell 10.125.103.27 NULL NULL cs4 inter Dell 10.125.103.28 NULL NULL I executed …

Read More
MySQL comparison with null value

I have a column called CODE in a MySQL table which can be NULL. Say I have some rows with CODE='C' which I want to ignore in my select result set. I can have either CODE=NULL or CODE!=…

Read More