SYMPTOM:
When executing a report that contains a non-aggregatable metric which uses a compound attribute in the dimensionality, the following error is returned:
"Error: SQL Generateion Complete. QueryEngine encountered error: Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [S1000:-3608: on HSTMT][NCR][ODBC Teradata Driver][Teradata RDBMS] Not enough values in a subquery….Error Code=-2147212544"
CAUSE:
The use of compound attributes in a non-aggregatable metric is not supported in Strategy Developer 9.x. When generating the SQL for the report containing such a metric, the subquery will only perform the max or min on the first ID in the attribute ID form.
Example:
select a11. Column1 Attribute1,
sum(a11.QUANTITY) WJXBFS1
into #ZZMD01
from Table1 a11
where (a11. Column1 in ('Northeast')
a11. Column2 = '112'
and a11. Column3 in ('34'))
and ((a11. Column2,
a11. Column3)
in (select max(c11. Column2)
from Table1 c11
where (c11. Column2 = `112`
and c11. Column3 in (`34`))
group by c11. Column2)))
group by a11.Column1
WORKAROUND:
The compound attribute can be modified to concatenate the ID forms together. Therefore, if Attribute1 was previously defined as the following:
Attribute1:
ID:
ID1: Column1
ID2: Column2
It could be redefined as the following:
Attribute1:
ID: Column1 + Column2
NOTE: If the columns are number fields, then they can be converted to strings and then concatenated or added together.
In order to add the numbers together, all the values in the column being added to the preceding column must be of the same length (i.e. the same number of digits for all values). The preceding column should be multiplied by a factor of 10 depending on how many digits are included in the column being added.
For example, if all of the values in Column2 are one digit, Column1 should be multiplied by 10 before adding Column2. If all the values in Column2 are two digits, Column1 should be multiplied by 100 before adding Column2:
Example:
Column1 value: 112
Column2 value: 34
ID: (Column1 * 100) + Column2
The value will be (112 * 100 + 34) = 11234
The corresponding SQL for this report will be as follows:
select a11. Column 1 Attribute1,
sum(a11.QUANTITY) WJXBFS1
into #ZZMD01
from Table1 a11
where (a11.Column1 in ('Northeast')
and a11.Column2 * 100 + a11.Column3 = '11234'
group by a11. Column1
ACTION:
Contact Strategy Technical Support for an update on the status of this issue.
