Re: Sorting

[Kaynor@xxxxxxx]

Zak,
There have been lots of good ideas on this thread.  Given the run-time 
variability, I'd favor SQL or SQLRPG myself, and I certainly wouldn't copy 
records between files.  FMTDTA is fast, but not as easy to alter dynamically 
as SQL.   But...I have not seen one of my favorites, so here it is for 
completeness sake:

The setup:
1) Add a new text field called something like "sort field" to your file.   
Make it as big as the concatenated sort keys might need to be.
2) Add a permanent LF ordered only by this "sort field."

At runtime:
3) Based on the requested sort, re-calc this field for all selected records 
(i.e. update the field by concatenating together the desired sort keys in the 
desired order)
4) Run your report as you would any report over a static LF.

This would not be as efficient at run-time as the other methods, but does not 
require using SQL, SQLRPG, FMTDTA, RGZPFM, or any other additional tool for 
dynamic sorting.  I've used it for user-specified sorts on reports written in 
straight RPG.   If multiple people can try to report at the same time, you 
would want to be sure to allocate the file first, and then release it since 
you cannot share it while you are doing this.   This method turns the task 
upside down and alters the data in the sort field rather than the sort 
itself.  DB2 handles the sorting via a regular, permanent LF.

Cheers,
--Chapin Kaynor


> Date: Wed, 30 Apr 2003 15:18:55 -0400
> From: "Metz, Zak" <Zak_Metz@xxxxxx>
> Subject: RE: Sorting
> 
> I'm with ya. So creating an index and doing a RGZPFM (you got RPG on the 
> mind) would likely be faster than an INSERT INTO selecting every record 
> from the first file with multiple ORDER BY fields, and ending up with an 
> extra file. I like it! Now, can the fields in the index be substrings? Doh! 
> Doesn't look like it, but they can in an LF, so that should work. Thanks, 
> Eric, and everyone else too!
> 
>