# WITH, SELECT, and BULK COLLECT INTO statements

{% hint style="danger" %}
This section is a translation specification. Information may change in the future.
{% endhint %}

{% hint style="info" %}
Some parts in the output code are omitted for clarity reasons.
{% endhint %}

## 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:&#x20;

* [SELECT INTO Statement Documentation](https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/SELECT-INTO-statement.html#GUID-6E14E04D-4344-45F3-BE80-979DD26C7A90).
* [SnowConvert Bulk Collect translation](..#bulk-collect-from-a-table).

## Sample Source Patterns

{% hint style="info" %}
Some parts in the output code are omitted for clarity reasons.
{% endhint %}

The following query is used for the following examples.

{% tabs %}
{% tab title="Oracle" %}
{% code title="IN -> Oracle_01.sql" %}
```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');
```
{% endcode %}
{% endtab %}

{% tab title="Snowflake" %}
{% code title="OUT -> Oracle_01.sql" %}
```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');
```
{% endcode %}
{% endtab %}
{% endtabs %}

### 1. Inside procedure simple case

{% hint style="danger" %}
This is an approach that uses a resultset data type. User-defined types must be reviewed. Review the following [Snowflake documentation](https://docs.snowflake.com/developer-guide/snowflake-scripting/resultsets) to review more information about RESULTSETs.
{% endhint %}

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.&#x20;

#### Oracle

{% tabs %}
{% tab title="Query" %}
<pre class="language-plsql" data-title="IN -> Oracle_02.sql"><code class="lang-plsql">-- Additional Params: -t JavaScript
<strong>CREATE OR REPLACE PROCEDURE simple_procedure
</strong>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();
</code></pre>
{% endtab %}

{% tab title="Result" %}
{% hint style="info" %}
The query does not return results but the expected gathered information would be the IT Salary Information used for the example:
{% endhint %}

| IT\_Salary |
| ---------- |
| 75000      |
| 80000      |
{% endtab %}
{% endtabs %}

{% hint style="danger" %}
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](https://docs.snowflake.com/developer-guide/snowflake-scripting/resultsets#limitations-of-the-resultset-data-type) for more information).
{% endhint %}

#### Snowflake Scripting

{% tabs %}
{% tab title="Query" %}
{% code title="OUT -> Oracle_02.sql" %}
```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();
```
{% endcode %}
{% endtab %}

{% tab title="Result" %}
| SALARY |
| ------ |
| 77500  |
| 80000  |
{% endtab %}
{% endtabs %}

### 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.](https://docs.snowflake.com/developer-guide/snowflake-scripting/cursors#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](https://docs.snowflake.com/sql-reference/snowflake-scripting/let#cursor-assignment-syntax) documentation.

#### Oracle

{% tabs %}
{% tab title="Query" %}
{% code title="IN -> Oracle_03.sql" %}
```plsql
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();
```
{% endcode %}
{% endtab %}

{% tab title="Result" %}
```
Statement processed.
Average Salary for IT Department: 77500
```
{% endtab %}
{% endtabs %}

#### Snowflake Scripting

{% tabs %}
{% tab title="Query" %}
{% code title="OUT -> Oracle_03.sql" %}
```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
--		--** 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();
```
{% endcode %}
{% endtab %}

{% tab title="Result" %}
| SIMPLE\_PROCEDURE                       |
| --------------------------------------- |
| Average Salary for IT Department: 77500 |
{% endtab %}
{% endtabs %}

## Known Issues

#### 1. Resulset limitations.

There are limitations while using the RESULTSET data type. Review the following [Snowflake documentation ](https://docs.snowflake.com/developer-guide/snowflake-scripting/resultsets#limitations-of-the-resultset-data-type)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](..#bulk-collect-from-a-table).

## Related EWIs

1. [SSC-FDM-0006](https://app.gitbook.com/s/E6G3vS8yE23cQPQj7xGQ/general/technical-documentation/issues-and-troubleshooting/functional-difference/general/ssc-fdm-0006): Number type column may not behave similarly in Snowflake.
2. [SSC-EWI-0020](https://app.gitbook.com/s/E6G3vS8yE23cQPQj7xGQ/general/technical-documentation/issues-and-troubleshooting/conversion-issues/general/ssc-ewi-0020): Custom UDF inserted.
3. [SSC-FDM-0024:](https://app.gitbook.com/s/E6G3vS8yE23cQPQj7xGQ/general/technical-documentation/issues-and-troubleshooting/functional-difference/general/ssc-fdm-0024) Functionality is not currently supported by Snowflake Scripting.
4. [SSC-EWI-0062](https://app.gitbook.com/s/E6G3vS8yE23cQPQj7xGQ/general/technical-documentation/issues-and-troubleshooting/conversion-issues/general/ssc-ewi-0062): Custom type usage changed to variant.
5. [SSC-EWI-0073](https://app.gitbook.com/s/E6G3vS8yE23cQPQj7xGQ/general/technical-documentation/issues-and-troubleshooting/conversion-issues/general/ssc-ewi-0073): Pending Functional Equivalence Review
6. [SSC-EWI-OR0036](https://app.gitbook.com/s/E6G3vS8yE23cQPQj7xGQ/general/technical-documentation/issues-and-troubleshooting/conversion-issues/oracle/ssc-ewi-or0036): Types resolution issues, the arithmetic operation may not behave correctly between string and date.
7. [SSC-EWI-OR0072](https://app.gitbook.com/s/E6G3vS8yE23cQPQj7xGQ/general/technical-documentation/issues-and-troubleshooting/conversion-issues/oracle/ssc-ewi-or0072): Procedural Member not supported
8. [SSC-EWI-OR0104](https://app.gitbook.com/s/E6G3vS8yE23cQPQj7xGQ/general/technical-documentation/issues-and-troubleshooting/conversion-issues/oracle/ssc-ewi-or0104): Unusable collection variable
9. SSC-EWI-OR0115: DBMS\_OUTPUT.PUTLINE check UDF implementation
