RE: 4GL order by

>---------- From: ANTS[SMTP:sui@naseej.com] >Sent: Thursday, March 12, 1998 5:00 AM >We are using 4GL to write a report, we need to sort data selected based on >several ways, depending on the some flags. Our programmer used "order by" >statement in the report. What he did is to save the sorting criteria in a >variable depending on the flag. Then in the statement "Order by" he will put >the variable as one of the parameters. >The problem that no compilation errors but the resulting list was not sorted >as desired. >Any hints..... Several issues arise... First: did your programmer use ORDER BY or ORDER EXTERNAL BY in the report? The difference is that in the first case, the report code does the sorting; in the second, the code driving the report does the sorting. Secondly, in either case, the report has determines the sorting sequence at compile time, based on the ordering clauses in the report. That means that if your code says "ORDER BY Col1, Col2, Col3" the generated report logic will expect to trigger an AFTER GROUP OF Col1 (or AGO Col1) clause when the Col1 value changes between successive OUTPUT TO REPORT calls. It will trigger an AGO Col3 clause when Col3, or Col2, or Col1 changes between successive OUTPUT TO REPORT calls. Now, if you arrange for the report driver to call the report with different sequences of variables, you may be able to get the required results: Order 1: OUTPUT TO REPORT whatever(rec.col1, rec.col2, rec.col3, rec.*) Order 2: OUTPUT TO REPORT whatever(rec.col3, rec.col2, rec.col1, rec.*) However, it is (IMNSHO) quite difficult to write the BEFORE GROUP OF (BGO) and AGO clauses in such a way that the report looks good in both sequences; the actual summarization tends to be trickier. Nevertheless, you can try it. There can also be type problems if col1 is an INTEGER, Col2 is a CHAR(30) and Col3 is a DATETIME YEAR TO SECOND; you normally get around this by claiming in the report that everything is a CHAR(30) and letting the implicit conversions deal with the differences. Yours, Jonathan Leffler (jleffler@visa.com) #include <bother.ms-exchange.h>