I have found after much annoyance, that an SQL query I had in production was incorrectly checking for NULL in my SQL statement.
This is a simple school-boy error that I will now never do again.
The issue is that if you have a case statement as follows:
CASE WHEN <Value to check> = NULL THEN <Value to replace NULL> ELSE <Value that is not null> END
This will always return NULL when there is a NULL value that is in the value check.
However, all you need to do is change the ‘=’ to an ‘IS’ and it will calculate correctly
CASE WHEN <Value to check> IS NULL THEN <Value to replace NULL> ELSE <Value that is not null> END