Example: Pivot Query Truncates the Alias Name - Advanced SQL Engine - Teradata Database

For the first part of this example, the EnableEON dbscontrol flag is set to false, so the column name limit defaults to 30 characters.

Assume the table t1 is defined as:

CREATE TABLE t1(yr INTEGER, mon VARCHAR(41), sales INTEGER);

Also assume that the table t1 contains the following row:

SELECT * FROM  t1;
yr	    mon                                         sales
----   ----------------------------------          -----
2001	  aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa          200

The row contains 35 characters for the column ‘mon’.

The following pivot query results truncate the ‘mon’ column value from 35 characters to 30 characters:

SELECT * FROM t1 PIVOT(SUM(sales) FOR mon IN ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'))tmp;

YR	 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
----  -------------------------------
2001	 200

Now, assume that the EnableEON dbscontrol flag is set to true, so the column name limit defaults to 128 characters.

Also assume that table t2 is defined as follows:

CREATE TABLE t2(yr INTEGER, mon VARCHAR(131), sales INTEGER);

Assume that the table t2 contains the following row:

SELECT mon FROM t2;
mon
-------------------------------------------------------------------------------------------------------------------------------
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

The row contains 130 characters for the column ‘mon’.

The following pivot query truncates the ‘mon’ column value from 130 characters to 128 characters:

SELECT * FROM t2 PIVOT(SUM(sales) FOR mon IN ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')) tmp;

YR   
----
2001
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
----------------------------------------------------------------------------------------------------------------------------------
200