I would like to use array parameter binding in SELECT like SQL statement, like in the following example (please note the limit0 CTE)

WITH
    limit0(val)
    AS
    (
        SELECT * FROM (?)
    ),
    total(limit0val)
    AS
    (
        SELECT DISTINCT limit0.val
        FROM limit0
    )
SELECT total.limit0val
FROM total;

The input value of the ? is an array of values and I would like to use these values as any other CTEs (filtering, joining on it, etc) The problem is that I couldn't find a way to bind an array into a SELECT statement.

Can anybody help me, how to refactor this query?

Applied code stack:

1. c
2. ODBC
3. Snowflake & Redshift compliance SQL

CodePudding user response：

So if we start with some SQL that works:

So array parameters could mean I will pass in arbitry array of values thus you want the SQL to be effectively:

WITH limit0(val) AS (
    SELECT * FROM VALUES (0),(1),(0),(1),(2)
), total(limit0val) AS (
    SELECT DISTINCT limit0.val 
    FROM limit0
)
SELECT total.limit0val
FROM total;

thus the input might be (0),(1),(0),(1),(2) OR you might mean I have an array that I pass as a single string value, and parse inside the SQL, thus:

WITH limit0(val) AS (
    SELECT try_to_number(t.value) FROM TABLE(split_to_table('0, 1, 0, 1,2', ',')) t
), total(limit0val) AS (
    SELECT DISTINCT limit0.val 
    FROM limit0
)
SELECT total.limit0val
FROM total;

could be used and the input would be a single parameter '0, 1, 0, 1,2'

The latter seems easer to me.

I also assume you have read the ODBC Binding Parameters to Array Variables For Batch Inserts help.

CodePudding user response：

You can't bind arrays, but you can create a delimited string and use the STRTOK_TO_ARRAY function to parse it to an array:

select strtok_to_array('MEMBER_1,MEMBER_2,MEMBER_3', ',') as MY_ARRAY;

You can specify the delimiter you want to use. You can then use a string bind variable like this:

select strtok_to_array(?, ',') as MY_ARRAY;