Re: User-defined sorting in Ace

peter_raeburn@my-deja.com wrote: > > In article <381DB22B.8001A42A@bloomberg.net>, > kagel@bloomberg.net wrote: > > peter_raeburn@my-deja.com wrote: > > > > > > Using ACE in Informix 7.2, I'm trying to let the > > > user define what sort order to use. I have four > > > fields that they can use, but no restriction as > > > to which order - it's being controlled by the > > > shell script that will pass parameters to the > > > report. For example, one run could be ORDER BY > > > fielda, fieldb, fieldc, fieldd, the next run > > > could be ORDER BY fieldc, fielda, fieldb, fieldd > > > > > > However, I don't seem to be able to get the > > > report to compile if I include parameters in the > > > ORDER BY clause. Am I wasting my time trying to > > > resolve this, or is there some way (no matter how > > > obscure) to achieve what I want. BTW, ACE has to > > > be the dev tool to use. > > > > Have the shell script select from among the 24 possible reports each > > compiled with a different ORDER BY clause or have the script write > and > > compile the ACE script on the fly (OK dumb idea but it'll work!). > > > > Truth is as soon as this report is in release someone will ask for > the > > formatting of the a,b,c,d ordered report to differ from the c,d,a,b > > report. It never fails. This way you are ready and even have the > > infrastructure to support multiple formats for one sort order in > place. > > > > Art S. Kagel > > > Thanks for that - I'd been coming to the same sort of conclusions > myself, but wanted to know if anyone had a neat solution. Other > alternatives included using a temporary table, or flat file that could > be sorted externally. The "compile on the fly" option has some > attractions; maintaining 24 separate sets of source doesn't! I had hesitated to mention the 'on the fly' option because you might just end up with 24 ACE programs anyway and would be stuck compiling every time when the program might already be there. Of course you might just find that the client really only ever uses 6 of the possible sort orders so I posted that one anyway. Hmm what if you determined if the compiled program is already there and use it or generate/compile it if not! Then you would build just those that the user uses and reuse them. Ooo I'm just so clever! ;-) > It seems odd that the SELECT statement can take runtime parameters, but > the ORDER BY can't. Decisions, decisions... > Of course, if the user had 4gl..... Yup. Art S. Kagel