WITH, SELECT, and BULK COLLECT INTO statements

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:

• SELECT INTO Statement Documentation.

• SnowConvert Bulk Collect translation.

Sample Source Patterns

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

The following query is used for the following examples.

Oracle

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

Snowflake

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

Query

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

Result

The query does not return results but the expected gathered information would be the IT Salary Information used for the example:

IT_Salary
75000
80000

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

Query

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.

  /* ** 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();

Result

SALARY
77500
80000

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

Query

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

Result

Statement processed.
Average Salary for IT Department: 77500

Snowflake Scripting

Query

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

Result

SIMPLE_PROCEDURE
Average Salary for IT Department: 77500

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

1. SSC-EWI-0058: Functionality is not currently supported by Snowflake Scripting.
2. SSC-EWI-0062: Custom type usage changed to variant.
3. SSC-EWI-0073: Pending Functional Equivalence Review
4. SSC-EWI-OR0036: Types resolution issues, the arithmetic operation may not behave correctly between string and date.
5. SSC-EWI-OR0072: Procedural Member not supported
6. SSC-EWI-OR0104: Unusable collection variable.
7. SSC-FDM-0006: Number type column may not behave similarly in Snowflake.
8. SSC-FDM-OR0035: DBMS_OUTPUT.PUTLINE check UDF implementation.