Re: ORDER BY within REPORT routine

Ian Clark wrote: > > Hi All, > > When writing reports within 4GL programs I usually end up with my > usual SELECT...FOREACH...OUTPUT TO REPORT scenario. > > The ordering is usually done with the initial SELECT statement. > > 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? There are two purposes to the ORDER BY in the report module. 1) If you know that the SELECT has an appropriate ORDER BY you can include an ORDER BY .... EXTERNAL statement that tells the 4GL compiler to generate more efficient GROUP code assuming that the data is coming in sorted. 2) You can support several different reports/sort orders from the same query by including appropriate re-sort options in the form of ORDER BY sections in the various report modules. I have done this to support multiple reports on the same dataset by making the cursor a SCROLL CURSOR, use a FOR LOOP and FETCH ABSOLUTEs, produce a default report then allow the user to select another report or format or sort order, reset the relative recno I will FETCH and go back into the loop to refetch the data from the SCROLL CURSOR's temp table without causing the engine to do much additional work. Art S. Kagel