MSCEWI1015
Pivot/Unpivot multiple function not supported.
Medium
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 is not supported by PIVOT and UNPIVOT clauses.
SELECT * FROM star1p UNPIVOT ((sales,cogs) FOR yr_qtr
IN ((Q101Sales, Q101Cogs) AS 'Q101A',
(Q201Sales, Q201Cogs) AS 'Q201A',
(Q301Sales, Q301Cogs) AS 'Q301A')) AS Tmp;
SELECT
*
FROM
-- ** MSC-ERROR - MSCEWI1015 - PIVOT/UNPIVOT MULTIPLE COLUMN NOT SUPPORTED **
-- PUBLIC.star1p UNPIVOT ((sales,cogs) FOR yr_qtr
--IN ((Q101Sales, Q101Cogs) AS 'Q101A', (Q201Sales, Q201Cogs) AS 'Q201A', (Q301Sales, Q301Cogs) AS 'Q301A')) AS Tmp
;
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, 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 result, either by providing the table definition or using a subquery with an explicit column list as input to the clause.
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;
CREATE TABLE star1 (
country VARCHAR(20),
state VARCHAR(10),
yr INTEGER,
qtr VARCHAR(3),
sales INTEGER,
cogs INTEGER
);
--SAMPLE 1
SELECT
*
FROM db1.PUBLIC.star1p UNPIVOT(column1 FOR for_column IN (col1, col2) /*** MSC-ERROR - MSCEWI1015 - PIVOT/UNPIVOT RENAME COLUMN NOT SUPPORTED ***/) Tmp;
--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
);
Multiple function is not supported for PIVOT clauses, sometimes multiple function queries could be re-written using case statements, see the following Teradata sample for more information https://docs.teradata.com/r/756LNiPSFdY~4JcCCcR5Cw/L0kKSOrOeu_68mcW3o8ilw
SELECT *
FROM STAR1 PIVOT(SUM(COL1), SUM(COL2) FOR YR IN ('Y1', 'Y2', 'Y3'))TMP;
SELECT
*
FROM
-- ** MSC-ERROR - MSCEWI1015 - PIVOT/UNPIVOT MULTIPLE FUNCTION NOT SUPPORTED **
-- PUBLIC.STAR1 PIVOT(SUM(COL1), SUM(COL2) FOR YR IN ('Y1', 'Y2', 'Y3'))TMP
;
Teradata PIVOT has an optional WITH clause, this is not allowed in snowflake's PIVOT.
SELECT *
FROM STAR1 PIVOT(SUM(COL1) FOR YR IN ('Y1', 'Y2', 'Y3') WITH SUM(*) AS withalias)TMP;
SELECT
*
FROM PUBLIC.STAR1 PIVOT(SUM(COL1) FOR YR IN ('Y1', 'Y2', 'Y3')
-- ** MSC-ERROR - MSCEWI1015 - PIVOT/UNPIVOT WITH CLAUSE NOT SUPPORTED **
-- WITH SUM(*) AS withalias )TMP;
XML output for PIVOT clause is not supported by snowflake.
SELECT * FROM (SELECT product_code, quantity FROM pivot_test)
PIVOT XML (SUM(quantity)
FOR (product_code) IN ('A','B','C'));
SELECT * FROM (SELECT product_code, quantity FROM MYDB.PUBLIC.pivot_test)
/*** MSC-ERROR - MSCEWI1015 - PIVOT/UNPIVOT XML OUTPUT FORMAT NOT SUPPORTED ***/
PIVOT (SUM(quantity) FOR product_code IN ( 'A', 'B', 'C'));
Subqueries for IN clause are not supported.
SELECT * FROM s1 PIVOT(SUM(COL1) FOR FORCOL IN (SELECT SELCOL FROM S2))DT;
SELECT
*
FROM
-- ** MSC-ERROR - MSCEWI1015 - PIVOT/UNPIVOT IN CLAUSE SUBQUERY NOT SUPPORTED **
-- PUBLIC.s1 PIVOT(SUM(COL1) FOR FORCOL IN (SELECT
--SELCOL
--FROM PUBLIC.S2))DT
;
This error is triggered when ANY keyword is used in IN clause. This is currently not supported.
SELECT * FROM (SELECT product_code, quantity FROM pivot_test)
PIVOT (SUM(quantity)
FOR product_code IN (ANY, ANY, ANY));
SELECT * FROM (SELECT product_code, quantity FROM MYDB.PUBLIC.pivot_test)
-- ** MSC-ERROR - MSCEWI1015 - PIVOT/UNPIVOT IN CLAUSE ANY SEQUENCE NOT SUPPORTED **
--PIVOT (SUM(quantity) FOR product_code IN (ANY, ANY, ANY))
;
INCLUDE NULLS or EXCLUDE NULLS are not valid options for UNPIVOT clauses in snowflake.
SELECT * FROM db1.star1p UNPIVOT INCLUDE NULLS (column1 FOR for_column IN (col1, col2)) Tmp;
SELECT
*
FROM /*** MSC-ERROR - MSCEWI1015 - PIVOT/UNPIVOT INCLUDE/EXCLUDE NULLS NOT SUPPORTED ***/
db1.PUBLIC.star1p UNPIVOT(column1 FOR for_column IN (col1, col2)) Tmp;
- Re-write the query if possible, otherwise, no additional user actions are required.
Last modified 3mo ago