MSCEWI3082

Cannot Convert Nested Type Attribute Expression

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

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:

CREATE OR REPLACE TYPE type1 AS OBJECT (type1_column1 INT);

CREATE TABLE tableA
(
  tableARow1 type1
);

SELECT tableARow1.type1_column1 FROM tableA;

Output Code:

-- ** MSC-WARNING - MSCEWI1056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO A VARIANT **
--CREATE OR REPLACE TYPE type1 AS OBJECT (type1_column1 INT)
                                                          ;

CREATE OR REPLACE TABLE PUBLIC.tableA (
tableARow1 VARIANT /*** MSC-WARNING - MSCEWI1059 - CUSTOM TYPE 'type1' USAGE CHANGED TO VARIANT ***/);

--** MSC-WARNING - MSCEWI1060 - ADDED STATEMENTS BECAUSE 'tableA' USED A CUSTOM TYPE **
CREATE OR REPLACE VIEW PUBLIC.tableA_view AS
SELECT
tableARow1:type1_column1 :: INTEGER AS type1_column1
FROM PUBLIC.tableA;

SELECT
-- ** MSC-ERROR - MSCEWI3082 - CANNOT CONVERT NESTED TYPE ATTRIBUTE EXPRESSION **
--       tableARow1.type1_column1
                                FROM PUBLIC.tableA;

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 [email protected]