MULTI-STATEMENT TABLE-VALUED
Translation reference to convert Transact-SQL UDF (User Defined Functions) with TABLE return type to Snowflake.
Last updated
Translation reference to convert Transact-SQL UDF (User Defined Functions) with TABLE return type to Snowflake.
Last updated
Some parts in the output code are omitted for clarity reasons.
All the code samples on this page have not been implemented yet in SnowConvert. They should be interpreted as a reference for how each scenario should be translated to Snowflake. These translations may change in the future.Some parts in the output code are omitted for clarity reasons.
Multi-statement table-valued is similar to Inline-statement 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();BEHAVIORAL_SEGMENT|
------------------+
Unknown|!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION calc_behavioral_segment ()
RETURNS BEHAVIORAL_SEGMENTS TABLE (
behavioral_segment VARCHAR(50))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
DECLARE @col varchar(15)
SET @col = 'Unknown'
INSERT INTO @behavioral_segments
SELECT @col
RETURN
END
SELECT * FROM calc_behavioral_segment();;BEHAVIORAL_SEGMENT|
------------------+
Unknown|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); NUMBER_TYPE|
------------------+
Odd|!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION odd_or_even_number (NUMBER INT)
RETURNS NUMBERS TABLE (
number_type VARCHAR(15))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
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);; NUMBER_TYPE|
------------------+
Odd|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'); ID_EMPLOYEE| WORKING_FROM_HOME| TEAM| COMPUTER|
--------------+-------------------|---------|-----------|
123456789| 1| TEAM_1| LAPTOP|!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION new_employee_hired (ID STRING, POSITION STRING, EXPERIENCE STRING)
RETURNS NEW_EMPLOYEE TABLE (
id_employee VARCHAR(50),
working_from_home BOOLEAN,
team VARCHAR(15),
computer VARCHAR(15))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
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');; ID_EMPLOYEE| WORKING_FROM_HOME| TEAM| COMPUTER|
--------------+-------------------|---------|-----------|
123456789| 1| TEAM_1| LAPTOP|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(); DEPARTMENT_NAME| FIRST_NAME| LAST_NAME| START_DATE| END_DATE| JOB_TITLE| MONTHS_WORKING|
-------------------------------------+ ------------------+-------------------+----------------+-----------------+------------------------------------+----------------------+
Sales| Syed| Abbas| 2013-03-14| NULL| Pacific Sales Manager| 106|
Production| Kim| Abercrombie| 2010-01-16| NULL| Production Technician - WC60| 144|
Quality Assurance| Hazem| Abolrous| 2009-02-28| NULL| Quality Assurance Manager| 155|
Shipping and Receiving| Pilar| Ackerman| 2009-01-02| NULL| Shipping and Receiving Supervisor| 156|
Production| Jay| Adams| 2009-03-05| NULL| Production Technician - WC60| 154|
Information Services| François| Ajenstat| 2009-01-17| NULL| Database Administrator| 156|
Sales| Amy| Alberts| 2012-04-16| NULL| European Sales Manager| 117|
Production| Greg| Alderson| 2008-12-02| NULL| Production Technician - WC45| 157|
Quality Assurance| Sean| Alexander| 2008-12-28| NULL| Quality Assurance Technician| 157|
Facilities and Maintenance| Gary| Altman| 2009-12-02| NULL| Facilities Manager| 145|
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION get_employees_history ()
RETURNS EMPLOYEE_HISTORY 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
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
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();; DEPARTMENT_NAME| FIRST_NAME| LAST_NAME| START_DATE| END_DATE| JOB_TITLE| MONTHS_WORKING|
-------------------------------------+ ------------------+-------------------+----------------+-----------------+------------------------------------+----------------------+
Sales| Syed| Abbas| 2013-03-14| NULL| Pacific Sales Manager| 106|
Production| Kim| Abercrombie| 2010-01-16| NULL| Production Technician - WC60| 144|
Quality Assurance| Hazem| Abolrous| 2009-02-28| NULL| Quality Assurance Manager| 155|
Shipping and Receiving| Pilar| Ackerman| 2009-01-02| NULL| Shipping and Receiving Supervisor| 156|
Production| Jay| Adams| 2009-03-05| NULL| Production Technician - WC60| 154|
Information Services| François| Ajenstat| 2009-01-17| NULL| Database Administrator| 156|
Sales| Amy| Alberts| 2012-04-16| NULL| European Sales Manager| 117|
Production| Greg| Alderson| 2008-12-02| NULL| Production Technician - WC45| 157|
Quality Assurance| Sean| Alexander| 2008-12-28| NULL| Quality Assurance Technician| 157|
Facilities and Maintenance| Gary| Altman| 2009-12-02| NULL| Facilities Manager| 145|
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'); TYPE| NAME|
--------------+ --------------+
SMALL_TEAM| TEAM1|
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION create_new_team (TEAM_NAME STRING)
RETURNS NEW_TEAM TABLE (
type VARCHAR(50),
name VARCHAR(50))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
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');; TYPE| NAME|
--------------+ --------------+
SMALL_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') VACATION_STATUS|
-----------------+
OK|!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION vacation_status (ID STRING)
RETURNS STATUS TABLE (
vacation_status VARCHAR(30))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
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'); VACATION_STATUS|
-----------------+
OK|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.
--Additional Params: -t JavaScript
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); GROUP_NAME|
------------------+
Tool Design|!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION get_group_name
(DEPARTMENT_ID INT)
RETURNS @group_names TABLE (
group_name VARCHAR(15))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
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);; GROUP_NAME|
------------------+
Tool Design|User-defined functions cannot DECLARE, OPEN, FETCH, CLOSE or DEALLOCATE a CURSOR. Use a Stored Procedure to work with cursors.
--Additional Params: -t JavaScript
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); AMOUNT|
------------------+
3|--Additional Params: -t JavaScript
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION amount_new_specimens (ID INT)
RETURNS @new_specimens TABLE (
amount INT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
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);; AMOUNT|
------------------+
3|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.
--Additional Params: -t JavaScript
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_history PRODUCT_NAME| Rating|
----------------------------------+------------------|
HL Mountain Pedal| 3|
Mountain Bike Socks, M| 5|
Road-550-W Yellow, 40| 5|CREATE 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(); PRODUCT_NAME| Rating|
----------------------------------+------------------|
HL Mountain Pedal| 3|
Mountain Bike Socks, M| 5|
Road-550-W Yellow, 40| 5|SSC-EWI-0040: Statement Not Supported.
SSC-EWI-0073: Pending Functional Equivalence Review