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 ] 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;

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.

-- ** 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 **

Other possible combinations

DescriptionOracleSnowflake

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:

pageCreate Type

2. 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:

pageNested Table Array Type DefinitionpageNested Table Type Definition

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.

Review the following page for more information:

pageObject Type Definition

4. Logic should be adapted to 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 phone_numbers_col
FROM
    employee e
    WHERE e.emp_id = 1;

Snowflake

Related EWIs

  1. MSCEWI1056: Custom Types Not Supported.

  2. MSCEWI1057: Create Type Variant Not Supported.

  3. ​MSCEWI1062​: Custom type usage changed to variant.

  4. ​MSCEWI1063: ​Added statements because object used a custom type.

PreviousIFNextLOG ERROR

Last updated