Some parts in the output code are omitted for clarity reasons.
Severity
Medium
Description
This section describes the different issues that could be triggered by PIVOT and UNPIVOT clauses. The not-supported scenarios are presented in the following table.
PIVOT
UNPIVOT
ORACLE
TERADATA
MULTIPLE COLUMN
X
X
X
X
RENAME COLUMN
X
X
X
X
MULTIPLE FUNCTION
X
X
X
WITH CLAUSE
X
X
XML OUTPUT FORMAT
X
X
IN CLAUSE SUBQUERY
X
X
X
IN CLAUSE ANY SEQUENCE
X
X
INCLUDE/EXCLUDE NULLS
X
X
X
MULTIPLE COLUMN
Multiple columns are not supported by PIVOT and UNPIVOT clauses.
Example Code
Input Code:
IN -> Oracle_01.sql
SELECT * FROM star1p UNPIVOT ((sales,cogs) FOR yr_qtr
IN ((Q101Sales, Q101Cogs) AS 'Q101A',
(Q201Sales, Q201Cogs) AS 'Q201A',
(Q301Sales, Q301Cogs) AS 'Q301A')) AS Tmp;
Output Code:
OUT -> Oracle_01.sql
// SnowConvert Helpers Code section is omitted.
SELECT * FROM
star1p
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT MULTIPLE COLUMN NOT SUPPORTED ***/!!!
UNPIVOT ((sales,cogs) FOR yr_qtr
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT MULTIPLE COLUMN NOT SUPPORTED ***/!!!
IN ((Q101Sales, Q101Cogs) AS 'Q101A',
(Q201Sales, Q201Cogs) AS 'Q201A',
(Q301Sales, Q301Cogs) AS 'Q301A')) AS Tmp;
RENAME COLUMN
Renaming columns with aliases is not supported in snowflake UNPIVOT clauses, Snow Convert is going to remove aliases for functions or columns in order to create a valid query and check that this change does not affect the original functionality.
For PIVOT, the use of column aliases is only supported in SnowConvert for Teradata if the following two conditions are true: all expressions inside the IN clause have an alias associated and Snowconvert has information about the columns that will be generated as a result, either by providing the table definition or using a subquery with an explicit column list as input to the clause.
Example Code
Input Code:
IN -> Teradata_01.sql
CREATE TABLE star1(
country VARCHAR(20),
state VARCHAR(10),
yr INTEGER,
qtr VARCHAR(3),
sales INTEGER,
cogs INTEGER
);
--SAMPLE 1
SELECT * FROM db1.star1p UNPIVOT (column1 FOR for_column
IN (col1 AS 'as_col1', col2 AS 'as_col2')) Tmp;
--SAMPLE 2
SELECT *
FROM star1 PIVOT (
SUM(sales) as ss1 FOR qtr
IN ('Q1' AS Quarter1,
'Q2' AS Quarter2,
'Q3' AS Quarter3)
)Tmp;
--SAMPLE 3
SELECT
*
FROM (
SELECT
country,
state,
yr,
qtr,
sales,
cogs
FROM star1 ) A
PIVOT (
SUM(sales) as ss1 FOR qtr
IN ('Q1' AS Quarter1,
'Q2' AS Quarter2,
'Q3' AS Quarter3)
)Tmp;
Output Code:
OUT -> Teradata_01.sql
CREATE OR REPLACE TABLE star1 (
country VARCHAR(20),
state VARCHAR(10),
yr INTEGER,
qtr VARCHAR(3),
sales INTEGER,
cogs INTEGER
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "teradata", "convertedOn": "08/14/2024" }}'
;
--SAMPLE 1
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "db1.star1p" **
SELECT
* FROM db1.star1p UNPIVOT (column1 FOR for_column
IN (col1 AS 'as_col1', col2 AS 'as_col2')) Tmp !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'PivotUnpivotTableReference' NODE ***/!!!;
--SAMPLE 2
SELECT
*
FROM
star1 PIVOT (
SUM(sales) FOR qtr IN ('Q1',
'Q2',
'Q3')) Tmp (
country,
state,
yr,
cogs,
Quarter1_ss1,
Quarter2_ss1,
Quarter3_ss1
);
--SAMPLE 3
SELECT
*
FROM (
SELECT
country,
state,
yr,
qtr,
sales,
cogs
FROM
star1
) A
PIVOT (
SUM(sales) FOR qtr IN ('Q1',
'Q2',
'Q3')) Tmp (
country,
state,
yr,
cogs,
Quarter1_ss1,
Quarter2_ss1,
Quarter3_ss1
);
SELECT *
FROM STAR1 PIVOT(SUM(COL1), SUM(COL2) FOR YR IN ('Y1', 'Y2', 'Y3'))TMP;
Output Code:
OUT -> Teradata_02.sql
// SnowConvert Helpers Code section is omitted.
SELECT
*
FROM
STAR1
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT MULTIPLE FUNCTION NOT SUPPORTED ***/!!!
PIVOT(SUM(COL1), SUM(COL2) FOR YR IN ('Y1', 'Y2', 'Y3'))TMP;
WITH CLAUSE
Teradata PIVOT has an optional WITH clause, this is not allowed in Snowflake's PIVOT.
Example Code
Input Code:
IN -> Teradata_03.sql
SELECT *
FROM STAR1 PIVOT(SUM(COL1) FOR YR IN ('Y1', 'Y2', 'Y3') WITH SUM(*) AS withalias)TMP;
Output Code:
OUT -> Teradata_03.sql
// SnowConvert Helpers Code section is omitted.
SELECT
*
FROM
STAR1 PIVOT(SUM(COL1) FOR YR IN ('Y1', 'Y2', 'Y3')
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT WITH CLAUSE NOT SUPPORTED ***/!!!
WITH SUM(*) AS withalias)TMP;
XML OUTPUT FORMAT
XML output for the PIVOT clause is not supported by Snowflake.
Example Code
Input Code:
IN -> Oracle_02.sql
SELECT * FROM (SELECT product_code, quantity FROM pivot_test)
PIVOT XML (SUM(quantity)
FOR (product_code) IN ('A','B','C'));
Output Code:
OUT -> Oracle_02.sql
// SnowConvert Helpers Code section is omitted.
SELECT * FROM
(
SELECT product_code, quantity FROM
pivot_test)
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT XML OUTPUT FORMAT NOT SUPPORTED ***/!!!
PIVOT (SUM(quantity) FOR product_code IN ( 'A', 'B', 'C'));
IN CLAUSE SUBQUERY
Subqueries for the IN clause are not supported.
Example Code
Input Code:
IN -> Oracle_03.sql
SELECT * FROM s1 PIVOT(SUM(COL1) FOR FORCOL IN (SELECT SELCOL FROM S2))DT;
Output Code:
OUT -> Oracle_03.sql
// SnowConvert Helpers Code section is omitted.
SELECT * FROM
s1 PIVOT (SUM(COL1) FOR FORCOL
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT IN CLAUSE SUBQUERY NOT SUPPORTED ***/!!! IN (SELECT SELCOL FROM
S2));
IN CLAUSE ANY SEQUENCE
This error is triggered when ANY keyword is used in the IN clause. This is currently not supported.
Example Code
Input Code:
IN -> Oracle_04.sql
SELECT * FROM (SELECT product_code, quantity FROM pivot_test)
PIVOT (SUM(quantity)
FOR product_code IN (ANY, ANY, ANY));
Output Code:
OUT -> Oracle_04.sql
// SnowConvert Helpers Code section is omitted.
SELECT * FROM (SELECT product_code, quantity FROM
pivot_test)
PIVOT (SUM(quantity)
FOR product_code
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT IN CLAUSE ANY SEQUENCE NOT SUPPORTED ***/!!!
IN (ANY, ANY, ANY));
INCLUDE/EXCLUDE NULLS
INCLUDE NULLS or EXCLUDE NULLS are not valid options for UNPIVOT clauses in Snowflake.
Example Code
Input Code:
IN -> Oracle_05.sql
SELECT * FROM db1.star1p UNPIVOT INCLUDE NULLS (column1 FOR for_column IN (col1, col2)) Tmp;
Output Code:
OUT -> Oracle_05.sql
// SnowConvert Helpers Code section is omitted.
SELECT * FROM
db1.star1p
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT INCLUDE NULLS NOT SUPPORTED ***/!!!
UNPIVOT ( column1 FOR for_column IN (
col1,
col2)) Tmp;
Recommendations
Re-write the query if possible, otherwise, no additional user actions are required.
