CODEWITHSUNDEEP
sqluser-defined-functionssnowflake-cloud-data-platform
Mirjamaria
Mirjamaria

Reputation: 1

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

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
  ';

Upvotes: 0

Views: 1491

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11086

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.

Upvotes: 0

Related Questions