CODEWITHSUNDEEP
sqlsql-serversnowflake-cloud-data-platform
David Borštner
David Borštner

Reputation: 89

How to call a procedure/function in a snowflake SQL query dynamically?

Theres very litte on this topic so far on StackOverflow and nothing has helped me with this issue. I've been trying to call a function inside a simple snowflake select query, pasted bellow. The error I keep getting is: "Unsupported subquery type cannot be evaluated". I've also tried using stored procedures but I can't seem to figure it out.

SELECT  CUST.CUSTID , GET_ORD_COUNT(CUST.CUSTID , 0,10)
FROM SALES.CUSTOMERS AS CUST

The function I am trying to use:

CREATE OR REPLACE FUNCTION GET_ORD_COUNT(CUSTID INT, COUNT_FROM INT, COUNT_TO INT)
RETURNS INTEGER
LANGUAGE SQL
AS $$
SELECT TOP 1 COUNT(*)
from(
SELECT  ORD1.ORDERID , ORD1.CUSTID, ORD1.ORDERDATE, MIN(ORD2.ORDERDATE) AS ORDERDATE2, DATEDIFF("D", ORD1.ORDERDATE , MIN(ORD2.ORDERDATE)) AS DaysDiff
FROM    SALES.ORDERS ORD1
LEFT JOIN SALES.ORDERS ORD2 ON ORD1.CUSTID = ORD2.CUSTID AND ORD2.ORDERDATE > ORD1.ORDERDATE
GROUP BY ORD1.ORDERID, ORD1.CUSTID , ORD1.ORDERDATE
HAVING DAYSDIFF IS NOT NULL
ORDER BY ORD1.CUSTID, ORD1.ORDERDATE 
) AS SALES_COUNT 
WHERE SALES_COUNT.DAYSDIFF <= COUNT_TO AND SALES_COUNT.DAYSDIFF >= COUNT_FROM AND SALES_COUNT.CUSTID = CUSTID
GROUP BY SALES_COUNT.CUSTID
$$;

The function works fine with hard coded values but that has no use for me.

Upvotes: 1

Views: 2079

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

So change from a procedure to a CTE, we move the inside of the function which is variable independent into a CTE sales_count then we have a second CTE cust_data purely for demonstrative purposes, as if these values are not changing the should be moved into the "function" which implies they are open to change, thus allowing them to be changed, when this join the two datasets using the logic that was present in the outer later of the function.

WITH sales_count AS (
    SELECT  ord1.orderid 
        ,ord1.custid
        ,ord1.orderdate
        ,MIN(ord2.orderdate) AS orderdate2
        ,DATEDIFF("D", ord1.orderdate , MIN(ord2.orderdate)) AS daysdiff
    FROM sales.orders AS ord1
    LEFT JOIN sales.orders AS ord2 
        ON ord1.custid = ord2.custid AND ord2.orderdate > ord1.orderdate
    GROUP BY ord1.orderid, ord1.custid, ord1.orderdate
    HAVING daysdiff IS NOT NULL
), cust_data AS (
    SELECT cust.custid
        ,0 AS count_from
        ,10 AS count_to
    FROM sales.customers AS cust
)
SELECT c.custid
    ,c.count_from
    ,c.count_to
    ,count(*) AS count
FROM cust_data AS c
JOIN sales_count AS s 
    ON s.custid = c.custid AND s.daysdiff <= c.count_to AND s.daysdiff >= c.count_from 
GROUP BY 1,2,3;

The CTE sales_count can have ord1.orderid removed from the select as it's not used in the join and because it's explicitly named in the order by it provides value (in this present question), thus that could be:

WITH sales_count AS (
    SELECT  ord1.custid
        ,ord1.orderdate
        ,MIN(ord2.orderdate) AS orderdate2
        ,DATEDIFF("D", ord1.orderdate , MIN(ord2.orderdate)) AS daysdiff
    FROM sales.orders AS ord1
    LEFT JOIN sales.orders AS ord2 
        ON ord1.custid = ord2.custid AND ord2.orderdate > ord1.orderdate
    GROUP BY ord1.orderid, ord1.custid, ord1.orderdate
    HAVING daysdiff IS NOT NULL
)

Upvotes: 2

Related Questions