Check if all the Zip codes are valid.

dp2810

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?

mosheg

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)