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

Question

Sorry!  I now see this was previously answeredhttps://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 · Answer

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)

Sign up

Please log in or register:

Welcome to the Rocket Forum!

Please log in or register: