This feature allows the DST operator to be used on more than one field within a single request and allows the ACROSS horizontal sort to be used with fields that have the DST operator applied. In requests with multiple display fields, the DST operator must be used at the lowest level of aggregation (the last display command in the request).
When used in a request against a relational database, the multiple DST syntax is optimized where the relational engine supports it.
The following request against the GGSALES data source counts the distinct number of products and categories by state.
TABLE FILE GGSALES SUM CNT.DST.PRODUCT CNT.DST.CATEGORY BY ST END
The output is:
COUNT COUNT
DISTINCT DISTINCT
State PRODUCT CATEGORY
----- -------- --------
CA 10 3
CT 10 3
FL 10 3
GA 10 3
IL 9 3
MA 10 3
MO 9 3
NY 10 3
TN 10 3
TX 9 3
WA 10 3
The following request against the GGSALES data source counts the total number of records by region, then the number of records, distinct categories, and distinct products by region and by state. The DST or CNT.DST operator can be used only with the last display command.
TABLE FILE GGSALES
COUNT CATEGORY AS 'TOTAL,COUNT'
BY REGION
SUM CNT.CATEGORY AS 'STATE,COUNT'
CNT.DST.CATEGORY CNT.DST.PRODUCT
BY REGION
BY ST
ENDThe output is:
COUNT COUNT
TOTAL STATE DISTINCT DISTINCT
Region COUNT State COUNT CATEGORY PRODUCT
------ ----- ----- ----- -------- --------
Midwest 1085 IL 362 3 9
MO 361 3 9
TX 362 3 9
Northeast 1084 CT 361 3 10
MA 360 3 10
NY 363 3 10
Southeast 1082 FL 361 3 10
GA 361 3 10
TN 360 3 10
West 1080 CA 721 3 10
WA 359 3 10
| Information Builders |