IS EMPTY
This is a translation reference to convert the Oracle IS EMPTY statement to Snowflake
Last updated
This is a translation reference to convert the Oracle IS EMPTY statement to Snowflake
Last updated
This section is a work in progress; information may change in the future.
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).
nested_table IS [ NOT ] EMPTYThe 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;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;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;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 TypeNested 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 DefinitionSince 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 DefinitionARRAY 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:
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;Outputs may differ from tables to ARRAYs.
SELECT
t.*
FROM
employee e,
table(e.phone_numbers_col) t
WHERE
emp_id = 1;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;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.