Snowflake 中的动态 SQL

当我在雪花中运行动态 SQL 时，遇到以下错误：

未完成对“SQL_MAIN”的分配，因为值超出了变量的大小限制。它的大小是263；限制为 256（内部存储大小以字节为单位）。

这是代码：

SET v_G = '1';
SET v_G1 = $v_G::VARCHAR;
SET v_P = (SELECT "txtstr" FROM table2 WHERE "grouping" = $v_G);
SET SQL_MAIN = 'CREATE TABLE N_1 AS
                SELECT a1.YEARMONTH as "DATE",
                COUNT(a1.RECORD_NUM) AS "COUNT",
                ' || $v_G1 ||' AS "GROUP"
                FROM table1 a1
                WHERE ' || $v_P || ' GROUP BY YEARMONTH';

EXECUTE IMMEDIATE $SQL_MAIN;

set d_max_row = (select count(*) from table2");

begin
    let counter :=1;
        while (counter <= $d_max_row)
        do
            SET v_G = $v_G+1;
            SET v_G1 = $v_G::VARCHAR;
            SET v_P = (SELECT "txtstr" FROM table2 WHERE "grouping" = $v_G);
            SET SQL_MAIN = 'INSERT INTO N_1 (DATE, COUNT, GROUP)
                            SELECT a1.YEARMONTH as "DATE",
                            COUNT(a1.RECORD_NUM) AS "COUNT",
                            ' || $v_G1 ||' AS "GROUP"
                            FROM table1 a1
                            WHERE ' || $v_P || ' GROUP BY YEARMONTH';

            EXECUTE IMMEDIATE $SQL_MAIN;
           
            counter := counter + 1;
        end while;
    return counter;
end;

有没有办法将 table2 中“txtstr”列中的每条记录作为 table1 中的条件运行循环，并将结果返回到单独的表中？

我找到了很多 Javascript 的例子，但没有什么特别针对 SQL 的。请告诉我这在雪花中是否可行。

Thanks!

看看评论 - 让我构建一个 Lukasz 和 NickW 所说内容的完整示例。

让我开始用这个重现错误set:

set long_string = (select array_generate_range(1,100)::string);

-- Assignment to 'LONG_STRING' not done because value exceeds size limit for variables. Its size is 289; the limit is 256 (internal storage size in bytes).

由于您的目标是构造要执行的长 sql 字符串，因此这是构造长 sql 字符串并执行它们的最小示例：

declare 
    long_string default (select array_generate_range(1,100)::string);
    sql_to_execute string;
    rs resultset;
begin
    sql_to_execute := $$ select $$ || long_string;
    rs := (execute immediate sql_to_execute);
    return table(rs);
end;
-- [   1,   2,   3,   4,   5,   6,   7,   8,   9,   10,   11,   12,   13,   14,   15,   16,   17,   18,   19,   20,   21,   22,   23,   24,   25,   26,   27,   28,   29,   30,   31,   32,   33,   34,   35,   36,   37,   38,   39,   40,   41,   42,   43,   44,   45,   46,   47,   48,   49,   50,   51,   52,   53,   54,   55,   56,   57,   58,   59,   60,   61,   62,   63,   64,   65,   66,   67,   68,   69,   70,   71,   72,   73,   74,   75,   76,   77,   78,   79,   80,   81,   82,   83,   84,   85,   86,   87,   88,   89,   90,   91,   92,   93,   94,   95,   96,   97,   98,   99 ]

秘诀就是避免set，而不是在中声明变量declare block.