Mobilize.NetForumsBlogDocumentation Home
Search…
⌃K
Links

MSCEWI1015

Pivot/Unpivot multiple function not supported.

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 column is not supported by PIVOT and UNPIVOT clauses.

Example Code

Input Code:

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:

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
;

RENAME COLUMN

Renaming columns with aliases is not supported in snowflake PIVOT and 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.

Example Code

Input Code:

--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(COL1) AS SC1, SUM(COL2) AS SC2 FOR YR
IN ((SC1, 'Y1'),
(SC2, 'Y2'),
(SC3, 'Y3')) WITH SUM(*) AS withalias) TMP;

Output Code:

--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
-- ** MSC-ERROR - MSCEWI1015 - PIVOT/UNPIVOT MULTIPLE FUNCTION NOT SUPPORTED **
-- PUBLIC.STAR1 PIVOT(SUM(COL1) AS SC1, SUM(COL2) AS SC2 FOR YR IN ((SC1, 'Y1'), (SC2, 'Y2'), (SC3, 'Y3')) WITH SUM(*) AS withalias)TMP
;
​

​

MULTIPLE FUNCTION

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​

Example Code

Input Code:

SELECT *
FROM STAR1 PIVOT(SUM(COL1), SUM(COL2) FOR YR IN ('Y1', 'Y2', 'Y3'))TMP;

Output Code:

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
;

WITH CLAUSE

Teradata PIVOT has an optional WITH clause, this is not allowed in snowflake's PIVOT.

Example Code

Input Code:

SELECT *
FROM STAR1 PIVOT(SUM(COL1) FOR YR IN ('Y1', 'Y2', 'Y3') WITH SUM(*) AS withalias)TMP;

Output Code:

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 FORMAT

XML output for PIVOT clause is not supported by snowflake.

Example Code

Input Code:

SELECT * FROM (SELECT product_code, quantity FROM pivot_test)
PIVOT XML (SUM(quantity)
FOR (product_code) IN ('A','B','C'));

Output Code:

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'));

IN CLAUSE SUBQUERY

Subqueries for IN clause are not supported.

Example Code

Input Code:

SELECT * FROM s1 PIVOT(SUM(COL1) FOR FORCOL IN (SELECT SELCOL FROM S2))DT;

Output Code:

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
;

IN CLAUSE ANY SEQUENCE

This error is triggered when ANY keyword is used in IN clause. This is currently not supported.

Example Code

Input Code:

SELECT * FROM (SELECT product_code, quantity FROM pivot_test)
PIVOT (SUM(quantity)
FOR product_code IN (ANY, ANY, ANY));

Output Code:

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/EXCLUDE NULLS

INCLUDE NULLS or EXCLUDE NULLS are not valid options for UNPIVOT clauses in snowflake.

Example Code

Input Code:

SELECT * FROM db1.star1p UNPIVOT INCLUDE NULLS (column1 FOR for_column IN (col1, col2)) Tmp;

Output Code:

SELECT
*
FROM /*** MSC-ERROR - MSCEWI1015 - PIVOT/UNPIVOT INCLUDE/EXCLUDE NULLS NOT SUPPORTED ***/
db1.PUBLIC.star1p UNPIVOT(column1 FOR for_column IN (col1, col2)) Tmp;

Recommendations

  • Re-write the query if possible, otherwise, no additional user actions are required.
  • For more support, you can email us at [email protected] post a message to our forums. If you have a contract for support with Mobilize.Net, reach out to your sales engineer and they can direct your support needs.
Previous
MSCEWI1014
Next
MSCEWI1016
Last modified 1yr ago