I have a view and want to add a column that basically checks if some columns are null. Here is my sql script for this column
(CASE WHEN `pr`.`SPONSOR` IS NULL OR `pr`.`SPONSOR` ="" THEN 0 ELSE 1 END) + (CASE WHEN `pr`.`POINT_OF_CONTACT` IS NULL OR `pr`.`POINT_OF_CONTACT` = "" THEN 0 ELSE 1 END ) + (CASE WHEN `pr`.`POINT_OF_CONTACT` IS NULL OR `pr`.`POINT_OF_CONTACT` = "" THEN 0 ELSE 1 END ) + (CASE WHEN `pr`.`NEED_CONFIRMED` IS NULL THEN 0 ELSE 1 END )
The problem is that it always adds the values even if doesn't have one. For example SPONSOR is set to take in nulls in the table and set to have null as default value. However, in the view it doesn't say null, it just has no value. I assume this is why it assumes is not null and counts it as 1. Any suggestion to account for both null and empty values?
Discussion posts and replies are publicly visible
Are you using MySQL? If so, try the TRIM & COALESCE functions. For example, the query below will return a 1 when null and nothing when it has a value. So you could use these in your CASE statement.
SELECT 1 FROM `TABLEA` WHERE TRIM(COALESCE(COLA, '')) = ''Hope that helps
I was able to fix it. My issue was using double quotes instead of single quotes