Does Vertica CASE statement (syntax form 2) recognize NULL values?

Pieter_Sheth-Vo

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?

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/LanguageElements/Expressions/CASEExpressions.htm

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

Jim_Knicely

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)