WITH, SELECT, and BULK COLLECT INTO statements
Last updated
Last updated
This section is a translation specification. Information may change in the future.
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:
.
The following query is used for the following examples.
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.
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).
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.
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.
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.
Review the following .