SQL select Where in list of strings

prayner — Fri, 23 Jun 2023 09:23:59 GMT

I am trying to query a table where specific country codes exist in field 'country_code' from a Redshift db. My solution is shared below, but I feel like it could have better performance:

[tbl1]: 
LOAD 
distinct country_code as distinct_country_code // e.g., ='US'
FROM source;

[temp]:
Load
Concat(distinct chr(39) & distinct_country_code & chr(39), ',') as distinct_country_code_str // ='US', 'FR'
Resident tbl1;

LET vCountry_List = Peek('distinct_country_code', 0, 'temp');
drop Table temp;

[tbl2]:
LOAD *;
SELECT *
FROM db.collection
WHERE country_code in ($(vCountry_List)); // Prints as: WHERE country_code in ('US', 'FR');

Any suggestions?

Re: SQL select Where in list of strings

NiTo — Fri, 23 Jun 2023 13:39:08 GMT

Hi,

Try This:

T1:

Load * inline (country_code, US);

Load * from table.qvd where exists(Country_code);

drop t1

topic Re: SQL select Where in list of strings in Connectivity & Data Prep

SQL select Where in list of strings

Re: SQL select Where in list of strings