title: Snowflake SQL UDFs: SELECT TOP N, LIMIT, ROW_NUMBER() AND RANK() not working in subqueries?
body: <p>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. </p>
  
  <p>Here is an example of an approach I tried:</p>
  
  <pre><code>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 &lt; 0 AND P.PAIVA &lt; TO_DATE(STARTDATE) THEN 1
                   WHEN DAYS &lt; 0 AND P.PAIVA &gt;= TO_DATE(STARTDATE) THEN 0
                   WHEN DAYS &gt;= 0 AND P.PAIVA &gt; TO_DATE(STARTDATE) THEN 1
                   ELSE 0
              END),
  CTE2 AS (           
  SELECT
      PAIVA
      ,CASE WHEN DAYS &gt;= 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 &gt; 0 THEN RANK = DAYS
              WHEN DAYS = 0 THEN 1 = 1
              ELSE RANK = -DAYS
        END
    ';
  </code></pre>