CODEWITHSUNDEEP
snowflake-cloud-data-platform
user3165854
user3165854

Reputation: 1655

Use comma delimited list from column values in Snowflake IN statment

I am writing some logic for a row access policy and have a column for departments and what I would like to do is convert it into a comma separated list which I can use as input into an IN statement to select departments from specific filter criteria. At the moment I am just trying to get the IN statement part working and I cam able to create the column separated list and if I cut and paste the result into the IN statement it works but when I use the SQL that created the list it returns no rows.

Can anyone see what I'm doing wrong?

  WITH 
    dept_map AS (SELECT (CONCAT('\'', (select listagg(dept, '\',\'') from dept_mapping_table), '\'')) AS dept_list)
    (
      SELECT * FROM dept WHERE dept_name IN (select dept_list from debt_map)
    );

Thanks for any help

Upvotes: 1

Views: 2089

Answers (1)

Rich Murnane
Rich Murnane

Reputation: 2920

I'm not sure I understand your use case and as NickW mentioned, for row access policies you can modify the query to a simple IN statement.

But, if you have to do some sort of parse list IN type of command, this might help.

CREATE TEMPORARY TABLE abc (str varchar);
INSERT INTO abc VALUES ('a'),('b'),('c');

CREATE TEMPORARY TABLE abc_xyz (str varchar);
INSERT INTO abc_xyz VALUES ('a'),('b'),('c'),('x'),('y'),('z');

--test the listagg function, use ~ instead of escaping backslash
SELECT listagg(str, '~') FROM abc;
SELECT listagg(str, '~') FROM abc_xyz;

--test out your listagg
SELECT b.value::string as str_part
FROM (select listagg(str, '~') str_agg from abc_xyz) a,
     lateral flatten(input=>split(a.str_agg, '~')) b;
     
--get records from table abc where str in abc_xyz
SELECT *
FROM   abc 
WHERE  str in (
    SELECT b.value::string as str_part
    FROM (select listagg(str, '~') str_agg from abc_xyz) a,
          lateral flatten(input=>split(a.str_agg, '~')) b);

--results
STR
a
b
c

Final thought: My opinion on row access policies is that they should be easy to understand and not complicated to implement. They are an incredibly powerful feature in Snowflake but if created poorly they could lead to data loss and/or other security issues, so I hope you make yours only as complicated as they need to be, no more complicated.

Upvotes: 1

Related Questions