WITH, SELECT, and BULK COLLECT INTO statements

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

This section is a translation specification. Information may change in the future.

Some parts in the output code are omitted for clarity reasons.

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

Some parts in the output code are omitted for clarity reasons.

The following query is used for the following examples.

IN -> Oracle_01.sql

-- 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');

OUT -> Oracle_01.sql

-- Sample MySampleTable table
CREATE OR REPLACE TABLE MySampleTable (
   MySampleID NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/ PRIMARY KEY,
   FirstName VARCHAR(50),
   Salary NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
   Department VARCHAR(50)
 )
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

-- 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

IN -> Oracle_02.sql

-- 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

OUT -> Oracle_02.sql

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.

  !!!RESOLVE EWI!!! /*** SSC-EWI-OR0072 - PROCEDURAL MEMBER TYPE DEFINITION NOT SUPPORTED. ***/!!!
  /*   TYPE salary_collection IS TABLE OF NUMBER */
  ;
  !!!RESOLVE EWI!!! /*** SSC-EWI-OR0104 - UNUSABLE VARIABLE, ITS TYPE WAS NOT TRANSFORMED ***/!!!
  /*   v_salaries salary_collection := salary_collection() */
  ;
  EXEC(`SELECT Salary
    FROM
       MySampleTable
    WHERE Department = 'IT'`);
  [
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'PlBulkCollectionItem' NODE ***/!!!
    //v_salaries
    null,V_SALARIES] = EXEC(`SELECT
   Salary
 FROM IT_Employees`);
$$;

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

IN -> Oracle_03.sql

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

OUT -> Oracle_03.sql

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
--		!!!RESOLVE EWI!!! /*** SSC-EWI-0058 - 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'
		)
		!!!RESOLVE EWI!!! /*** SSC-EWI-0058 - 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;
		  --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
		  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 ***/!!!
 		                                                                                                                                                                        --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
 		                                                                                                                                                                        LOOP
		  	v_average_salary :=
		  	!!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN NUMBER 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.

SSC-EWI-0058: 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-FDM-0006: Number type column may not behave similarly in Snowflake.
SSC-FDM-OR0035: DBMS_OUTPUT.PUTLINE check UDF implementation.

PreviousCollection Bulk Operations NextRecord Type Definition

Last updated 5 months ago

hashtagDescription

hashtagSample Source Patterns

hashtag1. Inside procedure simple case

hashtagOracle

hashtagSnowflake Scripting

hashtag2. Simple case for iterations: FOR LOOP statement

hashtagOracle

hashtagSnowflake Scripting

hashtagKnown Issues

hashtag1. Resulset limitations.

hashtag2. Execute statements with Bulk Collect clause are not supported.

hashtagRelated EWIs

Description

Sample Source Patterns

1. Inside procedure simple case

Oracle

Snowflake Scripting

2. Simple case for iterations: FOR LOOP statement

Oracle

Snowflake Scripting

Known Issues

1. Resulset limitations.

2. Execute statements with Bulk Collect clause are not supported.

Related EWIs