Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

edited September 2021 in General Discussion

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
Tagged:

Answers

  • Jim_KnicelyJim_Knicely Administrator
    edited September 2021

    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)
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.