I have the following query which gives me a very nice hold file with multiple sums for Dollars for Yrly, Qtrly, Mntly, a the selected day and order detail for that day. But I noticed that the SQL generated, does not have SUMS or GROUP BYs in it (see below). Which means I'm bringing a years worth of detail back to the report server, about 200k's worth, should only be around 5k to 10k rows of daily order detail.

The &PRIMARY_SORT could be Warehouse, GL Class, Item Class that kind of thing. Again, the code you see here, works, it just seems like if I could push the sums into the DBMS it would run a lot faster.

Any ideas?

 TABLE FILE SALESHISTORY

-* Yearly
SUM
ORDERED_DOLLARS   AS ORDERED_DOLLARS_Y
SHIPPED_DOLLARS   AS SHIPPED_DOLLARS_Y
CANCELD_DOLLARS   AS CANCELD_DOLLARS_Y
BY &PRIMARY_SORT_FIELD
BY PERYR

-* Quarterly
SUM
ORDERED_DOLLARS   AS ORDERED_DOLLARS_Q
SHIPPED_DOLLARS   AS SHIPPED_DOLLARS_Q
CANCELD_DOLLARS   AS CANCELD_DOLLARS_Q
BY &PRIMARY_SORT_FIELD
BY FISCAL_YR
BY FISCAL_QTR

-* Monthly
SUM
ORDERED_DOLLARS   AS ORDERED_DOLLARS_P
SHIPPED_DOLLARS   AS SHIPPED_DOLLARS_P
CANCELD_DOLLARS   AS CANCELD_DOLLARS_P
BY &PRIMARY_SORT_FIELD
BY FISCAL_YR
BY FISCAL_QTR
BY FISCAL_PER

-* Daily
SUM
ORDERED_DOLLARS   AS ORDERED_DOLLARS_D
SHIPPED_DOLLARS   AS SHIPPED_DOLLARS_D
CANCELD_DOLLARS   AS CANCELD_DOLLARS_D
ALPHA_NAME
ADDRESS NUMBER
ORDER_NUMBER
SHIPPED_TO
CATEGORY_CODE_20        
SUB_SECTION             
SALES_CATALOG_SECTION   

BY &PRIMARY_SORT_FIELD
BY FISCAL_YR
BY FISCAL_QTR
BY FISCAL_PER
BY ACTUAL_SHIP_DATE

WHERE ORDER_NUMBER            EQ '&ORDER_NUMBER';
WHERE CATEGORY_CODE_20        EQ '&CATEGORY_CODE_20';
WHERE SUB_SECTION             EQ '&SUB_SECTION';
WHERE ADDRESS_NUMBER          EQ  &ADDRESS_NUMBER;
WHERE SALES_CATALOG_SECTION   EQ '&SALES_CATALOG_SECTION';
WHERE FISCAL_YR               EQ  2011;
WHERE ACTUAL_SHIP_DATE        LE  DT('20110602 00:00:00');


ON TABLE HOLD 
END 

-* SQL TRACE

 SELECT T1."ORDERED_DOLLARS",T1."SHIPPED_DOLLARS",T1."CANCELD_DOLLARS",T1."CXCRRD",
 T1."SDSRP2",T1."E1ITMCLSSDESC",T1."SDADDJ",T1."SDAN8",
 T1."SDLITM",T1."MCRP20",T1."MCDL01",T1."SDUOM1",T1."SDDOCO",
 T1."IBMCU",T1."SDDSC1",T1."SDSRP1",T1."SDUOM4",T1."ABALPH",
 T1."E1GLCODEDESC",T1."CLYR",T1."CLPN",T1."CLFISQTR" FROM
 Sales.dbo.SalesHistory T1 WHERE (T1."ACTUAL_SHIP_DATE" <= '20110718
 00:00:00.000') AND (T1."FISCAL_YR" = 2011);

 

This message has been edited. Last edited by: Kerry,

Don, can you add this to your trace statements?

SET TRACEON=SQLAGGR//CLIENT

It would tell you why the AGGREGATION was not done.

I have not used multi-verb requests directly against the database so I'm not sure how iWay handles the SQL translation.

I can see from your statements that the *Yearly* SUM uses entirely different groupings from the ones used in the others (WAREHOUSE and PERYR are exclusive to this one); maybe this is impacting the ability to write one single SQL statement that would perform all those disparate aggregation/groupings in a single pass.

Also, your *Daily* piece is attempting to SUM fields that are not numeric (ALPHA_NAME, ADDRESS_NUMBER, etc.) and while this works perfectly with FOCUS files (the last instance of each value is retrieved), iWay is much pickier when translating this to SQL.

Try prefixing each of those non-numeric fields with MAX. to increase your chances of a proper SQL aggregation.

Obviously you know why your first SUM is so different from the rest, but if you could (at least for the sake of testing) change your Yearly piece to something like this:

SUM
ORDERED_DOLLARS   AS ORDERED_DOLLARS_Y
SHIPPED_DOLLARS   AS SHIPPED_DOLLARS_Y
CANCELD_DOLLARS   AS CANCELD_DOLLARS_Y
BY &PRIMARY_SORT_FIELD
BY FISCAL_YR

It would align better with the other segments and maybe iWay would be more forgiving this time.

njsden,

Arggggg! such a simple fix and I know better.

Thanks for taking the time,

Don