Order External By
An I4GL REPORT used ORDER EXTERNAL BY x,y,z and the poster wanted to conditionally add a new leading sort column, asking whether the ORDER BY could be chosen with an IF inside the report. Answer: no, a report's ORDER BY is fixed. Options given: sort in the driving SELECT and drop the ORDER BY, clone the report, or (Mike Aubury's preferred trick) pass an extra dummy sort variable holding the new field's value when the flag is 'Y' and a constant otherwise, using ORDER BY dummy_a,x,y,z. Note EXTERNAL must be removed, since it tells 4GL the data is already sorted and skips sorting.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I have a informix 4gl (report) program which basically taken data from a temporary table which doesnot have order by clause but in the format section of the report order external by has been used. Now i need to change that order external by adding new column as the first sort and rest are same. Can i use "if condition" and seperate the order external by in tow different field sets. Example: if condition is success - sort on x,y,z otherwise a,x,y,z Required suggestions from experts. Thanks
No. Your choices are: 1. Remove the ORDER EXTERNAL BY altogether and PREPARE the select from the temp table in the calling routine with the correct ORDER BY clause so that the data is passed to the report module in the correct/desired order without any ORDER BY clause in the report - or - 2. Clone the report module so that you have two different reports one ORDER BY x, y, z and the other ORDER BY a, x, y, z BTW, the EXTERNAL clause in the ORDER BY section of a 4GL report module causes the report to assume that the data was sorted already 'EXTERNAL' to the report, presumably by an ORDER BY in the driving SELECT statement. If you are not including an appropriate ORDER BY in the SELECT then you should remove the EXTERNAL clause from the report module so that the report will re-sort the data into the correct order. If it is working now, it is completely accidental. From the 4GL manual: Specify ORDER EXTERNAL BY if the input records have already been sorted by the SELECT statement. The list of variables after the keywords ORDER EXTERNAL BY control the execution order of GROUP BY control blocks. ... Specifying EXTERNAL to instruct 4GL not to sort the records again might result in an improvement in performance. Art On Sun, Jan 11, 2009 at 11:53 PM, KRISHNAN BALASUBRAMANIAN < krishnanbalasubramanian@rediffmail.com> wrote: > Hi, > > I have a informix 4gl (report) program which basically taken data from a > temporary table which doesnot have order by clause but in the format > section > of the report order external by has been used. > > Now i need to change that order external by adding new column as the first > sort and rest are same. Can i use "if condition" and seperate the order > external by in tow different field sets. > > Example: if condition is success - sort on x,y,z otherwise a,x,y,z > > Required suggestions from experts. > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Thanks Art, I have decided to give order by in the report module in place of order external by to meet my requirement. Is it possible shall i use both in the report program..? Like: If condition is 'Yes' i.e sort on a then x,y,z then Order By a,x,y,z else order external by x,y,z Thanks in advance.
No, you'll need two copies of the report module, one for each of the order by's. Art On Tue, Jan 13, 2009 at 7:01 AM, KRISHNAN BALASUBRAMANIAN < krishnanbalasubramanian@rediffmail.com> wrote: > Thanks Art, > > I have decided to give order by in the report module in place of order > external by to meet my requirement. > > Is it possible shall i use both in the report program..? > > Like: > > If condition is 'Yes' i.e sort on a then x,y,z then > > Order By a,x,y,z > > else > > order external by x,y,z > > Thanks in advance. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
I'm not sure thats stricly true... You can pass in a dummy variable and use that as the first sort order.. When you want to order by that value - you set the 'dummy_a' to that value. When you dont - set it to some constant value (not null - will be dependant on the datatype) Then just : Order By dummy_a,x,y,z (You'll obviously need to pass the 'a' parameter in as well as the dummy_a..) Saves duplicating the report anyway... On Tuesday 13 January 2009 12:05:01 Art Kagel wrote: > No, you'll need two copies of the report module, one for each of the order > by's. > > Art > > On Tue, Jan 13, 2009 at 7:01 AM, KRISHNAN BALASUBRAMANIAN < > > krishnanbalasubramanian@rediffmail.com> wrote: > > Thanks Art, > > > > I have decided to give order by in the report module in place of order > > external by to meet my requirement. > > > > Is it possible shall i use both in the report program..? > > > > Like: > > > > If condition is 'Yes' i.e sort on a then x,y,z then > > > > Order By a,x,y,z > > > > else > > > > order external by x,y,z > > > > Thanks in advance. > > *************************************************************************** >**** > > > Forum Note: Use "Reply" to post a response in the discussion forum. -- Mike Aubury http://www.aubit.com/ Aubit Computing Ltd is registered in England and Wales, Number: 3112827 Registered Address : Clayton House,59 Piccadilly,Manchester,M1 2AQ
Mike Aubury wrote: > I'm not sure thats stricly true... > > You can pass in a dummy variable and use that as the first sort order.. > When you want to order by that value - you set the 'dummy_a' to that value. > When you dont - set it to some constant value (not null - will be dependant on > the datatype) > > Then just : > > Order By dummy_a,x,y,z > > (You'll obviously need to pass the 'a' parameter in as well as the dummy_a..) > > Saves duplicating the report anyway... But it might have unexpected consequences. :o) -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com
In this case - its an "order by" rather than an "order external by" - so I'm not sure what any unexpected consequences might be... Testing the dummy variable would let you know which type you were using - so you could omit any prints in a before/after group section if thats what you are worried about... Of course - it might just be I've not had enough coffee yet ;-) On Tuesday 13 January 2009 12:21:08 Obnoxio The Clown wrote: > But it might have unexpected consequences. :o) -- Mike Aubury http://www.aubit.com/ Aubit Computing Ltd is registered in England and Wales, Number: 3112827 Registered Address : Clayton House,59 Piccadilly,Manchester,M1 2AQ
Mike Aubury wrote: > In this case - its an "order by" rather than an "order external by" - so I'm > not sure what any unexpected consequences might be... > > Testing the dummy variable would let you know which type you were using - so > you could omit any prints in a before/after group section if thats what you > are worried about... > > Of course - it might just be I've not had enough coffee yet ;-) > > On Tuesday 13 January 2009 12:21:08 Obnoxio The Clown wrote: >> But it might have unexpected consequences. :o) I guess it's a case of you might wind up making the report much more complicated to cater for that kind of thing. But then duplicating the code isn't any easier. I guess it's just a case of having to do the extra checking. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com
Duplicating code is easy... Maintaining it is harder ;-) On Tuesday 13 January 2009 12:41:09 Obnoxio The Clown wrote: > But then duplicating the code isn't any easier. -- Mike Aubury http://www.aubit.com/ Aubit Computing Ltd is registered in England and Wales, Number: 3112827 Registered Address : Clayton House,59 Piccadilly,Manchester,M1 2AQ
The problem in my report program is: There is a program which has been written some years back with the order external by set of fields. There is a after,before groups has been used for the order external fields. Now the new requirements is it should be sorted with a new field and followed by old fields. the new field will not be used for grouping, only old fields used. Now i have a records set which is passed as the parameters to the report function the new field is coming along with that. Based on one specific flag (whenever it is 'Y') i need to use the new field in the order by clause otherwise use the old order external by only. Your advices are excellent so for and more suggestions/advices are highly appreciated. Thanks krishnan
Krishnan: Mike Aubury solve this one for you already: Instead of checking the flag INSIDE the report module, check it BEFORE the call to the report. If it is 'Y' then pass in the value of the new sort field in a new argument - call it sort_data - if it is not 'Y' then pass in the same dummy string for every row - say "NOSORT". Then add the extra argument sort_data to the report module and change the ORDER BY clause in the module from: ORDER EXTERNAL BY x, y, z to ORDER BY sort_data, x, y, z Note that I have REMOVED the EXTERNAL clause, this is because if you put that clause in there the report module will assume that the data is already sorted correctly and WILL NOT SORT IT FOR YOU! Art On Wed, Jan 14, 2009 at 5:10 AM, KRISHNAN BALASUBRAMANIAN < krishnanbalasubramanian@rediffmail.com> wrote: > The problem in my report program is: > > There is a program which has been written some years back with the order > external by set of fields. There is a after,before groups has been used for > the order external fields. > > Now the new requirements is it should be sorted with a new field and > followed > by old fields. the new field will not be used for grouping, only old fields > used. > > Now i have a records set which is passed as the parameters to the report > function the new field is coming along with that. > > Based on one specific flag (whenever it is 'Y') i need to use the new field > in > the order by clause otherwise use the old order external by only. > > Your advices are excellent so for and more suggestions/advices are highly > appreciated. > > Thanks > krishnan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.