** RETRACTION ** Order By in 4GL Report

*** RETRACTION *** What I said about different orderings in a single report may not have been accurate. I withdraw what I said. *** END RETRACTION *** Christopher, I am not going to contest this any more. Please note that your report code said 'ORDER BY' but your argument says you thought you wrote 'ORDER EXTERNAL BY'. I'll put this down to a syntax omission (it isn't an error because what you wrote is syntactically correct). I'm not sure whether you are correct or not. I don't have the time to find out, and there is enough merit in what you are saying that I'm withdrawing my unconditional 'NO', pending further argument. I am sending this synopsis of our discussion to c.d.i with a retraction clearly stated at the top, and the comment that I have not been 100% convinced either way. One problem is working out a realistic scenario for a report which can be sorted multiple ways using the same printing code; I've not been able to put one together. If I had, I'd have done whatever investigation work was necessary to come up with some sort of definitive answer. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> PS: For the curious, here is a transcript of the conversation. I think I've reduced the repetition to a minimum without changing its substance. =========================================================================== Date: Tue Jan 7 09:49:24 1997 From: johnl@informix.com (Jonathan Leffler) To: batonnet@zeta.org.au, informix-list@rmy.emory.edu Subject: Re: Order By in 4GL Report >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> =========================================================================== From: Christopher Sharrer <chris@luminous-chao.com> Date: Thu, 09 Jan 1997 08:46:11 -0600 X-Informix-List-Id: <news.32400> Jonathan Leffler wrote: > You are stuck with a different report for each ORDER BY that you need. > [...] 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. =========================================================================== Date: Fri Jan 10 09:17:54 1997 From: johnl@informix.com (Jonathan Leffler) To: chris@luminous-chao.com Subject: Re: Order By in 4GL Report Christopher, Yes, you're right that you can do it that way for some (possibly many) simpler reports. I was thinking in more general terms, of a report ordered by maybe three different variables of three different types in any of 6 different orders, with BEFORE and AFTER GROUP OF block for each variable, and there, I think, my comments are still accurate. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> =========================================================================== Date: Fri, 10 Jan 1997 12:49:54 -0600 From: Christopher Sharrer <chris@luminous-chao.com> To: Jonathan Leffler <johnl@informix.com> Subject: Re: Order By in 4GL Report Jonathan Leffler wrote: > Yes, you're right that you can do it that way for some (possibly many) Jonathon, I think the same logic applies... (PLEASE IGNORE ANY BAD SYNTAX) CREATE TABLE table ( col1 CHAR(50), col2 SMALLINT, col3 DATE ) FUNCTION test() DEFINE col1, col2, col3 CHAR(32), sql_stmt CHAR(1024) LET col1 = "table.col2 USING '&&&&&'" LET col2 = "table.col1" LET col3 = "table.col3 USING 'YYMMDD'" LET sql_stmt = "SELECT table.*, ", col1, ", ", col2, ", ", col3, "FROM table", "ORDER BY ", col1, ", ", col2, ", ", col3 PREPARE sql FROM sql_stmt DECLARE curs CURSOR for sql START REPORT report2 FOREACH curs INTO record.*, sort1, sort2, sort3 OUTPUT TO REPORT report2 (record.*, sort1, sort2, sort3) END FOREACH FINISH REPORT report2 END FUNCTION REPORT report2(record.*, sort1, sort2, sort3) DEFINE record LIKE table.*, sort1, sort2, sort3 CHAR(1024) ORDER BY sort1, sort2, sort3 FORMAT ON EVERY ROW PRINT record.col1, record.col2, record.col3 BEFORE GROUP OF sort1 PRINT "Listing of all records for ", sort1. BEFORE GROUP OF sort2 PRINT "-- ", sort2, "--" BEFORE GROUP OF sort3 PRINT "- ", sort3 "-" AFTER GROUP OF sort1 PRINT "Count for ", sort1, ":", GROUP COUNT(*) SKIP TO TOP OF PAGE AFTER GROUP OF sort2 PRINT "Count for ", sort2, ":" GROUP COUNT(*) SKIP 2 LINES AFTER GROUP OF sort3 PRINT "Count for ",