WITH, SELECT, and BULK COLLECT INTO statements
This section is a translation specification. Information may change in the future.
Description
This section is a translation specification for the statement WITH subsequent to a SELECT statement which uses a BULK COLLECT INTO statement. For more information review the following documentation:
Sample Source Patterns
The following query is used for the following examples.
-- Sample MySampleTable table
CREATE TABLE MySampleTable (
MySampleID NUMBER PRIMARY KEY,
FirstName VARCHAR2(50),
Salary NUMBER,
Department VARCHAR2(50)
);
-- Insert some sample data
INSERT INTO MySampleTable (MySampleID, FirstName, Salary, Department)
VALUES (1, 'Bob One', 50000, 'HR');
INSERT INTO MySampleTable (MySampleID, FirstName, Salary, Department)
VALUES (2, 'Bob Two', 60000, 'HR');
INSERT INTO MySampleTable (MySampleID, FirstName, Salary, Department)
VALUES (3, 'Bob Three', 75000, 'IT');
INSERT INTO MySampleTable (MySampleID, FirstName, Salary, Department)
VALUES (4, 'Bob Four', 80000, 'IT');
1. Inside procedure simple case
This is an approach that uses a resultset data type. User-defined types must be reviewed. Review the following Snowflake documentation to review more information about RESULTSETs.
The following example uses a User-defined type and it is declared indirectly as a table. The translation for this case implements a RESULTSET as a data type in Snowflake. The resultset is stored on a variable which must be returned wrapped on a TABLE()
function.
Oracle
-- Additional Params: -t JavaScript
CREATE OR REPLACE PROCEDURE simple_procedure
IS
TYPE salary_collection IS TABLE OF NUMBER;
v_salaries salary_collection := salary_collection();
BEGIN
WITH IT_Employees AS (
SELECT Salary
FROM MySampleTable
WHERE Department = 'IT'
)
SELECT Salary BULK COLLECT INTO v_salaries
FROM IT_Employees;
END;
CALL simple_procedure();
One of the limitations of the RESULTSETs is that they cannot be used as tables. E.g.: select * from my_result_set;
(This is an error, review the following documentation for more information).
Snowflake Scripting
CREATE OR REPLACE PROCEDURE simple_procedure ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
/* ** SSC-EWI-OR0072 - PROCEDURAL MEMBER TYPE DEFINITION NOT SUPPORTED. ** */
/* TYPE salary_collection IS TABLE OF NUMBER */
;
/* ** SSC-EWI-OR0104 - UNUSABLE VARIABLE, ITS TYPE WAS NOT TRANSFORMED ** */
/* v_salaries salary_collection := salary_collection() */
;
// WITH IT_Employees AS (
// SELECT Salary
// FROM MySampleTable
// WHERE Department = 'IT'
// )
// SELECT Salary BULK COLLECT INTO v_salaries
// FROM IT_Employees
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'WithCte' NODE ***/!!!
null
$$;
CALL simple_procedure();
2. Simple case for iterations: FOR LOOP statement
The following case is to define a translation for iteration with FOR...LOOP
. In this case, the User-defined type is implicitly a table, thus, it is possible to use a cursor to iterate. Review the following documentation to learn more:
Snowflake documentation about Returning a Table for a Cursor.
In this case, there is a need to create a cursor for the iteration. Review the following Cursor Assignment Syntax documentation.
Oracle
CREATE OR REPLACE PROCEDURE simple_procedure
IS
TYPE salary_collection IS TABLE OF NUMBER;
v_salaries salary_collection := salary_collection();
v_average_salary NUMBER;
salaries_count NUMBER;
BEGIN
salaries_count := 0;
WITH IT_Employees AS (
SELECT Salary
FROM MySampleTable
WHERE Department = 'IT'
)
SELECT Salary BULK COLLECT INTO v_salaries
FROM IT_Employees;
-- Calculate the average salary
IF v_salaries.COUNT > 0 THEN
v_average_salary := 0;
FOR i IN 1..v_salaries.COUNT LOOP
v_average_salary := v_average_salary + v_salaries(i);
salaries_count := salaries_count + 1;
END LOOP;
v_average_salary := v_average_salary / salaries_count;
END IF;
-- Display the average salary
DBMS_OUTPUT.PUT_LINE('Average Salary for IT Department: ' || v_average_salary);
END;
/
CALL simple_procedure();
Snowflake Scripting
CREATE OR REPLACE PROCEDURE simple_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
-- --** SSC-FDM-0024 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
-- TYPE salary_collection IS TABLE OF NUMBER;
v_salaries VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'salary_collection' USAGE CHANGED TO VARIANT ***/!!! := salary_collection();
v_average_salary NUMBER(38, 18);
salaries_count NUMBER(38, 18);
BEGIN
salaries_count := 0;
WITH IT_Employees AS
(
SELECT Salary
FROM
MySampleTable
WHERE Department = 'IT'
)
-- --** SSC-FDM-0024 - FUNCTIONALITY FOR 'RECORDS AND COLLECTIONS' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
-- SELECT Salary BULK COLLECT INTO v_salaries
-- FROM IT_Employees
;
-- Calculate the average salary
IF (null /*v_salaries.COUNT*/!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'NESTED TABLE CUSTOM TYPE EXPRESSION' NODE ***/!!! > 0) THEN
v_average_salary := 0;
FOR i IN 1 TO 0 /*v_salaries.COUNT*/!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'NESTED TABLE CUSTOM TYPE EXPRESSION' NODE ***/!!! LOOP
v_average_salary :=
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN ExactNumeric AND salary_collection ***/!!!
:v_average_salary + v_salaries(i);
salaries_count := :salaries_count + 1;
END LOOP;
v_average_salary := :v_average_salary / :salaries_count;
END IF;
-- Display the average salary
--** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
CALL DBMS_OUTPUT.PUT_LINE_UDF('Average Salary for IT Department: ' || NVL(:v_average_salary :: STRING, ''));
END;
$$;
CALL simple_procedure();
Known Issues
1. Resulset limitations.
There are limitations while using the RESULTSET data type. Review the following Snowflake documentation to learn more. Markable limitations are the following:
Declaring a column of type RESULTSET.
Declaring a parameter of type RESULTSET.
Declaring a stored procedure’s return type as a RESULTSET.
2. Execute statements with Bulk Collect clause are not supported.
Review the following documentation.
Related EWIs
SSC-FDM-0006: Number type column may not behave similarly in Snowflake.
SSC-EWI-0020: Custom UDF inserted.
SSC-FDM-0024: Functionality is not currently supported by Snowflake Scripting.
SSC-EWI-0062: Custom type usage changed to variant.
SSC-EWI-0073: Pending Functional Equivalence Review
SSC-EWI-OR0036: Types resolution issues, the arithmetic operation may not behave correctly between string and date.
SSC-EWI-OR0072: Procedural Member not supported
SSC-EWI-OR0104: Unusable collection variable
SSC-EWI-OR0115: DBMS_OUTPUT.PUTLINE check UDF implementation
Last updated