Check if all the Zip codes are valid.

Best Answer

• Options

  mosheg Vertica Employee Administrator

  August 2021 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)
  

  0