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.
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;
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.
EMP_NAME
Jane Smith
Other possible combinations
Description
Oracle
Snowflake
Ask for a IS NOT EMPTY
Ask for NULL instead of EMPTY
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.
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.
3. 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.
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.
-- ** MSC-WARNING - MSCEWI1056 - 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 VARCHAR(30) /*** MSC-WARNING - MSCEWI1036 - VARCHAR2 DATA TYPE CONVERTED TO VARCHAR ***/)
;
-- ** MSC-ERROR - MSCEWI1057 - CREATE TYPE NESTED TABLE VARIANT IS NOT SUPPORTED **
-- CREATE TYPE phone_number_list AS TABLE OF VARIANT /*** MSC-WARNING - MSCEWI1062 - CUSTOM TYPE 'phone_number_type' USAGE CHANGED TO VARIANT ***/
;
CREATE OR REPLACE TABLE employee
/*** MSC-WARNING - MSCEWI3024 - TABLE PROPERTIES REMOVED BECAUSE SNOWFLAKE DOES NOT REQUIRE THEM. ***/
(
emp_id NUMBER(38, 18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/,
emp_name VARCHAR(50) /*** MSC-WARNING - MSCEWI1036 - VARCHAR2 DATA TYPE CONVERTED TO VARCHAR ***/,
phone_numbers_col VARIANT /*** MSC-WARNING - MSCEWI1062 - CUSTOM TYPE 'phone_number_list' USAGE CHANGED TO VARIANT ***/
);
--** MSC-WARNING - MSCEWI1063 - ADDED STATEMENTS BECAUSE 'employee' USED A CUSTOM TYPE **
CREATE OR REPLACE VIEW PUBLIC.employee_view
AS
SELECT
emp_id,
emp_name,
phone_numbers_col -- ** MSC-WARNING - MSCEWI1056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED BY A VARIANT **
FROM
employee;
INSERT INTO employee
SELECT
1,
'John Doe',
-- ** MSC-WARNING - MSCEWI1056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED BY A VARIANT **
ARRAY_CONSTRUCT(1234567890)
;
INSERT INTO employee
SELECT
2,
'Jane Smith',
-- ** MSC-WARNING - MSCEWI1056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED BY A VARIANT **
ARRAY_CONSTRUCT();
-- NOTE: Equivalence for IS EMPTY
SELECT emp_name
FROM
employee
WHERE ARRAY_SIZE(phone_numbers_col) = 0; -- ** MSC-WARNING - MSCEWI1056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED BY A VARIANT **
(...)
WHERE phone_numbers_col IS NOT EMPTY;
(...)
WHERE ARRAY_SIZE(phone_numbers_col) != 0;
(...)
WHERE phone_numbers_col IS NULL;
(...)
WHERE ARRAY_SIZE(phone_numbers_col) IS NULL;
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;
CREATE OR REPLACE PROCEDURE proc1 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
col1 VARIANT:= ARRAY_CONSTRUCT();
BEGIN
IF ( ARRAY_SIZE(col1) = 0)
THEN
RETURN 'IS EMPTY';
END IF;
END;
$$;
SELECT t.*
FROM employee e, table(e.phone_numbers_col) t WHERE emp_id = 1
;
SELECT phone_numbers_col
FROM
employee e
WHERE e.emp_id = 1;
