SQL – Using CASE statement with multiple IS NULL, IS NOT NULL

I’m having difficulties writing a case statement with multiple IS NULL, NOT NULL conditions. I have the case statement below, however the third condition (WHEN ID IS NOT NULL AND LABEL IS NULL THEN TITLE) does not seem to be recognised. When Label is null, the statement does not pick up title. Is there a different way to write this case statement?

CASE WHEN ID IS NULL THEN TEXT 
WHEN ID IS NOT NULL THEN LABEL
WHEN ID IS NOT NULL AND LABEL IS NULL THEN TITLE
END AS DESCRIPTION

Thanks

Answer

The CASE expression stops that the first match. So you want the more restrictive conditions first. In your case:

(CASE WHEN ID IS NOT NULL AND LABEL IS NULL THEN TITLE
      WHEN ID IS NOT NULL THEN LABEL
      WHEN ID IS NULL THEN TEXT
 END) AS DESCRIPTION

Or, a simpler formulation is:

(CASE WHEN ID IS NULL THEN TEXT
      WHEN LABEL IS NULL THEN TITLE
      ELSE LABEL          
 END) AS DESCRIPTION

After the first condition, we know that ID IS NOT NULL, so there is no need to check that again.