RE: ORDER BY within REPORT routine

---------- Ian Clark wrote: > What I would like to know is in what circumstances should I consider > using the ORDER BY statement within the REPORT routine? Would the > report be produced any quicker, is it more efficient and is it any > easier? Peter Lancashire wrote: > I think it is there mainly for use with ASCII file input. I'd guess the > engine would be better just about every time. I haven't tested it, though. Richard Stanford (richards@herald.net) wrote: >The big difference is that the report can not take advantage of any >indexes that the engine might. However, performing sorts within the >report does take a load off the engine, which is often preferable. Jonathan Leffler (jleffler@visa.com) writes: The ORDER BY clause in an I4GL report is there for the occasions when it is not feasible, for whatever reason, to ensure that the data is presented to the report in the sequence in which it should be printed. One of the more complex cases I encountered was where the report was meant to produce output similar to a spreadsheet, with a total sales volume in the top portion of the report, the total sales value in the middle, and the revenue per unit volume at the bottom. Each section was broken down by product group and sales month, so there were three sections of the report. It was easy enough to produce the volumn, value, and value/volume figures for 3 cells in different parts of the report in a single FETCH, but it would have been prohibitively expensive to compute the three sets of values separately. Consequently, the report I wrote used an ORDER BY clause; the OUTPUT TO REPORT was called three times in succession, once to place the volume, once to place the value, and once to place the value/volume figure. Of course, handling the units and the formatting was another bag of worms.. Anyway, that report would have been very much slower without having the ORDER BY facility. Richard comments that an ORDER BY statement in a report takes the load off the engine; NOT SO! The report becomes a two-pass report. During pass 1, the data sent to the report by an OUTPUT TO REPORT is loaded into a temporary table in the database. During pass 2 (triggered by the FINISH REPORT statement), the data is read back from the engine in sorted order. You can also create a two-pass report without using ORDER BY if you have a GROUP PERCENT aggregate or if you print any overall aggregate (rather than a group aggregate) in any section other than ON LAST ROW. Yours, Jonathan Leffler (jleffler@visa.com) #include <bother.ms-exchange.h>