SSC-EWI-0015

Pivot/Unpivot multiple function not supported.

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.

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 ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT MULTIPLE COLUMN NOT SUPPORTED ***/!!!
           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 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 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":1, "minor":0},"attributes":{"component":"teradata"}}'
;

--SAMPLE 1
SELECT
	* FROM
	db1.star1p UNPIVOT(column1 FOR for_column IN (col1, col2) !!!RESOLVE EWI!!! /*** SSC-EWI-0015 - 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

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:

IN -> Teradata_02.sql
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