IS EMPTY
This is a translation reference to convert the Oracle IS EMPTY statement to Snowflake
This section is a work in progress; information may change in the future.
Description
Use the IS [NOT] EMPTY conditions to test whether a specified nested table is empty, regardless whether any elements of the collection are NULL. (Documentation).
Oracle syntax
nested_table IS [ NOT ] EMPTYSample Source Patterns
Oracle
The following example shows the usage of the IS EMPTY statement. The statement is applied over a nested table which uses a UDT as the definition type. The output shows the name of the employees who do not have a phone number.
CREATE TYPE phone_number_type AS OBJECT (phone_number VARCHAR2(30));
/
CREATE TYPE phone_number_list AS TABLE OF phone_number_type;
CREATE TABLE employee (
emp_id NUMBER,
emp_name VARCHAR2(50),
phone_numbers_col phone_number_list
) NESTED TABLE phone_numbers_col STORE AS nested_tab return as value;
INSERT INTO employee VALUES (
1,
'John Doe',
phone_number_list(phone_number_type('1234567890'))
);
/
INSERT INTO employee VALUES (
2,
'Jane Smith',
phone_number_list()
);
SELECT emp_name
FROM employee
WHERE phone_numbers_col IS EMPTY;Snowflake
The Snowflake query shown below is the equivalence of the functionality of the IS EMPTY statement. Particularly, the IS EMPTY statement has a difference between a NULL and an EMPTY object.
Notice that the User-Defined Types are transformed to a VARIANT. The VARIANT type in Snowflake is able to store objects and arrays. Since a nested table is a sequence of information, the ARRAY type is the most suitable type to redefine them and verify is the object ARRAY is empty.
The ARRAY_SIZE equivalent solution also allows to ask for nullability of the nested table (transformed to VARIANT). In other words, the VARIANT type can also store NULLs and empty ARRAYs.
!!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO VARIANT ***/!!!
CREATE TYPE phone_number_type AS OBJECT (phone_number VARCHAR2(30))
;
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'NESTED TABLE' NODE ***/!!!
CREATE TYPE phone_number_list AS TABLE OF phone_number_type;
CREATE OR REPLACE TABLE employee (
emp_id NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
emp_name VARCHAR(50),
phone_numbers_col VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'phone_number_list' USAGE CHANGED TO VARIANT ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
CREATE OR REPLACE VIEW PUBLIC.employee_view
AS
SELECT
emp_id,
emp_name,
phone_numbers_col
FROM
employee;
INSERT INTO employee
VALUES (
1,
'John Doe',
phone_number_list(phone_number_type('1234567890') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'phone_number_type' NODE ***/!!!) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'phone_number_list' NODE ***/!!!
);
INSERT INTO employee
VALUES (
2,
'Jane Smith',
phone_number_list() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'phone_number_list' NODE ***/!!!
);
SELECT emp_name
FROM
employee
WHERE
ARRAY_SIZE( phone_numbers_col) = 0;Other possible combinations
Ask for a IS NOT EMPTY
(...)
WHERE phone_numbers_col IS NOT EMPTY;(...)
WHERE ARRAY_SIZE(phone_numbers_col) != 0;Ask for NULL instead of EMPTY
(...)
WHERE phone_numbers_col IS NULL;(...)
WHERE ARRAY_SIZE(phone_numbers_col) IS NULL;Known Issues
1. User-defined types are being transformed into Variant.
User-defined types are not supported thus they are transformed into Variant types which could need manual effort to ensure some functionalities.
Review the following page for more information:
Create Type2. Nested tables are not supported.
Nested tables are not currently supported. The best approach based on this equivalence is to handle nested tables as Variant but declare Arrays with JSON data inside and execute the PARSE_JSON Snowflake function to populate the nested information.
Review the following pages for more information:
Nested Table Array Type DefinitionNested Table Type Definition3. Insert statements are not supported for User-defined types.
Since User-defined types are not supported in consequence the Insert statements to these types are not supported. Specifically in nested tables, the INSERT INTO ... VALUES statement has to be changed to a INSERT INTO ...SELECT because the ARRAY_CONSTRUCT function is expected to be used in that pattern.
Review the following page for more information:
Object Type Definition4. Logic should be adapted to ARRAY types.
ARRAY types.Since the nested tables should be equivalently transformed to VARIANT and behave as ARRAYs,the functionality and logic of implementing procedures and interaction with the data should be adapted.
Review the following examples:
4.1 Procedures equivalence
create or replace procedure proc1
as
col1 phone_number_list:= phone_number_list();
begin
IF col1 IS EMPTY
THEN
dbms_output.put_line('IS EMPTY');
END IF;
end;4.2 Select statements
Outputs may differ from tables to ARRAYs.
Oracle
SELECT
t.*
FROM
employee e,
table(e.phone_numbers_col) t
WHERE
emp_id = 1;Snowflake
SELECT
t.*
FROM
employee e,
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0035 - TABLE FUNCTION IS NOT SUPPORTED WHEN IT IS USED AS A COLLECTION OF EXPRESSIONS ***/!!!
table(e.phone_numbers_col) t
WHERE
emp_id = 1;Related EWIs
SSC-EWI-0056: Create Type Not Supported.
SSC-EWI-0063: Added statements because object used a custom type.
SSC-EWI-0073: Pending Functional Equivalence Review.
SSC-EWI-OR0035: The table function is not supported when it is used as a collection of expressions.
SSC-FDM-0006: Number type column may not behave similarly in Snowflake.
Last updated