This is a deprecated version of the SnowConvert documentation, please visit the official site .
Severity
Medium
Description
SnowConvert supports and transforms the 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.
