Snowflake SQL UDFs: SELECT TOP N, LIMIT, ROW_NUMBER() AND RANK() not working in subqueries?

Question

I've been experimenting with Snowflake SQL UDF solutions to add a desired number of working days to a timestamp. I've been tryinig to define a function that takes a timestamp and desired number of working days to add as parameters and returns a date. The function uses date dimension table. The function works when I pass it a single date as a parameter, but whenever I try to give it a full column of dates, it throws error "Unsupported subquery type cannot be evaluated". It seems that this happens whenever I try to use SELECT TOP N, LIMIT, ROW_NUMBER() or RANK() in a subquery.

Here is an example of an approach I tried:

CREATE OR REPLACE FUNCTION "ADDWORKINGDAYSTOWORKINGDAY"(STARTDATE TIMESTAMP_NTZ, DAYS NUMBER)
RETURNS DATE
LANGUAGE SQL
AS '
    WITH CTE AS ( 
SELECT PAIVA 
    FROM EDW_DEV.REPORTING_SCHEMA."D_PAIVA"
    WHERE ARKIPAIVA = 1 AND ARKIPYHA_FI = FALSE 
    AND 1 = CASE WHEN DAYS < 0 AND P.PAIVA < TO_DATE(STARTDATE) THEN 1
                 WHEN DAYS < 0 AND P.PAIVA >= TO_DATE(STARTDATE) THEN 0
                 WHEN DAYS >= 0 AND P.PAIVA > TO_DATE(STARTDATE) THEN 1
                 ELSE 0
            END),
CTE2 AS (           
SELECT
    PAIVA
    ,CASE WHEN DAYS >= 0 THEN RANK() OVER
        (ORDER BY PAIVA)
        ELSE RANK() OVER 
        (ORDER BY PAIVA DESC)
    END AS RANK
FROM CTE
ORDER BY RANK)

SELECT TOP 1
ANY_VALUE (CASE WHEN DAYS IS NULL OR TO_DATE(STARTDATE) IS NULL THEN NULL
     WHEN DAYS = 0 THEN TO_DATE(STARTDATE)
     ELSE PAIVA
     END) AS PAIVA
FROM CTE2 
WHERE CASE  WHEN DAYS IS NULL OR TO_DATE(STARTDATE) IS NULL THEN 1 = 1
            WHEN DAYS > 0 THEN RANK = DAYS
            WHEN DAYS = 0 THEN 1 = 1
            ELSE RANK = -DAYS
      END
  ';

So, there's nothing wrong with the SQL, it only gives an error when you're using it with "a full column of dates"? How do you use it when you're giving it "a full column of dates"? — Scratte
Yes SQL is working fine. If I call for example SELECT ADDWORKINGDAYSTOWORKINGDAY(TO_DATE('2020-01-01'), 1) it is working as expected but SELECT ADDWORKINGDAYSTOWORKINGDAY(V.ECR_DELIVERYDEADLINE, 1) FROM EDW_DEW.PUBLIC.ECR_VEHICLE is throwing an error — Mirjamaria
Ok. That's a hardcoded value. I usually debug slowly. Next I would put a where clause on it and see if it can handle one row. — Scratte
Already did that. It couldn't handle one row directly selected from the table but when I assigned a value from one row select to variable, it could handle that. — Mirjamaria

Greg Pavlik Greg Pavlik · Accepted Answer · 2020-01-21T18:22:59

UDFs are scalar. They will return only one value of a specified type, in this case a date. If you want to return a set of values for a column, you may want to investigate UDTFs, User Defined Table Functions, which return a table.

https://docs.snowflake.net/manuals/sql-reference/udf-table-functions.html

With a bit of modification to your UDF, you can convert it to a UDTF. You can pass it columns instead of scalar values. You can then join the table resulting from the UDTF with the base table to get the work day addition values.

Snowflake SQL UDFs: SELECT TOP N, LIMIT, ROW_NUMBER() AND RANK() not working in subqueries?

1 Answers