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;
Result:
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;
Result:
YR ---- 2001 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ---------------------------------------------------------------------------------------------------------------------------------- 200