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:

• SELECT INTO Statement Documentation.

• SnowConvert Bulk Collect translation.

Sample Source Patterns

The following query is used for the following examples.

Oracle

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

-- Sample MySampleTable table
CREATE OR REPLACE TABLE MySampleTable (
  MySampleID NUMBER(38, 18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/ PRIMARY KEY,
   FirstName VARCHAR(50) /*** MSC-WARNING - MSCEWI1036 - VARCHAR2 DATA TYPE CONVERTED TO VARCHAR ***/,
   Salary NUMBER(38, 18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/,
   Department VARCHAR(50) /*** MSC-WARNING - MSCEWI1036 - VARCHAR2 DATA TYPE CONVERTED TO VARCHAR ***/
 );

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

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

CREATE OR REPLACE PROCEDURE simple_procedure ()
RETURNS TABLE()
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
   v_salaries RESULTSET;
   
   BEGIN

   v_salaries := (    WITH IT_Employees AS
    (
      SELECT Salary
      FROM
        MySampleTable
      WHERE Department = 'IT'
    )
    SELECT Salary FROM IT_Employees);
    
    RETURN TABLE(v_salaries);

   END;
$$;


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

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

CREATE OR REPLACE PROCEDURE simple_procedure()
RETURNS VARCHAR
LANGUAGE SQL
AS

  DECLARE
    v_average_salary INTEGER DEFAULT 0;
    res1 RESULTSET;
    salaries_count FLOAT;
    
  BEGIN
  salaries_count := 0.0;
  res1 := (    WITH IT_Employees AS
    (
      SELECT Salary
      FROM
        Employees
      WHERE Department = 'IT'
    )
    SELECT Salary FROM IT_Employees);
    
    LET cur1 CURSOR FOR res1;
    
     -- Calculate the average salary
    FOR row_variable IN cur1 DO
      v_average_salary := v_average_salary + row_variable.Salary;
      salaries_count := salaries_count + 1;
    END FOR;

    v_average_salary := v_average_salary / salaries_count;
    
    --** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DBMS_OUTPUT.PUT_LINE' INSERTED. **
    --** MSC-WARNING - MSCEWI3115 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE. **
    CALL DBMS_OUTPUT.PUT_LINE('Average Salary for IT Department: ' || v_average_salary);

  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.