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 ] EMPTY
Sample 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.
IN -> Oracle_01.sql
CREATETYPEphone_number_typeASOBJECT (phone_number VARCHAR2(30));/CREATETYPEphone_number_listASTABLE OF phone_number_type;CREATETABLEemployee ( emp_id NUMBER, emp_name VARCHAR2(50), phone_numbers_col phone_number_list) NESTED TABLE phone_numbers_col STORE AS nested_tab returnasvalue;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_nameFROM employeeWHERE phone_numbers_col ISEMPTY;
EMP_NAME
Jane Smith
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.
OUT -> Oracle_01.sql
!!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO VARIANT ***/!!!
CREATETYPEphone_number_typeASOBJECT (phone_number VARCHAR2(30));!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'NESTED TABLE' NODE ***/!!!CREATETYPEphone_number_listASTABLE OF phone_number_type;CREATE OR REPLACETABLEemployee ( 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 REPLACEVIEWPUBLIC.employee_viewCOMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "" }}'
ASSELECT emp_id, emp_name, phone_numbers_colFROM employee;INSERT INTO employeeVALUES (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 employeeVALUES (2,'Jane Smith', phone_number_list() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'phone_number_list' NODE ***/!!!
);SELECT emp_nameFROM employeeWHERE ARRAY_SIZE( phone_numbers_col) =0;
EMP_NAME
Jane Smith
Other possible combinations
Description
Oracle
Snowflake
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;