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.

Query

IN -> Oracle_01.sql

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;

Output

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.

Query

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 ***/!!!
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
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "" }}'
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;

Output

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;

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

Nested Table Array Type Definition

Nested 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:

Object 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

Oracle

IN -> Oracle_02.sql

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;

Snowflake

OUT -> Oracle_02.sql

CREATE OR REPLACE PROCEDURE proc1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
   DECLARE
      col1 VARIANT /*** SSC-FDM-0015 - REFERENCED CUSTOM TYPE 'phone_number_list' IN QUERY NOT FOUND, USAGES MAY BE AFFECTED ***/ := phone_number_list() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'phone_number_list' NODE ***/!!!;
   BEGIN
      IF (ARRAY_SIZE(:col1) = 0) THEN
         --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
         CALL DBMS_OUTPUT.PUT_LINE_UDF('IS EMPTY');
      END IF;
   END;
$$;

Output

PROC1
IS EMPTY

4.2 Select statements

Outputs may differ from tables to ARRAYs.

Oracle

Query

IN -> Oracle_03.sql

SELECT
    t.*
FROM
    employee e,
    table(e.phone_numbers_col) t
WHERE
    emp_id = 1;

Output

PHONE_NUMBER
1234567890

Snowflake

Query

OUT -> Oracle_03.sql

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;

Output

PHONE_NUMBERS_COL
[ 1234567890 ]

Related EWIs

1. SSC-EWI-0056: Create Type Not Supported.

2. SSC-EWI-0062: Custom type usage changed to variant.

3. SSC-EWI-0073: Pending Functional Equivalence Review.

4. SSC-EWI-OR0035: The table function is not supported when it is used as a collection of expressions.

5. SSC-FDM-0006: Number type column may not behave similarly in Snowflake.

6. SSC-FDM-0015: ​Referenced custom type in query not found.

7. SSC-FDM-OR0035: DBMS_OUTPUT.PUTLINE check UDF implementation.