SSC-EWI-OR0082

Cannot Convert Nested Type Attribute Expression

Some parts of the output code are omitted for clarity reasons.

Severity

Medium

Description

This error message appears when a query, like a select, tries to access an attribute within a column that was defined as a type. These cannot be automatically converted, but they can be quickly converted by hand.

Example Code:

Input Code Oracle:

IN -> Oracle_01.sql

CREATE TYPE type1 AS OBJECT (
  attribute1 VARCHAR2(20),
  attribute2 NUMBER
);
CREATE TYPE type2 AS OBJECT (
  property1 type1,
  property2 DATE
);
CREATE TABLE my_table (
  id NUMBER PRIMARY KEY,
  column1 type2
);
INSERT INTO my_table VALUES (
  1, type2(type1('value1', 100), SYSDATE)
);
SELECT column1.property1.attribute1, column1.property2
FROM my_table;

Output Code:

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 type1 AS OBJECT (
  attribute1 VARCHAR2(20),
  attribute2 NUMBER
)
;

!!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO VARIANT ***/!!!
CREATE TYPE type2 AS OBJECT (
  property1 type1,
  property2 DATE
)
;

CREATE OR REPLACE TABLE my_table (
  id NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/ PRIMARY KEY,
  column1 VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'type2' USAGE CHANGED TO VARIANT ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE VIEW PUBLIC.my_table_view
AS
SELECT
  id,
  column1:property1:attribute1 :: VARCHAR AS attribute1,
  column1:property1:attribute2 :: NUMBER AS attribute2,
  column1:property2 :: DATE AS property2
FROM
  my_table;

INSERT INTO my_table
VALUES (
  1, type2(type1('value1', 100) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'type1' NODE ***/!!!, CURRENT_TIMESTAMP()) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'type2' NODE ***/!!!
);

SELECT
  !!!RESOLVE EWI!!! /*** SSC-EWI-OR0082 - CANNOT CONVERT NESTED TYPE ATTRIBUTE EXPRESSION ***/!!! column1.property1.attribute1,
  column1.property2
FROM
  my_table;

Related EWIS

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

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

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

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

Recommendations

• The code can be manually fixed by changing the '.' accessor for the ':' wherever a type column is being accessed.

• If you need more support, you can email us at [e-mail]