MULTI-STATEMENT TABLE-VALUED
Translation reference to convert SQL Server UDF (User Defined Functions) with TABLE return type to Snowflake.
Last updated
Translation reference to convert SQL Server UDF (User Defined Functions) with TABLE return type to Snowflake.
Last updated
Multi-statement table-valued is similar to Inline-statement table-valued (INLINE TABLE-VALUED). However Multi-statement table-valued may have more than one statement in its function body, the table columns are specified in the return type and it has a BEGIN/END block (SQL Server Language Reference Creating a multi-statement table-valued function
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] [READONLY] }
[ ,...n ]
]
)
RETURNS @return_variable TABLE <table_type_definition>
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END
[ ; ]CREATE OR REPLACE FUNCTION <name> ( [ <arguments> ] )
RETURNS TABLE ( <output_col_name> <output_col_type> [, <output_col_name> <output_col_type> ... ] )
AS '<sql_expression>'The following section describes all the possible source code patterns that can appear in this kind of CREATE FUNCTION syntax.
The function body of Multi-Statement Table-Valued function must be a SELECT statement. For this reason the others statements must be called separately.
Inserts one or more rows into the table and returns the table with the new values
CREATE OR ALTER FUNCTION calc_behavioral_segment()
RETURNS @behavioral_segments TABLE (behavioral_segment VARCHAR(50))
AS
BEGIN
DECLARE @col varchar(15)
SET @col = 'Unknown'
INSERT INTO @behavioral_segments
SELECT @col
RETURN
END
SELECT * FROM calc_behavioral_segment();CREATE OR REPLACE FUNCTION calc_behavioral_segment()
RETURNS TABLE(behavioral_segment VARCHAR(50))
AS
$$
WITH CTE AS (SELECT 'Unknown' as behavior)
SELECT behavior FROM CTE
$$;
SELECT * FROM TABLE(calc_behavioral_segment());Inserts a row into the table according to the condition and returns the table with the new value
CREATE OR ALTER FUNCTION odd_or_even_number(@number INT)
RETURNS @numbers TABLE (number_type VARCHAR(15))
AS
BEGIN
IF ((@number % 2) = 0)
BEGIN
INSERT @numbers SELECT 'Even'
END
ELSE
BEGIN
INSERT @numbers SELECT 'Odd'
END
RETURN
END
SELECT * FROM odd_or_even_number(9);CREATE OR REPLACE FUNCTION odd_or_even_number(number int)
RETURNS TABLE(number_type VARCHAR(50))
AS
$$
WITH CTE2 as (SELECT CASE WHEN (number % 2) = 0 THEN 'EVEN'
ElSE 'ODD'
END AS result)
SELECT result FROM CTE2
$$;
select * from table(odd_or_even_number(9));The example below inserts more than one value into the table and more than one variable is modified according to the condition. Returns the table with the new values
CREATE OR ALTER FUNCTION new_employee_hired(@id VARCHAR (50), @position VARCHAR(50), @experience VARCHAR(15))
RETURNS @new_employee TABLE (id_employee VARCHAR (50), working_from_home BIT, team VARCHAR(15), computer VARCHAR(15))
AS
BEGIN
DECLARE @wfh BIT
DECLARE @team VARCHAR(15)
DECLARE @computer VARCHAR(15)
IF @position = 'DEVELOPER'
BEGIN
SET @team = 'TEAM_1'
SET @computer = 'LAPTOP'
END
IF @position = 'IT'
BEGIN
SET @team = 'TEAM_2'
SET @computer = 'DESKTOP'
END
IF @experience = 'JUNIOR'
BEGIN
SET @wfh = '0'
END
IF @experience = 'SENIOR'
BEGIN
SET @wfh = '1'
END
INSERT INTO @new_employee VALUES (@id, @wfh, @team, @computer)
RETURN
END
SELECT * FROM new_employee_hired('123456789', 'DEVELOPER', 'SENIOR');CREATE OR REPLACE FUNCTION new_employee_hired(id VARCHAR (50), position VARCHAR(50), experience VARCHAR(15))
RETURNS TABLE (id_employee VARCHAR (50), working_from_home BOOLEAN, team VARCHAR(15), computer VARCHAR(15))
AS
$$
WITH CTE1 AS (SELECT CASE
WHEN POSITION = 'DEVELOPER' THEN 'TEAM_1'
WHEN POSITION = 'IT' THEN 'TEAM_2'
END),
CTE2 AS (SELECT CASE
WHEN POSITION = 'DEVELOPER' THEN 'LAPTOP'
WHEN POSITION = 'IT' THEN 'DESKTOP'
END),
CTE3 AS (SELECT CASE
WHEN EXPERIENCE = 'JUNIOR' THEN FALSE
WHEN EXPERIENCE = 'SENIOR' THEN TRUE
END)
SELECT ID, (SELECT * FROM CTE3), (SELECT * FROM CTE1), (SELECT * FROM CTE2)
$$;
SELECT * FROM TABLE(new_employee_hired('123456789', 'DEVELOPER', 'SENIOR'));In case there are nested if statements and more than one variables are modified in the statements it is necessary to use a stored procedure.
Updates columns values of the table into the function body and returns it with the new values.
CREATE OR ALTER FUNCTION get_employees_history()
RETURNS @employee_history TABLE (
department_name NVARCHAR(50),
first_name NVARCHAR(50),
last_name NVARCHAR(50),
start_date DATE,
end_date DATE,
job_title NVARCHAR(50),
months_working INT
)
BEGIN
INSERT INTO @employee_history
SELECT D.name AS department_name, P.first_name, P.last_name, EH.start_date, EH.end_date, E.job_title, 0 FROM Department D
LEFT OUTER JOIN employee_department_history EH
ON D.department_ID = EH.department_ID
INNER JOIN Employee E
ON E.business_entity_ID = EH.business_entity_ID
INNER JOIN Person P
ON P.business_entity_ID = E.business_entity_ID
UPDATE @employee_history
SET
months_working =
CASE WHEN end_date IS NULL THEN DATEDIFF(MONTH, start_date, GETDATE())
ELSE DATEDIFF(MONTH, start_date, end_date)
END
RETURN;
END;
SELECT TOP(10) * FROM get_employees_history();CREATE OR REPLACE FUNCTION get_employees_history()
RETURNS TABLE(
department_name VARCHAR(50),
first_name VARCHAR(50),
last_name VARCHAR(50),
start_date DATE,
end_date DATE,
job_title VARCHAR(50),
months_working INT)
AS
$$
WITH CTE AS (
SELECT D.name AS department_name, P.first_name, P.last_name, EH.start_date, EH.end_date, E.job_title,
CASE WHEN end_date IS NULL THEN DATEDIFF(MONTH, start_date, GETDATE())
ELSE DATEDIFF(MONTH, start_date, end_date) END as months_working
FROM Department D
LEFT OUTER JOIN employee_department_history EH
ON D.department_ID = EH.department_ID
INNER JOIN Employee E
ON E.business_entity_ID = EH.business_entity_ID
INNER JOIN Person P
ON P.business_entity_ID = E.business_entity_ID
)
SELECT * FROM CTE
$$;
SELECT TOP 10 * FROM TABLE(get_employees_history());In the following sample there is more than one return clause, this is because depending on the situation it is not necessary to keep executing the whole function.
CREATE OR ALTER FUNCTION create_new_team(@team_name VARCHAR(50))
RETURNS @new_team TABLE (type VARCHAR(50), name VARCHAR(50))
AS
BEGIN
DECLARE @employees INT
SET @employees = (SELECT count(*) FROM employee)
DECLARE @type VARCHAR(15)
SET @type = 'small_team'
IF (@employees < 8)
BEGIN
INSERT @new_team VALUES (@type, @team_name)
RETURN
END
SET @type = 'big_team'
INSERT @new_team VALUES (@type, @team_name)
RETURN
END
SELECT * FROM create_new_team('Team1'); CREATE OR REPLACE FUNCTION create_new_team(team_name VARCHAR(50))
RETURNS TABLE(type VARCHAR(50), name VARCHAR(50))
AS
$$
WITH CTE1 AS (SELECT COUNT(*) AS employees FROM EMPLOYEE),
CTE2 AS (SELECT CASE WHEN(SELECT employees FROM cte1) < 8 THEN 'small_team' ELSE'big_team' END AS type)
SELECT (SELECT type FROM CTE2), TEAM_NAME
$$;
SELECT * FROM TABLE(create_new_team('Team1'));This transformation is applied when there is only one value to insert, if there is more than one value it is necessary to use a stored procedure.
The example is a complex case that uses nested if statements and inserts a value depending on the true condition.
CREATE OR ALTER FUNCTION vacation_status(@id VARCHAR (50))
RETURNS @status TABLE (vacation_status VARCHAR(30))
AS
BEGIN
DECLARE @hire_date DATETIME
SET @hire_date = (SELECT @hire_date FROM employee WHERE employeeId = @id)
DECLARE @vacation_hours INT
SET @vacation_hours = (SELECT count(vacation_hours) FROM employee WHERE employeeId = @id)
DECLARE @time_working INT
SET @time_working = (SELECT DATEDIFF(MONTH, @hire_date,GETDATE()))
IF (@vacation_hours > 0)
BEGIN
IF (@time_working > 3)
BEGIN
IF (@vacation_hours < 120)
BEGIN
INSERT INTO @status VALUES ('Ok')
END
IF (@vacation_hours = 120)
BEGIN
INSERT INTO @status values ('In the limit')
END
IF (@vacation_hours > 120)
BEGIN
INSERT INTO @status VALUES ('With excess')
END
END
ELSE
BEGIN
INSERT INTO @status values ('Hired recently')
END
END
ELSE
BEGIN
INSERT INTO @status values ('No hours')
END
RETURN
END
SELECT * FROM vacation_status('adventure-worksken0')CREATE OR REPLACE FUNCTION vacation_status (ID VARCHAR(50))
RETURNS TABLE (vacation_status varchar(30))
AS
$$
WITH CTE1 AS (SELECT hiredate AS hire_date FROM EMPLOYEE WHERE employeeId = ID),
CTE2 AS (SELECT COUNT(vacationhours) AS vacation_hours FROM EMPLOYEE WHERE loginid = ID),
CTE3 AS (SELECT DATEDIFF(MONTH, (SELECT * from CTE1), GETDATE()) AS time_working),
CTE4 AS (SELECT CASE
WHEN (SELECT vacation_hours FROM CTE2) > 0 THEN (
SELECT CASE
WHEN (SELECT time_working FROM CTE3) > 3 THEN (
SELECT CASE
WHEN (SELECT vacation_hours FROM CTE2) < 120 THEN 'Ok'
WHEN (SELECT vacation_hours FROM CTE2) = 120 THEN 'On the limit'
WHEN (SELECT vacation_hours FROM CTE2) > 120 THEN 'With excess'
END
)
ELSE 'Hired recently'
END
)
ELSE 'No hours'
END AS result)
SELECT result FROM CTE4
$$;
SELECT * FROM TABLE(vacation_status('adventure-worksken0'));The problem with this example is that there's no way of transforming the while statement to a CTE inside the WITH clause of the main select, this forces us to transform this statement to store procedure to maintain the same logic.
CREATE OR ALTER FUNCTION get_group_name
(@department_id INT)
RETURNS @group_names TABLE (group_name VARCHAR(15))
AS
BEGIN
DECLARE @name VARCHAR(30) = 'Another Department'
WHILE @name = 'Another Department'
BEGIN
IF (@department_id < 3)
BEGIN
SET @name = 'engineering'
END
IF @department_id = 3
BEGIN
SET @name = 'Tool Design'
END
SELECT @department_id = @department_id / 3
END
INSERT @group_names SELECT @name
RETURN
END
SELECT * FROM get_group_name(9);CREATE OR REPLACE PROCEDURE number_type(department_id FLOAT)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
var fetch = (count,rows,stmt) => (count && rows.next() && Array.apply(null,Array(stmt.getColumnCount())).map((_,i) => rows.getColumnValue(i + 1))) || [];
var SELECT = (sql,binds = [],...args) => {
var reducers = args.filter((i) => i instanceof Function);
reducers = reducers.length ? reducers : [(value) => value]
args = args.splice(0,args.length - reducers.length)
EXEC("SELECT " + sql,binds)
if (ROW_COUNT < 1) return;
var colCount = _ROWS.getColumnCount();
if (colCount != reducers.length) throw new Error("Missing arguments results has ${colCount} columns");
var cols = Array.from(Array(colCount),() => []);
while ( _ROWS.next() ) {
for(var i = 0;i < colCount;i++) {
cols[i].push(_ROWS.getColumnValue(i + 1))
}
}
if (colCount == 1) {
cols[0].forEach((value) => reducers[0](value))
return (cols[0])[0];
}
for(var i = 0;i < colCount;i++) {
cols[i].forEach((value) => reducers[i](value))
}
};
var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', OBJECT_SCHEMA_NAME = 'UNKNOWN', ERROR_HANDLERS, NUM_ROWS_AFFECTED, PROC_NAME = arguments.callee.name, DOLLAR_DOLLAR = '$' + '$';
function* sqlsplit(sql) {
var part = '';
var ismark = () => sql[i] == '$' && sql[i + 1] == '$';
for(var i = 0;i < sql.length;i++) {
if (sql[i] == ';') {
yield part + sql[i];
part = '';
} else if (ismark()) {
part += sql[i++] + sql[i++];
while ( i < sql.length && !ismark() ) {
part += sql[i++];
}
part += sql[i] + sql[i++];
} else part += sql[i];
}
if (part.trim().length) yield part;
};
var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
var fixBind = function (arg) {
arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;
return arg;
};
var EXEC = (stmt,binds = [],severity = "16",noCatch = false) => {
binds = binds ? binds.map(fixBind) : binds;
for(var stmt of sqlsplit(stmt)) {
try {
_RS = snowflake.createStatement({
sqlText : stmt,
binds : binds
});
_ROWS = _RS.execute();
ROW_COUNT = _RS.getRowCount();
NUM_ROWS_AFFECTED = _RS.getNumRowsAffected();
return {
THEN : (action) => !SQLCODE && action(fetch(_ROWS))
};
} catch(error) {
let rStack = new RegExp('At .*, line (\\d+) position (\\d+)');
let stackLine = error.stackTraceTxt.match(rStack) || [0,-1];
MESSAGE_TEXT = error.message.toString();
SQLCODE = error.code.toString();
SQLSTATE = error.state.toString();
snowflake.execute({
sqlText : `SELECT UPDATE_ERROR_VARS_UDF(?,?,?,?,?,?)`,
binds : [stackLine[1],SQLCODE,SQLSTATE,MESSAGE_TEXT,PROC_NAME,severity]
});
throw error;
}
}
};
// END REGION
EXEC(`CREATE OR REPLACE TEMPORARY TABLE T_group_names(group_name VARCHAR(30));`);
let NAME = `Another Department`;
while ( NAME == `Another Department` ) {
if (DEPARTMENT_ID < 3) {
{
NAME = `engineering`;
}
}
if (DEPARTMENT_ID == 3) {
{
NAME = `Tool Design`;
}
}
// ** MSC-INFORMATION - MSCINF0016 - SELECTION OF @department_id MAY HAVE RUNTIME ERRORS **
SELECT(`? / 3`,[DEPARTMENT_ID],(value) => DEPARTMENT_ID = value);
}
EXEC(`INSERT INTO T_group_names SELECT ?`,[NAME]);
EXEC(`SELECT * from T_group_names`);
return NAME;
$$;
CALL number_type(9);
SELECT * FROM T_group_names;User-defined functions cannot DECLARE, OPEN, FETCH, CLOSE or DEALLOCATE a CURSOR. Use a Stored Procedure to work with cursors.
CREATE OR ALTER FUNCTION amount_new_specimens(@id int)
RETURNS @new_specimens TABLE (amount int)
AS
BEGIN
DECLARE @first_specimen VARCHAR(30) ;
set @first_specimen = (select name_specimen from specimen where specimen_id = @id);
DECLARE @second_specimen VARCHAR(30);
DECLARE @specimens TABLE (name_specimen VARCHAR(30))
DECLARE Cursor1 CURSOR
FOR SELECT name_specimen
FROM specimen
OPEN cursor1
FETCH NEXT FROM cursor1
INTO @second_specimen;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @first_specimen <> @second_specimen
BEGIN
INSERT INTO @specimens values (CONCAT_WS('-', @first_specimen, @second_specimen))
END
FETCH NEXT FROM cursor1
INTO @second_specimen;
END
CLOSE cursor1;
DEALLOCATE cursor1;
INSERT INTO @new_specimens SELECT COUNT(*) FROM @specimens
RETURN
END
SELECT * FROM amount_new_specimens(1);CREATE OR REPLACE PROCEDURE amount_new_specimens(ID float)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
var fetch = (count,rows,stmt) => (count && rows.next() && Array.apply(null,Array(stmt.getColumnCount())).map((_,i) => rows.getColumnValue(i + 1))) || [];
var SELECT = (sql,binds = [],...args) => {
var reducers = args.filter((i) => i instanceof Function);
reducers = reducers.length ? reducers : [(value) => value]
args = args.splice(0,args.length - reducers.length)
EXEC("SELECT " + sql,binds)
if (ROW_COUNT < 1) return;
var colCount = _ROWS.getColumnCount();
if (colCount != reducers.length) throw new Error("Missing arguments results has ${colCount} columns");
var cols = Array.from(Array(colCount),() => []);
while ( _ROWS.next() ) {
for(var i = 0;i < colCount;i++) {
cols[i].push(_ROWS.getColumnValue(i + 1))
}
}
if (colCount == 1) {
cols[0].forEach((value) => reducers[0](value))
return (cols[0])[0];
}
for(var i = 0;i < colCount;i++) {
cols[i].forEach((value) => reducers[i](value))
}
};
var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', OBJECT_SCHEMA_NAME = 'UNKNOWN', ERROR_HANDLERS, NUM_ROWS_AFFECTED, PROC_NAME = arguments.callee.name, DOLLAR_DOLLAR = '$' + '$';
function* sqlsplit(sql) {
var part = '';
var ismark = () => sql[i] == '$' && sql[i + 1] == '$';
for(var i = 0;i < sql.length;i++) {
if (sql[i] == ';') {
yield part + sql[i];
part = '';
} else if (ismark()) {
part += sql[i++] + sql[i++];
while ( i < sql.length && !ismark() ) {
part += sql[i++];
}
part += sql[i] + sql[i++];
} else part += sql[i];
}
if (part.trim().length) yield part;
};
var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
var fixBind = function (arg) {
arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;
return arg;
};
var EXEC = (stmt,binds = [],severity = "16",noCatch = false) => {
binds = binds ? binds.map(fixBind) : binds;
for(var stmt of sqlsplit(stmt)) {
try {
_RS = snowflake.createStatement({
sqlText : stmt,
binds : binds
});
_ROWS = _RS.execute();
ROW_COUNT = _RS.getRowCount();
NUM_ROWS_AFFECTED = _RS.getNumRowsAffected();
return {
THEN : (action) => !SQLCODE && action(fetch(_ROWS))
};
} catch(error) {
let rStack = new RegExp('At .*, line (\\d+) position (\\d+)');
let stackLine = error.stackTraceTxt.match(rStack) || [0,-1];
MESSAGE_TEXT = error.message.toString();
SQLCODE = error.code.toString();
SQLSTATE = error.state.toString();
snowflake.execute({
sqlText : `SELECT UPDATE_ERROR_VARS_UDF(?,?,?,?,?,?)`,
binds : [stackLine[1],SQLCODE,SQLSTATE,MESSAGE_TEXT,PROC_NAME,severity]
});
throw error;
}
}
};
var CURSOR = function (stmt,binds) {
var statementObj, result_set, total_rows, isOpen = false, result_set_table = '', self = this;
this.CURRENT = new Object;
this.OPEN = function (usingParams) {
try {
if (usingParams) binds = usingParams;
if (binds instanceof Function) binds = binds();
var finalBinds = binds && binds.map(fixBind);
var finalStmt = stmt instanceof Function ? stmt() : stmt;
statementObj = snowflake.createStatement({
sqlText : finalStmt,
binds : finalBinds
});
result_set = statementObj.execute();
total_rows = statementObj.getRowCount();
isOpen = true;
row_count = 0;
} catch(error) {
RAISE(error.code,"error",error.message);
}
return this;
};
this.CURSOR_ROWS = function () {
return total_rows;
};
this.FETCH_STATUS = function () {
return total_rows >= row_count;
};
this.FETCH_NEXT = function () {
self.res = [];
self.res = fetch(total_rows,result_set,statementObj);
if (self.res) row_count++
return self.res && self.res.length > 0;
};
this.INTO = function () {
return self.res;
};
this.CLOSE = function () {
isOpen = row_count = result_set_table = total_rows = result_set = statementObj = undefined;
};
this.DEALLOCATE = function () {
this.CURRENT = self = undefined;
};
};
// END REGION
EXEC(`CREATE OR REPLACE TEMPORARY TABLE T_new_specimens(amount int);`);
let FIRST_SPECIMEN;
FIRST_SPECIMEN = SELECT(`name_specimen from specimen where specimen_id = ?`,[ID]);
let SECOND_SPECIMEN;
EXEC(`CREATE OR REPLACE TEMPORARY TABLE T_specimens (name_specimen VARCHAR(30))`);
var CURSOR1 = new CURSOR(`SELECT name_specimen FROM specimen`,[],false);
CURSOR1.OPEN();
CURSOR1.FETCH_NEXT() && ([SECOND_SPECIMEN] = CURSOR1.INTO());
while ( CURSOR1.FETCH_STATUS() ) {
if (FIRST_SPECIMEN != SECOND_SPECIMEN) {
{
EXEC(`INSERT INTO T_specimens values (CONCAT_WS('-', ?, ?))`,[FIRST_SPECIMEN,SECOND_SPECIMEN]);
}
}
CURSOR1.FETCH_NEXT() && ([SECOND_SPECIMEN] = CURSOR1.INTO());
}
CURSOR1.CLOSE();
CURSOR1.DEALLOCATE();
EXEC(`INSERT INTO T_new_specimens SELECT COUNT(*) FROM T_specimens`);
return;
$$;
CALL amount_new_specimens(1);
SELECT * FROM T_new_specimens;The clauses UPDATE, INSERT, DELETE, ALTER or DROP are not supported on the body of common tables expressions, even after their declaration using a delimitator. For this reason, the function can be modified to work as a stored procedure.
CREATE OR ALTER PROCEDURE product_history
AS
BEGIN
DECLARE @product_history TABLE (
product_name NVARCHAR(50),
rating INT
)
INSERT INTO @product_history
SELECT P.Name AS product_name, AVG(ALL R.rating) FROM Production.product P
INNER JOIN Production.product_review R
ON R.product_ID = P.product_ID
GROUP BY P.Name;
DELETE FROM @product_history
WHERE rating < 2;
SELECT * FROM @product_history;
END
GO;
EXEC product_historyCREATE OR REPLACE PROCEDURE product_history ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', OBJECT_SCHEMA_NAME = 'UNKNOWN', ERROR_HANDLERS, NUM_ROWS_AFFECTED, PROC_NAME = arguments.callee.name, DOLLAR_DOLLAR = '$' + '$';
function* sqlsplit(sql) {
var part = '';
var ismark = () => sql[i] == '$' && sql[i + 1] == '$';
for(var i = 0;i < sql.length;i++) {
if (sql[i] == ';') {
yield part + sql[i];
part = '';
} else if (ismark()) {
part += sql[i++] + sql[i++];
while ( i < sql.length && !ismark() ) {
part += sql[i++];
}
part += sql[i] + sql[i++];
} else part += sql[i];
}
if (part.trim().length) yield part;
};
var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
var fixBind = function (arg) {
arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;
return arg;
};
var EXEC = (stmt,binds = [],severity = "16",noCatch = false) => {
binds = binds ? binds.map(fixBind) : binds;
for(var stmt of sqlsplit(stmt)) {
try {
_RS = snowflake.createStatement({
sqlText : stmt,
binds : binds
});
_ROWS = _RS.execute();
ROW_COUNT = _RS.getRowCount();
NUM_ROWS_AFFECTED = _RS.getNumRowsAffected();
return {
THEN : (action) => !SQLCODE && action(fetch(_ROWS))
};
} catch(error) {
let rStack = new RegExp('At .*, line (\\d+) position (\\d+)');
let stackLine = error.stackTraceTxt.match(rStack) || [0,-1];
MESSAGE_TEXT = error.message.toString();
SQLCODE = error.code.toString();
SQLSTATE = error.state.toString();
snowflake.execute({
sqlText : `SELECT UPDATE_ERROR_VARS_UDF(?,?,?,?,?,?)`,
binds : [stackLine[1],SQLCODE,SQLSTATE,MESSAGE_TEXT,PROC_NAME,severity]
});
throw error;
}
}
};
// END REGION
EXEC(`CREATE OR REPLACE TEMPORARY TABLE T_product_history (
product_name VARCHAR(50),
rating INT
)`);
EXEC(` INSERT INTO T_product_history
SELECT
P.Name AS product_name,
AVG(ALL R.rating) FROM
Production.product P
INNER JOIN
Production.product_review R
ON R.product_ID = P.product_ID
GROUP BY
P.Name`);
EXEC(`DELETE FROM
T_product_history
WHERE
rating < 2`);
EXEC(`
SELECT
*
FROM
T_product_history`);
$$;
!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
;
CALL product_history();SSC-EWI-0068: User defined function was transformed to a Snowflake procedure.