SYMPTOM:
Users notice that when generating SQL for a report containing Custom Groups, the following error is thrown:
Execute Query failed.
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. Duplication of column COLUMN_NAME in a table, derived table, view, macro or trigger. Connection String: DSN=DSN_NAME;UID=USER_NAME;. SQL Statement: create volatile table VOLATILE_TABLE NAME 0, no fallback, no log
This occurs for projects which use Teradata for the data warehouse and reports which use Global optimization Level 4.
CAUSE:
This issue is caused when two metrics used by the report are using the same Column Alias in Strategy. When the SQL Engine creates the column name for the temporary table, it assigns a number to the end of each column to distinguish (e.g COLUMN1 and COLUMN2). Due to a 30-character limit in Teradata for column names, the number for long names becomes truncated resulting in duplicate column names in the SQL.
This is a known issue in Strategy 9.4.x.
ACTION:
Upgrade to Strategy 10. This has been addressed by the last valid character in the 30 character limit being changed to the necessary identifying number to prevent duplicate column names.
WORKAROUND:
Modify the column aliases to be different for each metric used in the report.
Note:
After Strategy 2020, with Data Engine version Strategy 2020, the Unified Quoting feature will retain special characters, e.g., white space, in the metric alias. This may increase the number of characters truncated, and increase the chance that two column aliases become duplicated. If the two metric aliases are in different cases, for example, one is "Column," and the other one is "column," the Engine will not treat them as the same, and the Engine will not assign a number to the end of each column to distinguish. However, if the database is case insensitive, "Column" and "column" will be treated as duplicates, and the database will return an error. This does not indicate that the truncating or distinguishing workflow has been broken. For example, if the warehouse is on a Teradata Database, with the Unified Quoting feature, the Engine no longer converts the alias to the upper case before sending them to the warehouse, and the report may fail if two column alias are named "Column" and "column."
Workaround:
There are two ways to avoid this issue:
