MSCEWI2061

TD_UNPIVOT transformation requires column information that could not be found, columns missing in result

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

Severity

Medium

Description

SnowConvert supports and transforms the TD_UNPIVOT function, which can be used to represent columns from a table as rows.

However, this transformation requires information about the table/tables columns to work, more specifically the names of the columns. When this information is not present the transformation may be left in an incomplete state where columns are missing from the result, this EWI is generated in these cases.

Example code

Input Code:

CREATE TABLE unpivotTable  (
	myKey INTEGER NOT NULL PRIMARY KEY,
	firstSemesterIncome DECIMAL(10,2),
	secondSemesterIncome DECIMAL(10,2),
	firstSemesterExpenses DECIMAL(10,2),
	secondSemesterExpenses DECIMAL(10,2)
);

SELECT * FROM
 TD_UNPIVOT(
 	ON unpivotTable 
 	USING
 	VALUE_COLUMNS('Income', 'Expenses')
 	UNPIVOT_COLUMN('Semester')
 	COLUMN_LIST('firstSemesterIncome, firstSemesterExpenses', 'secondSemesterIncome, secondSemesterExpenses')
 	COLUMN_ALIAS_LIST('First', 'Second')
 )X ORDER BY mykey;

SELECT * FROM
 TD_UNPIVOT(
 	ON unknownTable
 	USING
 	VALUE_COLUMNS('MonthIncome')
 	UNPIVOT_COLUMN('Months')
 	COLUMN_LIST('januaryIncome', 'februaryIncome', 'marchIncome', 'aprilIncome')
 	COLUMN_ALIAS_LIST('January', 'February', 'March', 'April')
 )X ORDER BY yearKey;

Output Code:

CREATE TABLE unpivotTable (
	myKey INTEGER NOT NULL PRIMARY KEY,
	firstSemesterIncome DECIMAL(10,2),
	secondSemesterIncome DECIMAL(10,2),
	firstSemesterExpenses DECIMAL(10,2),
	secondSemesterExpenses DECIMAL(10,2)
);
SELECT
* FROM
(
	SELECT
		myKey,
		TRIM(GET_IGNORE_CASE(OBJECT_CONSTRUCT('FIRSTSEMESTERINCOME', 'First', 'FIRSTSEMESTEREXPENSES', 'First', 'SECONDSEMESTERINCOME', 'Second', 'SECONDSEMESTEREXPENSES', 'Second'), Semester), '"') AS Semester,
		Income,
		Expenses
	FROM
		unpivotTable UNPIVOT(Income FOR Semester IN (
			firstSemesterIncome,
			secondSemesterIncome
		)) UNPIVOT(Expenses FOR Semester1 IN (
			firstSemesterExpenses,
			secondSemesterExpenses
		))
	WHERE
		Semester = 'FIRSTSEMESTERINCOME'
		AND Semester1 = 'FIRSTSEMESTEREXPENSES'
		OR Semester = 'SECONDSEMESTERINCOME'
		AND Semester1 = 'SECONDSEMESTEREXPENSES'
) X ORDER BY mykey;

SELECT
* FROM
	--** MSC-ERROR - MSCEWI2061 - TD_UNPIVOT TRANSFORMATION REQUIRES COLUMN INFORMATION THAT COULD NOT BE FOUND, COLUMNS MISSING IN RESULT **
(
		SELECT
			TRIM(GET_IGNORE_CASE(OBJECT_CONSTRUCT('JANUARYINCOME', 'January', 'FEBRUARYINCOME', 'February', 'MARCHINCOME', 'March', 'APRILINCOME', 'April'), Months), '"') AS Months,
			MonthIncome
		FROM
			unknownTable UNPIVOT(MonthIncome FOR Months IN (
			januaryIncome,
			februaryIncome,
			marchIncome,
			aprilIncome
				))
) X ORDER BY yearKey;

Recommendations

• There are two ways of supplying the information about columns to the conversion tool: put the table specification in the same file as the TD_UNPIVOT call or specify a column list in the SELECT query of the ON expression instead of SELECT * or the table name.

• This issue can be safely ignored if ALL the columns from the input table/tables are being unpivoted, otherwise, the result will have missing columns.

• If you need more support, you can email us at [email protected]