Strategy SQL Generation Engine can produce four different kinds of syntax for the GROUP BY clause. The syntax generated for ID forms is controlled by the "GROUP BY ID Attribute" VLDB property. This property is found in the Select/Insert folder of the VLDB property editor:
This property has four options:
The VLDB property's behavior will be demonstrated using the following attribute and report. The attribute uses a CASE statement to replace NULL values with -999.
Attribute:
ApplySimple("(case when #0 is null then -999 else #0 end)", )
Report:
Group by expression
The entire expression is duplicated in the GROUP BY clause.
select (case when a11.PROMOTION_ID is null then -999 else a11.PROMOTION_ID end) CustCol_9,
sum((a11.QTY_SOLD * ((a11.UNIT_PRICE - a11.DISCOUNT) - a11.UNIT_COST))) WJXBFS1
from ORDER_DETAIL a11
group by (case when a11.PROMOTION_ID is null then -999 else a11.PROMOTION_ID end)
Group by alias
The column alias assigned to the expression appears in the GROUP BY clause.
select (case when a11.PROMOTION_ID is null then -999 else a11.PROMOTION_ID end) CustCol_9,
sum((a11.QTY_SOLD * ((a11.UNIT_PRICE - a11.DISCOUNT) - a11.UNIT_COST))) WJXBFS1
from ORDER_DETAIL a11
group by CustCol_9
Group by column
Any columns used in the expression will be included in the GROUP BY clause. If the expression uses more than one column, all of them will be used for grouping.
select (case when a11.PROMOTION_ID is null then -999 else a11.PROMOTION_ID end) CustCol_9,
sum((a11.QTY_SOLD * ((a11.UNIT_PRICE - a11.DISCOUNT) - a11.UNIT_COST))) WJXBFS1
from ORDER_DETAIL a11
group by a11.PROMOTION_ID
Note: This option should be used only if there is a one-to-one match between the underlying column values and the expression's results. Substring expressions, for instance, could return the same result for several distinct column values (many-to-one). In such scenarios, grouping by the column values will aggregate metric values differently and return "duplicate rows" to Strategy in the result set (where duplicate rows are rows that have the same primary key values). If this is part of the modeling strategy for a particular project, the "Group by column" selection for this VLDB property is not recommended.
Group by position
The GROUP BY clause will contain numbers corresponding to the expressions in the SELECT clause. The first selected expression is 1, the second 2 and so on.
select (case when a11.PROMOTION_ID is null then -999 else a11.PROMOTION_ID end) CustCol_9,
sum((a11.QTY_SOLD * ((a11.UNIT_PRICE - a11.DISCOUNT) - a11.UNIT_COST))) WJXBFS1
from ORDER_DETAIL a11
group by 1
Note: Strategy occasionally generates a GROUP BY clause in a SQL statement that contains only aggregate expressions and no attribute IDs. Non-aggregatable metrics using beginning or ending lookup dimensionality do this to obtain, for instance, the last day within a month or quarter. "Group by position" is not valid for these cases because the following is illegal.
select max(a11.DAY_ID) WJXBFS1
into #ZZOP00
from LU_MONTH a11
group by 1
"1" corresponds to the max() expression, and SQL does not allow grouping by an aggregate expression. If this issue is encountered, one of the other options must be used, depending on the database platform and report structure.
