SYMPTOM
A report is executed against a Teradata data warehouse, and the following error returns.
Error: SQL Generation Complete
QueryEngine encountered error: Execute Query failed.
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. Syntax error: expected something between ')' and ','. ...
Error in Process method of Component: QueryEngineServer, Project Strategy Tutorial, Job 96, Error Code= -2147212544.
The report contains a metric which uses a formula-based transformation: YEAR_ID - 1 instead of LY_YEAR_ID.
The SQL for the report replicates the transformation expression for each attribute ID in the WHERE clause.
select a11.CATEGORY_ID CATEGORY_ID,
max(a12.CATEGORY_DESC) CATEGORY_DESC0,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from YR_CATEGORY_SLS a11
join LU_CATEGORY a12
on (a11.CATEGORY_ID = a12.CATEGORY_ID)
where a11.YEAR_ID in ((2006 - 1), (2007 - 1))
group by a11.CATEGORY_ID
If the transformation expressions are removed, the SQL executes properly against Teradata.
CAUSE
The transformation formulas in the WHERE clause are due to the Transformation Formula Optimization, introduced into Strategy SQL Generation Engine. Consult the following Strategy Knowledgebase document for more information on this feature.
KB31136: What is the Transformation Formula Optimization in Strategy SQL Generation Engine?
In the example report, the Transformation Formula Optimization avoids joining to the transformation table by writing the transformation formula directly against each element ID to be transformed. Without the transformation, the report would show Category-level revenue for 2006 and 2007. With the transformation, the result is for the previous years' revenue: 2005 and 2006.
Teradata does not accept the syntax generated in the WHERE clause when the Transformation Formula Optimization is enabled. (Many other database platforms permit the syntax.)
ACTION
For Teradata warehouses, disable the Transformation Formula Optimization VLDB property. Setting the option in the database instance's VLDB properties will apply to any reports using that database instance. Alternately, the setting may be made in individual reports.
After changing the property to the option "Always join the transformation table to perform transformation," the SQL reverts to the normal transformation strategy and executes against Teradata without issue.
select a11.CATEGORY_ID CATEGORY_ID,
max(a13.CATEGORY_DESC) CATEGORY_DESC0,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from YR_CATEGORY_SLS a11
join LU_YEAR a12
on (a11.YEAR_ID = (a12.YEAR_ID - 1))
join LU_CATEGORY a13
on (a11.CATEGORY_ID = a13.CATEGORY_ID)
where a12.YEAR_ID in (2006, 2007)
group by a11.CATEGORY_ID
