Need a match function for Vertica SQL

What version of Vertica are you running? This is relatively easy in Vertica 10.1.x

Example:

verticademos=> SELECT * FROM t1;
   c
--------
 123456
 555555
 666666
(3 rows)

verticademos=> SELECT * FROM t2;
           c
-----------------------
 123456,522122,345644
 231423,666666,124132
 12345,678910,11121314
(3 rows)

verticademos=> ALTER TABLE t2 ADD COLUMN "Match" VARCHAR(1);
ALTER TABLE

verticademos=> UPDATE t2 SET "Match" = 'Y' FROM (SELECT c FROM (SELECT c, explode(STRING_TO_ARRAY(c USING PARAMETERS collection_delimiter = ',')) OVER (PARTITION BY c) FROM t2) foo WHERE EXISTS (SELECT NULL FROM t1 WHERE t1.c = foo.value)) foo WHERE foo.c = t2.c;
 OUTPUT
--------
      2
(1 row)

verticademos=> SELECT * FROM t2;
           c           | Match
-----------------------+-------
 12345,678910,11121314 |
 123456,522122,345644  | Y
 231423,666666,124132  | Y
(3 rows)

thanks - going to try to plug this in and I'll let you know:)
not sure what version of Vertica I'm using

You can get the version with the VERSION function!

verticademos=> SELECT version();
               version
-------------------------------------
 Vertica Analytic Database v10.1.1-1
(1 row)

I'm getting an error testing this - not good with SQL:)

SQL Error [4856] [42601]: [Vertica]VJDBC ERROR: Syntax error at or near "verticademos"

How do I place this in my current query - as follows?

Select o.*
from db.t2 o
where where "Report Date" >= (select max("Report Date") from db.t2)

I just want to add a field at the end of my columns for the Match column Y/N

Another variation of that if you just want a query:

select t2.c, decode(t1.c,null,'N','Y') "Match" from t2 left join t1 on contains(string_to_array(t2.c),t1.c);

       c           | Match

-----------------------+-------
231423,666666,124132 | Y
123456,522122,345644 | Y
12345,678910,11121314 | N
(3 rows)

As Jim indicated, the SQL will be slightly different if you are pre-10.1 and pre-10.1.1.

SQL Error [3457] [42883]: [Vertica]VJDBC ERROR: Function string_to_array(varchar) does not exist, or permission is denied for string_to_array(varchar)

I have the Array function but not string_to_array

Rather than on contains(string to array... - is it possible to use a where statement here?

Thanks for your help directing me - I found the following works:

SELECT o.'service code',decode(l.SERVICECODE,null,'N','Y')"Match"
from report o
left join lifesustain l on contains(array[o.'service code'],l.SERVICECODE)

Well, it seems to be matching 1 to 1 but when there are multiples in T2 such as ( 12345,678910,11121314), it isn't matching when the code is in T1 (678910)

What does c represent in this statement?

UPDATE t2 SET "Match" = 'Y' FROM (SELECT c FROM (SELECT c, explode(STRING_TO_ARRAY(c USING PARAMETERS collection_delimiter = ',')) OVER (PARTITION BY c) FROM t2) foo WHERE EXISTS (SELECT NULL FROM t1 WHERE t1.c = foo.value)) foo WHERE foo.c = t2.c;

It is the column name in the sample tables I used (T1 and T2).

So my query is as follows - can you show me how to add in the explode (string to array) which will create another field at the end based on the tables I gave you?

SELECT *
from T2

You haven't told us the Vertica version yet

First check if you have the EXPLODE function by running \df in vsql:

verticademos=> \df explode
                         List of functions
 procedure_name | procedure_return_type | procedure_argument_types
----------------+-----------------------+--------------------------
 Explode        | Any                   | Any
(1 row)

Or you could run this SQL:

verticademos=> SELECT function_name FROM user_functions WHERE function_name = 'Explode';
 function_name
---------------
 Explode
(1 row)

Version: Vertica Analytic Database v10.1.0-2 - Yes, the Explode function is there

The query I provided will work but you'd need to change the STRING_TO_ARRAY syntax until you get to 10.1.1.

It will be one of these two (can't recall which version started using different syntax but I gave an example for 10.1.1 which is better and clearer):
string_to_array('['||t2.c||']',',')
or
string_to_array('['||t2.c||']' using parameters collection_delimiter=',')

The string_to_array function was not found? SELECT function_name FROM user_functions WHERE function_name = 'string_to_array';

You won't see it in USER_FUNCTIONS.

Try \df

verticademos=> \df string_to_array
                         List of functions
 procedure_name  | procedure_return_type | procedure_argument_types
-----------------+-----------------------+--------------------------
 string_to_array | VarcharArray1D        | Varchar
 string_to_array | VarcharArray1D        | Varchar, Varchar
(2 rows)

\df string_to_array SQL Error [4856] [42601]: [Vertica]VJDBC ERROR: Syntax error at or near "\"

You have to run \df in vsql!

Or just try this:

SELECT string_to_array('[' || 'Vertica' || ']');

SQL Error [3457] [42883]: [Vertica]VJDBC ERROR: Function string_to_array(varchar) does not exist, or permission is denied for string_to_array(varchar)

ok tried this query:

SELECT o.'service code',decode(l.SERVICECODE,null,'N','Y')"Match"
from wfmgmt_prd.open_report o
left join WFMGMT_PRD.map_hcpc_lifesustain l on contains(string_to_array('['||o.'service code'||']' using parameters collection_delimiter=','))
where o.'Report Date' = '2021-06-09' and o.'service code' like '%1628%'

Getting error: SQL Error [3457] [42883]: [Vertica]VJDBC ERROR: Function string_to_array(varchar) does not exist, or permission is denied for string_to_array(varchar)

WFMGMT_PRD.map_hcpc_lifesustain = T1
wfmgmt_prd.open_report = T2

Try:

SELECT o.'service code',decode(l.SERVICECODE,null,'N','Y')"Match"
from wfmgmt_prd.open_report o
left join WFMGMT_PRD.map_hcpc_lifesustain l on contains(string_to_array('['||o.'service code'||']', ','))
where o.'Report Date' = '2021-06-09' and o.'service code' like '%1628%'

SQL Error [3457] [42883]: [Vertica]VJDBC ERROR: Function contains(array[varchar]) does not exist, or permission is denied for contains(array[varchar])