英文:
Dynamic SQL in Snowflake
问题
当我在Snowflake中运行动态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示例。请告诉我在Snowflake中是否可能实现这个目标。
谢谢！
英文:
When I run a dynamic SQL in snowflake, I am running into the following error:
Assignment to 'SQL_MAIN' not done because value exceeds size limit for variables. Its size is 263; the limit is 256 (internal storage size in bytes).
Here is the code:
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 ASSELECT a1.YEARMONTH as "DATE",COUNT(a1.RECORD_NUM) AS "COUNT",' || $v_G1 ||' AS "GROUP"FROM table1 a1WHERE ' || $v_P || ' GROUP BY YEARMONTH';EXECUTE IMMEDIATE $SQL_MAIN;set d_max_row = (select count(*) from table2");beginlet counter :=1;while (counter <= $d_max_row)doSET 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 a1WHERE ' || $v_P || ' GROUP BY YEARMONTH';EXECUTE IMMEDIATE $SQL_MAIN;counter := counter + 1;end while;return counter;end;
Is there a way to run a loop through every record in "txtstr" column from table2 as a condition in table1 and return the results in a separate table?
I find lots of examples for Javascript but nothing too specific for SQL. Please let me know if this possible in Snowflake.
Thanks!
答案1
得分: 0
根据评论 - 让我构建一个完整的示例，演示Lukasz和NickW所说的内容。
让我从以下set开始重现错误：
set long_string = (select array_generate_range(1,100)::string);-- 未对'LONG_STRING'进行赋值，因为其值超出了变量的大小限制。其大小为289；限制为256（以字节为单位的内部存储大小）。
由于您的目标是构建要执行的长SQL字符串，这是一个构建长SQL字符串并执行它们的最小示例：
declarelong_string default (select array_generate_range(1,100)::string);sql_to_execute string;rs resultset;beginsql_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块中声明您的变量。
英文:
Looking at the comments - let me construct a full example of what Lukasz and NickW are saying.
Let me start reproducing the error with this 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).
As your goal is to construct long sql strings to be executed, this is a minimal example that constructs long sql strings and executes them:
declarelong_string default (select array_generate_range(1,100)::string);sql_to_execute string;rs resultset;beginsql_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 ]
The secret is to avoid set, and instead declaring your variables in the declare block.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论