Need a match function for Vertica SQL
I have 1 table in Vertica with a list of codes. Each field has 1 code in it (text) e.g. 123456
My 2nd table in Vertica has a list of codes. Each field can have more than 1 code in it (text) separated by a comma e.g. 123456,522122,345644
I want a query to select all from table 2 and add a column at the end called "Match" with a "Y" if the multiple code field contains any code from table 1
The example shown above should have a match of "Y"
Tagged:
0
Answers
What version of Vertica are you running? This is relatively easy in Vertica 10.1.x
Example:
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!
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);
-----------------------+-------
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:
Or you could run this SQL:
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
\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:
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])
Tried this one too but have error: SQL Error [4856] [42601]: [Vertica]VJDBC ERROR: Syntax error at or near "from"
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(StringTokenizerDelim(o.'service code',',') over (partition by o.'intake id') from wfmgmt_prd.open_report)
where o.'Report Date' = '2021-06-09' and o.'service code' like '%1628%';
Found out this will break up the service codes in T2 but how do I write this into the query to code the Match as a 'Y'?
SELECT v_txtindex.StringTokenizerDelim(o.'service code',',') over()
from WFMGMT_PRD.open_report o
where o.'Report Date' = '2021-06-09' and o.'service code' like '%1628%';
I have no idea why your STRING_TO_ARRAY function isn't working.
Anyway, you can use StringTokenizerDelim like the Explode function...
Example:
Send me the DDL for each of your tables and I can rewrite the above for you.
To get the DDL you need to run these two commands:
SELECT export_objects('','wfmgmt_prd.open_report');
SELECT export_objects('','WFMGMT_PRD.map_hcpc_lifesustain');
Post the results here or send them as a message directly to me.
SQL Error [5297] [0A000]: [Vertica]VJDBC ERROR: Unsupported use of LIMIT/OFFSET clause when running export_objects
I can tell you that Table 1 = map_hcpc_lifesustain (Varchar(20) field called 'SERVICECODE') - this is a list with single service codes
Table 2 = open_report (Varchar(500)field called 'service code') - this has multiple service codes with comma between
I need a select query to add a field when I select all from T2(open_report) that says whether the 'service code' matches to one of the 'SERVICECODE' IN T1(map_hcpc_lifesustain
This seems to be working but it is grouping the 'service code' from Table 2 - I need to show all the rows from table 2 with the Match at the end.
SELECT foo.'service code', NVL2(l.SERVICECODE, 'Y', 'N') "Match"
FROM (SELECT o.'service code', v_txtindex.StringTokenizerDelim(o.'service code', ',') OVER (PARTITION BY o.'service code')
FROM wfmgmt_prd.open_report o
where o.'Report Date' = '2021-06-09' and o.'service code' like '%1628%') foo
LEFT JOIN WFMGMT_PRD.map_hcpc_lifesustain l
ON foo.words = l.SERVICECODE
LIMIT 1 OVER (PARTITION BY foo.'service code' ORDER BY foo.'service code', NVL2(l.SERVICECODE, 'Y', 'N') DESC)