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

Check if all the Zip codes are valid.

Hey folks,

I have a list of Zips like this:
ZIP_LIST = '20109 20166 20601 20716 21014 21090 21093 21222 21236 21601 21704 21740 21784'

And I also have a Vertica table that has a Zips column. I want to check if there's a Zip in my list that isn't present in that Zips column, hence is invalid.

Is there a way to match a list with a column in the Vertica table?

Tagged:

Best Answer

  • moshegmosheg Employee
    Accepted Answer

    Try this..

    CREATE TABLE my_t1(f1 INT, f2 ARRAY[INT]);
    CREATE TABLE my_t2(f1 INT, ZIP INT);
    
    INSERT INTO my_t1 VALUES(1,ARRAY[20109,20166,20601,20716,21014,21090,21093,21222,21236,21601,21704,21740,21784]);
    COPY my_t2 FROM STDIN DELIMITER ',' ABORT ON ERROR;
    1,20109
    2,20166
    3,20601
    4,20716
    5,21014
    6,21090
    7,21093
    8,21222
    9,21236
    10,22222
    11,21704
    12,21740
    13,21784
    \.
    
    
    SELECT my_t2.f1, my_t2.ZIP, CONTAINS(my_t1.f2,my_t2.ZIP)
    FROM my_t1,my_t2;
     f1 |  ZIP  | CONTAINS
    ----+-------+----------
       0 | 20109 | t
       1 | 20166 | t
       2 | 20601 | t
       3 | 20716 | t
       4 | 21014 | t
       5 | 21090 | t
       6 | 21093 | t
       7 | 21222 | t
       8 | 21236 | t
       9 | 11111 | f
     10 | 21704 | t
     11 | 21740 | t
     12 | 22222 | f
     13 | 33333 | f
    (14 rows)
    
    SELECT my_t2.ZIP AS 'ZIP codes not found in the array'
    FROM my_t1,my_t2
    WHERE NOT CONTAINS(my_t1.f2,my_t2.ZIP);
     ZIP codes not found in the array
    ----------------------------------
                                11111
                                22222
                                33333
    (3 rows)
    
    WITH
    list1 AS (SELECT EXPLODE(my_t1.f2) OVER(PARTITION BEST) FROM my_t1)
    SELECT list1.position AS 'Array position', list1.value AS 'Invalid ZIP'
    FROM    list1
            LEFT JOIN my_t2
                ON list1.value IN (my_t2.ZIP)
    WHERE   my_t2.ZIP IS NULL;
     Array position | Invalid ZIP
    ----------------+-------------
                             9 |       21601
                          12 |       21784
    (2 rows)
    

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.