英文:
Snowflake stored procedure error , I want to create a procedure that calls an existing procedure and then with it's results join to other tables etc
问题
以下是已翻译的代码部分：
create or replace procedure sp_sp1_2
(TABLENAME varchar(60), ID varchar(60), DATE varchar(60), VARIABLE varchar(60))
RETURNS TABLE ()
LANGUAGE SQL
AS
DECLARE
res resultset DEFAULT
(
CALL sp_sp1 (:TABLENAME, :ID, :DATE, :VARIABLE)
CREATE OR REPLACE temp TABLE a1 AS
SELECT * FROM table(RESULT_SCAN(LAST_QUERY_ID()))
SELECT * FROM a1 JOIN a2 ON a1.id = a2.id
);
BEGIN
RETURN table(res);
END;
This stored procedure returns without error outside the stored procedure:
CALL sp_sp1 (:TABLENAME, :ID, :DATE, :VARIABLE);
CREATE OR REPLACE temp TABLE a1 AS
SELECT * FROM table(RESULT_SCAN(LAST_QUERY_ID()));
SELECT * FROM a1 JOIN a2 ON a1.id = a2.id;
If this statement by itself is in the stored procedure it returns without error:
CALL sp_sp1 (:TABLENAME, :ID, :DATE, :VARIABLE)
but when I also with to add these other statements, the stored procedure throws an error.
英文:
create or replace procedure sp_sp1_2
(TABLENAME varchar(60), ID varchar(60), DATE varchar(60), VARIABLE varchar(60))
RETURNS TABLE ()
LANGUAGE SQL
AS
DECLARE
res resultset DEFAULT
(
CALL sp_sp1 (:TABLENAME, :ID, :DATE, :VARIABLE)
CREATE OR REPLACE temp TABLE a1 AS
SELECT * FROM table(RESULT_SCAN(LAST_QUERY_ID()))
SELECT * FROM a1 JOIN a2 ON a1.id = a2.id
);
BEGIN
RETURN table(res);
END;
This stored procedure returns without error outside the stored procedure:
CALL sp_sp1 (:TABLENAME, :ID, :DATE, :VARIABLE);
CREATE OR REPLACE temp TABLE a1 AS
SELECT * FROM table(RESULT_SCAN(LAST_QUERY_ID()));
SELECT * FROM a1 JOIN a2 ON a1.id = a2.id;
If this statement by itself is in the stored procedure it returns without error:
CALL sp_sp1 (:TABLENAME, :ID, :DATE, :VARIABLE)
but when I also with to add these other statements, the stored procedure throws an error.
Coming from T-SQL so this snowflake SQL stored procedure logic is still puzzling me.
答案1
得分: 0
好的，以下是翻译好的部分：
Okey lets create all the things it seems are missing from the question, in simple example forms:
create or replace table a1 (id number, val text);
create or replace table a2 (id number);
insert into a2 values
(1),(2),(3);
create or replace procedure sp_sp1(TABLENAME varchar(60), ID varchar(60), DATE varchar(60), VARIABLE varchar(60))
returns TABLE(id number, val text)
LANGUAGE SQL AS
DECLARE
res RESULTSET DEFAULT (SELECT seq8() as id, :TABLENAME as val FROM TABLE(GENERATOR(rowcount=>10)));
BEGIN
return TABLE(res);
END;
CALL sp_sp1('aaa', 'bb', 'ccc', 'ddd');
| ID | VAL |
|---|---|
| 0 | aaa |
| 1 | aaa |
| 2 | aaa |
| 3 | aaa |
| 4 | aaa |
| 5 | aaa |
| 6 | aaa |
| 7 | aaa |
| 8 | aaa |
| 9 | aaa |
righto, so we now, can run the SQL that works by itself, I dropped the TEMP of the table, just to make it all simpler, will try add back in later:
CREATE OR REPLACE TABLE a1 AS
SELECT * FROM table(RESULT_SCAN(LAST_QUERY_ID()));
all good
SELECT * FROM a1 JOIN a2 ON a1.id = a2.id;
| ID | VAL | ID_2 |
|---|---|---|
| 1 | aaa | 1 |
| 2 | aaa | 2 |
| 3 | aaa | 3 |
Right, we are ready to start...
So you have three statements, inside your "result set" and a resultset is just one set of results, so I am going to assume you want the final results, so will move some tihngs around:
create or replace procedure sp_sp1_2
(TABLENAME varchar(60), ID varchar(60), DATE varchar(60), VARIABLE varchar(60))
RETURNS TABLE ()
LANGUAGE SQL
AS
DECLARE
res resultset;
BEGIN
CALL sp_sp1 (:TABLENAME, :ID, :DATE, :VARIABLE);
CREATE OR REPLACE TABLE a1 AS
SELECT * FROM table(RESULT_SCAN(LAST_QUERY_ID()));
res := (SELECT * FROM a1 JOIN a2 ON a1.id = a2.id);
RETURN table(res);
END;
CALL sp_sp1_2('xxx', 'bb', 'ccc', 'ddd');
and this gives:
| ID | VAL | ID_2 |
|---|---|---|
| 1 | xxx | 1 |
| 2 | xxx | 2 |
| 3 | xxx | 3 |
<details>
<summary>英文:</summary>
Okey lets create all the things it seems are missing from the question, in simple example forms:
create or replace table a1 (id number, val text);
create or replace table a2 (id number);
insert into a2 values
(1),(2),(3);
create or replace procedure sp_sp1(TABLENAME varchar(60), ID varchar(60), DATE varchar(60), VARIABLE varchar(60))
returns TABLE(id number, val text)
LANGUAGE SQL AS
DECLARE
res RESULTSET DEFAULT (SELECT seq8() as id, :TABLENAME as val FROM TABLE(GENERATOR(rowcount=>10)));
BEGIN
return TABLE(res);
END;
CALL sp_sp1('aaa', 'bb', 'ccc', 'ddd');
ID |VAL
--|--
0 |aaa
1 |aaa
2 |aaa
3 |aaa
4 |aaa
5 |aaa
6 |aaa
7 |aaa
8 |aaa
9 |aaa
righto, so we now, can run the SQL that works by itself, I dropped the TEMP of the table, just to make it all simpler, will try add back in later:
CREATE OR REPLACE TABLE a1 AS
SELECT * FROM table(RESULT_SCAN(LAST_QUERY_ID()));
all good
SELECT * FROM a1 JOIN a2 ON a1.id = a2.id;
ID |VAL |ID_2
--|--|--
1 |aaa |1
2 |aaa |2
3 |aaa |3
Right, we are ready to start...
So you have three statements, inside your "result set" and a resultset is just one set of results, so I am going to assume you want the final results, so will move some tihngs around:
create or replace procedure sp_sp1_2
(TABLENAME varchar(60), ID varchar(60), DATE varchar(60), VARIABLE varchar(60))
RETURNS TABLE ()
LANGUAGE SQL
AS
DECLARE
res resultset;
BEGIN
CALL sp_sp1 (:TABLENAME, :ID, :DATE, :VARIABLE);
CREATE OR REPLACE TABLE a1 AS
SELECT * FROM table(RESULT_SCAN(LAST_QUERY_ID()));
res := (SELECT * FROM a1 JOIN a2 ON a1.id = a2.id);
RETURN table(res);
END;
CALL sp_sp1_2('xxx', 'bb', 'ccc', 'ddd');
and this gives:
ID |VAL |ID_2
--|--|--
1 |xxx |1
2 |xxx |2
3 |xxx |3
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论