cursor for loop & dynamic SQL - Snowflake-CodePudding

I'm attempting to write a procedure that takes in a list of tables and date_column to create some row_counts by calendar dates for reconciliation purposes.

SELECT t.*
FROM (
  VALUES ('tbl1', 'created_date')
       , ('tbl2', 'modify_date')
       , ('tbl3', 'last_seen_date')
  ) t(tbl, dt)

 ---- -------------- 
|TBL |DT            |
 ---- -------------- 
|tbl1|created_date  |
|tbl2|modify_date   |
|tbl3|last_seen_date|
 ---- --------------

I'm connected to Snowflake via a JDBC connection using Datagrip - so I assume I need to follow the classic SnowSQL part of the documentation:

EXECUTE IMMEDIATE $$

  DECLARE
    dt text
    , tbl text;
    c1 CURSOR FOR SELECT dt, tbl from t;
    BEGIN
      FOR record in c1 DO
        dt := record.dt
        tbl := record.tbl
        stmt =: 'SELECT COUNT(*)' ||
           CONCAT(', DAYOFMONTH(', $dt, ')') ||
           CONCAT('\n FROM ', $tbl) ||
           CONCAT('\n WHERE YEAR(', $dt, ')', ' = YEAR(CURRENT_DATE)') ||
           CONCAT('\n AND MONTH(', $dt, ')', ' = MONTH(CURRENT_DATE)') ||
           '\n GROUP BY' ||
           CONCAT('\n DAYOFMONTH(', $dt, ')')
      EXECUTE IMMEDIATE stmt -- will adapt this to be an update statement eventually. 
      END FOR
      end;
$$

This returns a SQL Compilation error, I've tried a few different variations of this but I'm none the wiser on how to proceed.

CodePudding user response：

There are lots of minor issues like missing semicolons etc. Here is the fixed script:

DECLARE
    dt text;
    tbl text;
    stmt text;
    c1 CURSOR FOR SELECT dt, tbl from t;
BEGIN
  FOR record in c1 DO
    dt := record.dt;
    tbl := record.tbl;
    stmt := 'SELECT COUNT(*)' ||
       CONCAT(', DAYOFMONTH(', dt, ')') ||
       CONCAT('\n FROM ', tbl) ||
       CONCAT('\n WHERE YEAR(', dt, ')', ' = YEAR(CURRENT_DATE)') ||
       CONCAT('\n AND MONTH(', dt, ')', ' = MONTH(CURRENT_DATE)') ||
       '\n GROUP BY' ||
       CONCAT('\n DAYOFMONTH(', dt, ')');
   -- EXECUTE IMMEDIATE :stmt; 
     RETURN stmt;
  END FOR;
END;

CodePudding user response：

Instead of concatenating the query string which makes it almost unreadable it could be rewritten using bind variables:

DECLARE
    dt text;
    tbl text;
    stmt text;
    c1 CURSOR FOR SELECT dt, tbl from t;
BEGIN
  FOR record in c1 DO
    dt := record.dt;
    tbl := record.tbl;
    
    stmt := 'INSERT INTO result(cnt, day_of_month)
             SELECT COUNT(*), DAYOFMONTH(IDENTIFIER(?)) AS day_of_month
             FROM TABLE(?)
             WHERE YEAR(IDENTIFIER(?)) = YEAR(CURRENT_DATE)
               AND MONTH(IDENTIFIER(?)) = MONTH(CURRENT_DATE)
             GROUP BY day_of_month';
             
     EXECUTE IMMEDIATE :stmt USING (dt, tbl, dt, dt); 
     
     RETURN stmt;
  END FOR;
END;

If column or table is parameter it should be wrapped with IDENTIFIER/TABLE funtion.

For sample data:

CREATE OR REPLACE TABLE t AS
SELECT  'col1' AS dt, 'tab1' AS tbl UNION ALL 
SELECT  'col2' AS dt, 'tab1' ;

CREATE TABLE tab1(col1 DATE, col2 DATE) AS
SELECT CURRENT_DATE(), CURRENT_DATE()-40;

CREATE TABLE result(cnt INT, day_of_month INT);

SELECT * FROM result;