Snowflake UDF using column as parameter

Question

Is it possible to call a UDF in Snowflake using a view column as an input parameter? I have a column with multiple dates delimited by | and I want to check if at least one of those dates falls between a given range for each row.

I tried the below code but it is throwing an "Invalid expression in VALUES clause" error. It works when you pass individual values manually, but not when you pass a column as the input parameter DATE_LIST

CREATE OR REPLACE FUNCTION CHECK_DATE_RANGE (DATE_LIST VARCHAR, START_DT DATE, END_DT DATE)
RETURNS table (a boolean)
LANGUAGE SQL
AS 
'WITH data_list AS (
    SELECT ORDER_DT_LIST
    FROM tableName
    WHERE DATE_LIST = ORDER_DT_LIST
),
data_range AS (
    SELECT value BETWEEN USER_START_DT AND USER_END_DT AS TF
    FROM data_list, lateral split_to_table(ORDER_DT, ''|'')
    ORDER BY seq, index
),
data_bool AS (
    SELECT CASE WHEN SUM(CASE WHEN TF=TRUE THEN 1 ELSE 0 END) > 0 THEN TRUE 
    ELSE FALSE 
    END AS CHECK_DATE_RANGE
    FROM data_range
)
select * FROM data_bool
';

You'll probably need "Dynamic sql" in the procedure to accomplish this. Some good examples can be found on Snowflakes page Constructing SQL at runtime specifically in the section about dynamic sql — JNevill
– JNevill, Commented Sep 23, 2024 at 20:00
Looks like this might not work for UDFs. I stritctly need it to be a function because it would be passed within a Cognos query — Foink
– Foink, Commented Sep 23, 2024 at 22:31

Simeon Pilgrim · Accepted Answer · 2024-09-23 23:03:59Z

0

given the UDF you have shown, you do not really need a UDF or a UDTF, and can do it all inline:

with tablename(order_dt_list, start_dt, end_dt) as (
    select * from values
        ('2024-09-24', '2024-08-01'::date,'2024-08-31'::date),
        ('2024-09-24|2024-08-24', '2024-08-01'::date,'2024-08-31'::date),
        ('2024-10-24|2024-09-24|2024-08-24', '2024-08-01'::date,'2024-08-31'::date)
)
select 
    t.order_dt_list, t.start_dt, t.end_dt,
    SUM(iff(s.value::text::date BETWEEN t.start_dt AND t.end_dt, 1, 0)) > 0 AS CHECK_DATE_RANGE
from tablename as t
  , lateral split_to_table(t.order_dt_list, '|') as s
group by 1,2,3,s.seq

answered Sep 23, 2024 at 23:03

Simeon Pilgrim

26.7k3 gold badges36 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Foink Over a year ago

Thank you Simeon. Yes I’m able to do it inline, the problem is that the start and end dates will be inputted by users in Cognos so I was hoping I could create a UDF to allow filtering in the WHERE clause in Cognos

Foink Over a year ago

Ok this logic works, but when replacing your SELECT * FROM VALUES (...) with the column name ORDER_DT_LIST from an outer query, it returns an 'Unsupported subquery type cannot be evaluated' error. Is there any way around this?

Simeon Pilgrim Over a year ago

Yes, change how you think about SQL, drop the "per row thinking" and change to "massive list of data A" and "massive list of data B" and transform them so that each is the most ready possible to merge via a equi join. Then you will have both performant code, and no errors.

Collectives™ on Stack Overflow

Snowflake UDF using column as parameter

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related