Snowflake SQL for Dynamic IN() Clauses from Session Variables

dynamic_in.sql

--+----------------------------------------------------------------------------------------------------------------------+--
-- Dynamic IN() Clause from a Session Variable
-- David A Spezia
-- Solution Architect
-- Sigma Computing
-- david.spezia@sigmacomputing.com
-- 04 Novemebr 2023
--+----------------------------------------------------------------------------------------------------------------------+--
-- <SQL>
--+----------------------------------------------------------------------------------------------------------------------+--
-- 01.00 - Context
--+----------------------------------------------------------------------------------------------------------------------+--
use warehouse sigma_se_wh;
use role papercrane;
use database snowflake_sample_data;
use schema tpcds_sf10tcl;

--+----------------------------------------------------------------------------------------------------------------------+--
-- 02.00 What is Valind in IN($session_variable)
--+----------------------------------------------------------------------------------------------------------------------+--
-- 02.01 IN(List)
-- Let's use email address from customer, grab 10
select distinct char(39) || c_email_address || char(39) || char(44) from snowflake_sample_data.tpcds_sf10tcl.customer limit 10;

-- Check in list length
select length($$
'Jennifer.Hernandez@rGg8bM.edu',
'Marvin.Sullivan@TY7ObD.org',
'Brian.Murray@yiV6ibbUzb.edu',
'Michael.Morales@kA.com',
'Theresa.Turner@XK0Y42DaHK.edu',
'Rosie.Wagner@FaLJjg6Ql4RMT.edu',
'Janine.Jones@IniTA.org',
'Kay.Pearson@gSPx.edu',
'Charlene.Miller@tqBZQrBth.org',
'Tony.Burch@bsDfZOQ3XVnVGPdn.com'
$$) as char_length;

-- 255 Char Max in Session String Variable
set emails_in = $$'Jennifer.Hernandez@rGg8bM.edu','Marvin.Sullivan@TY7ObD.org','Brian.Murray@yiV6ibbUzb.edu',
'Michael.Morales@kA.com','Theresa.Turner@XK0Y42DaHK.edu','Rosie.Wagner@FaLJjg6Ql4RMT.edu','Janine.Jones@IniTA.org',
'Kay.Pearson@gSPx.edu','Charlene.Miller@tqBZQrBth.org','Tony.Burch@bsDfZOQ3XVnVGPdn.com'$$;

-- Break it up
set emails_in_pt1 = $$'Jennifer.Hernandez@rGg8bM.edu','Marvin.Sullivan@TY7ObD.org','Brian.Murray@yiV6ibbUzb.edu'$$;
set emails_in_pt2 = $$,'Michael.Morales@kA.com','Theresa.Turner@XK0Y42DaHK.edu','Rosie.Wagner@FaLJjg6Ql4RMT.edu'$$;
set emails_in_pt3 = $$,'Janine.Jones@IniTA.org','Kay.Pearson@gSPx.edu','Charlene.Miller@tqBZQrBth.org'$$;
set emails_in_pt4 = $$,'Tony.Burch@bsDfZOQ3XVnVGPdn.com'$$;;

-- Append together
select $emails_in_pt1 || $emails_in_pt2 || $emails_in_pt3 || $emails_in_pt4 as emails_in;

-- Check your work
show variables;

-- 02.02 Single Quoted List
-- OK let's play with the first three to get it to work, then work the full list later
set emails_in = $$'Jennifer.Hernandez@rGg8bM.edu','Marvin.Sullivan@TY7ObD.org','Brian.Murray@yiV6ibbUzb.edu'$$;
select $emails_in;
-- Simple test query
---- !Query Produced No Results!
select c_customer_sk, c_first_name || ' ' || c_last_name as full_name, c_email_address
from snowflake_sample_data.tpcds_sf10tcl.customer
where upper(c_email_address) in(upper($emails_in));

-- Make it work type in
---- !Query Produced No Results!
select c_customer_sk, c_first_name || ' ' || c_last_name as full_name, c_email_address
from snowflake_sample_data.tpcds_sf10tcl.customer
where upper(c_email_address)
in(upper($$'Jennifer.Hernandez@rGg8bM.edu','Marvin.Sullivan@TY7ObD.org','Brian.Murray@yiV6ibbUzb.edu'$$));

-- Make it work type in mk2
select c_customer_sk, c_first_name || ' ' || c_last_name as full_name, c_email_address
from snowflake_sample_data.tpcds_sf10tcl.customer
where c_email_address in('Jennifer.Hernandez@rGg8bM.edu','Marvin.Sullivan@TY7ObD.org','Brian.Murray@yiV6ibbUzb.edu');

-- 02.03 JSON Array List IN()
-- Hmmm...what can we do?  Hammer Time w/ JSON Array!
set emails_in = $$["Jennifer.Hernandez@rGg8bM.edu","Marvin.Sullivan@TY7ObD.org","Curt.Loves.Cubes@yiV6ibbUzb.edu"]$$;

-- Table flatten method to pull list of values from JSON
select value from table(flatten(input => parse_json($emails_in)));

-- Let's use it in our Customers query
select c_customer_sk, c_first_name || ' ' || c_last_name as full_name, c_email_address
from snowflake_sample_data.tpcds_sf10tcl.customer
where upper(c_email_address) in(select upper(value) from table(flatten(input => parse_json($emails_in))));

-- Put it all together
set emails_in_pt1 = $$"Jennifer.Hernandez@rGg8bM.edu","Marvin.Sullivan@TY7ObD.org","Brian.Murray@yiV6ibbUzb.edu"$$;
set emails_in_pt2 = $$,"Michael.Morales@kA.com","Theresa.Turner@XK0Y42DaHK.edu","Rosie.Wagner@FaLJjg6Ql4RMT.edu"$$;
set emails_in_pt3 = $$,"Janine.Jones@IniTA.org","Kay.Pearson@gSPx.edu","Charlene.Miller@tqBZQrBth.org"$$;
set emails_in_pt4 = $$,"Tony.Burch@bsDfZOQ3XVnVGPdn.com"$$;

-- Lifetime sales for emails in()  takes 4 mins on a small
with
customers as (
    select c_customer_sk, c_first_name || ' ' || c_last_name as full_name, c_email_address
    from snowflake_sample_data.tpcds_sf10tcl.customer
    where upper(c_email_address) in(select upper(value) from table(flatten(input => parse_json(
    '[' || $emails_in_pt1 || $emails_in_pt2 || $emails_in_pt3 || $emails_in_pt4 || ']'))))
)
select customers.*, sum(ss_quantity), sum(ss_sales_price), 
    sum(ss_quantity * ss_sales_price) as sales, to_decimal(sum(sales) over (),38,2) as total_sales
from snowflake_sample_data.tpcds_sf10tcl.store_sales ss, customers
where ss.ss_customer_sk in (select distinct c_customer_sk from customers)
group by all;
--+----------------------------------------------------------------------------------------------------------------------+--
-- </SQL>
--+----------------------------------------------------------------------------------------------------------------------+--