Links

MSCEWI2061

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

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 PUBLIC.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') 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('FIRSTSEMESTERINCOME', 'First', 'FIRSTSEMESTEREXPENSES', 'First', 'SECONDSEMESTERINCOME', 'Second', 'SECONDSEMESTEREXPENSES', 'Second'), Semester), '"') AS Semester,
Income,
Expenses FROM unknownTable 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') ORDER BY mykey;

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.
  • For more support, you can email us at [email protected] or 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.