Возможно ли в Snowflake dwh получить результат SQL-запроса и передать его в качестве переменной следующему запросу в рамках той же процедуры?
Я пытаюсь попробовать% ROWCOUNT, CURSOR case, но застрял в следующем:
CREATE OR REPLACE PROCEDURE PASS_VAR_IN_JS() RETURNS VARCHAR LANGUAGE javascript AS $$ var command = "SELECT * FROM TABLE WHERE ATTRIBUTE = 'Value'"; var stmt = snowflake.createStatement( {sqlText: command} ); var result1 = stmt.execute(); return result1.getColumnName(3); //this returns column name var col_name = result1.getColumnName(3); //save column name to variable //pass the column name to the query var stmt = snowflake.createStatement({sqlText: "SELECT * FROM INFORMATION_SCHEMA WHERE COLUMN_NAME = ;", binds:[col_name]}); $$;
UPDATE - working code that passes one result to another query
<pre>
CREATE OR REPLACE PROCEDURE szpotma_rowcount()
RETURNS VARCHAR(250)
LANGUAGE JAVASCRIPT
as
$$
var command = `SELECT ATTR1 FROM TABLE1 LIMIT 1`;
var stmt = snowflake.createStatement( {sqlText: command} );
var rs = stmt.execute(), col_name ;
if (rs.next()) {
col_name = rs.getColumnValue(1);
stmt = snowflake.createStatement({
sqlText: `SELECT STATUS FROM TABLE_STATUS WHERE ATTR1 = :1`, binds: [col_name]});
//view second query results
rs = stmt.execute();
rs.next()
return rs.getColumnValue(1);
}
$$