Does Vertica CASE statement (syntax form 2) recognize NULL values?
Sorry! I now see this was previously answered
https://forum.vertica.com/discussion/239767/null-equals-null-with-nullsequal
How can I get Vertica CASE statement syntax form 2 to recognize NULL values?
For instance, the following query I would expect and prefer it to recode empty strings to 9 or even 8 but it falls through to the else clause and returns 7. Is this the expected behavior?
SELECT (
CASE status
WHEN 'Yes' THEN 1
WHEN 'Reviewed and blocked' THEN 0
WHEN NULL THEN 9
WHEN '' THEN 8
ELSE 7
END
) AS coded,
COUNT(*) AS freq
FROM myschema.mytable
GROUP BY 1
| coded | freq |
|---|---|
| 0 | 21 |
| 1 | 522 |
| 7 | 1532 |
0
Answers
You have to handle NULL a little differently...
Example:
verticademos=> CREATE TABLE t(c VARCHAR); CREATE TABLE verticademos=> INSERT INTO t SELECT NULL; OUTPUT -------- 1 (1 row) verticademos=> SELECT CASE c WHEN NULL THEN 7 END FROM t; case ------ (1 row) verticademos=> SELECT CASE WHEN c IS NULL THEN 7 END FROM t; case ------ 7 (1 row) verticademos=> SELECT CASE NVL(c, 'NULL') WHEN 'NULL' THEN 7 END FROM t; case ------ 7 (1 row)Or like in that link...
verticademos=> SELECT CASE c WHEN NULLSEQUAL NULL THEN 7 END FROM t; case ------ 7 (1 row)