Re: Order By in 4GL Report

Posted in 1997

Christopher Sharrer — Thu, 09 Jan 1997 08:46:11 -0600 — source: Informix-list mailing list archive (1991-1998)

------------656049A446731 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=us-ascii Jonathan Leffler wrote: > > }From: batonnet@zeta.org.au (Bryan Tonnet) > }Date: 7 Jan 1997 18:26:17 +1100 > }X-Informix-List-Id: <news.32288> > } > }Anyone know of a decent (decent = not recopying the entire report block) way > }to include a dynamic "order by" in a 4GL report? > } > }That is, if my report driver is; > } > } LET sql_stmt_l = "SELECT a,b,c FROM z" > } > } IF something THEN > } LET sql_stmt_l = sql_stmt_l clipped, " ORDER BY a" > } ELSE > } LET sql_stmt_l = sql_stmt_l clipped, " ORDER BY b" > } END IF > } > }; can the report be told of a variable "order by", or am I stuck with > }a different report block for each possibility? > > You are stuck with a different report for each ORDER BY that you need. > > If you ever get to look at the generated C code, then after you've > recovered from the heart-attack, you'll begin to see why dynamic ORDER BY > operations would be rather hard to support. The whole of a report is a > complex state machine implemented with 'computed gotos' (Fortran-speak, but > implemented in C), and the sequence in which the various parts of a report > (page header, before group, after group, on every row, etc) are triggered > depends on the ORDER BY clause, and so to handle multiple order by > sequences, you'd have to have (at a minimum) different tables of jumps for > the different order by clauses. I'd hate to have to prototype it, even for > a simple one-pass report with only one level of order by and no aggregates > or group aggregates, let alone try to handle all possible cases. > > Yours, > Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> Sorry, Informix guy, but you're wrong on this one. Just pass the column you want to sort by as a variable to the report, and then order by that variable. Using the example above: PREPARE sql FROM sql_stmt1 DECLARE cur CURSOR FOR sql START REPORT report FOREACH cur INTO a, b, c IF something THEN LET sort_var = a ELSE LET sort_var=b END IF OUTPUT TO REPORT report(a, b, c, sort_var) END FOREACH FINISH REPORT report REPORT report(a, b, c, sort_var) DEFINE a, b, c, sort_var ORDER BY sort_var FORMAT AFTER GROUP OF sort_var END REPORT -- Christopher Sharrer Luminous Chao, Inc. ------------656049A446731 Content-Transfer-Encoding: 7bit Content-Type: text/html; charset=us-ascii <HTML><BODY> <DT>Jonathan Leffler wrote: >  > }From: batonnet@zeta.org.au (Bryan Tonnet) > }Date: 7 Jan 1997 18:26:17 +1100 > }X-Informix-List-Id: <news.32288> > } > }Anyone know of a decent (decent = not recopying the entire report block) way > }to include a dynamic "order by" in a 4GL report? > } > }That is, if my report driver is; > } > }    LET sql_stmt_l = "SELECT a,b,c FROM z" > } > }    IF something THEN > }        LET sql_stmt_l = sql_stmt_l clipped, " ORDER BY a" > }    ELSE > }        LET sql_stmt_l = sql_stmt_l clipped, " ORDER BY b" > }    END IF > } > }; can the report be told of a variable "order by", or am I stuck with > }a different report block for each possibility? >  > You are stuck with a different report for each ORDER BY that you need. >  > If you ever get to look at the generated C code, then after you've > recovered from the heart-attack, you'll begin to see why dynamic ORDER BY > operations would be rather hard to support.  The whole of a report is a > complex state machine implemented with 'computed gotos' (Fortran-speak, but > implemented in C), and the sequence in which the various parts of a report > (page header, before group, after group, on every row, etc) are triggered > depends on the ORDER BY clause, and so to handle multiple order by > sequences, you'd have to have (at a minimum) different tables of jumps for > the different order by clauses.  I'd hate to have to prototype it, even for > a simple one-pass report with only one level of order by and no aggregates > or group aggregates, let alone try to handle all possible cases. >  > Yours, > Jonathan Leffler (johnl@informix.com) #include <disclaimer.h></DT> <DT> Sorry, Informix guy, but you're wrong on this one.  Just pass the column you</DT> <DT>want to sort by as a variable to the report, and then order by that variable.</DT> <DT> </DT> <DT>Using the example above:</DT> <DT> </DT> <DT>PREPARE sql FROM sql_stmt1</DT> <DT>DECLARE cur CURSOR FOR sql</DT> <DT> </DT> <DT>START REPORT report</DT> <DT> </DT> <DT>FOREACH cur INTO a, b, c</DT> <DT>    IF something THEN</DT> <DT>        LET sort_var = a</DT> <DT>    ELSE</DT> <DT>        LET sort_var=b</DT> <DT>    END IF</DT> <DT> </DT> <DT>    OUTPUT TO REPORT report(a, b, c, sort_var)</DT> <DT>END FOREACH</DT> <DT> </DT> <DT>FINISH REPORT report</DT> <DT> </DT> <DT>REPORT report(a, b, c, sort_var)</DT> <DT>    DEFINE a, b, c, sort_var</DT> <DT> </DT> <DT>    ORDER BY sort_var</DT> <DT> </DT> <DT>   FORMAT</DT> <DT>        AFTER GROUP OF sort_var</DT> <DT>END REPORT</DT> <DT> </DT> <DT>--  Christopher Sharrer Luminous Chao, Inc.  </DT> </BODY> </HTML> ------------656049A446731--