Re: Subfile "sort" thoughts

["Carel Teijgeler" <coteijgeler@xxxxxxxxx>]

Bob,

The only problem you are facing is the ORDER BY clause, which cannot be set up 
dynamically. 

You could take a look at SQL Packages, in which you store prepared SQL 
statements with their execution plans (best ODP, runtime, etc). Each fieldname 
from the SFL can be used as the name of the prepared SQL statement. One way to 
do it is using the Dynamic Extended SQL API (QSQPRCED). 

Or use embedded SQL with seven variotions of your statement but with a 
different ORDER BY clause each. Not pretty, though.

Just some ideas.

Regards,
Carel Teijgeler

*********** REPLY SEPARATOR  ***********

On 13-8-03 at 9:52 Bob cozzi wrote:

>I have a situation were I need to help implement a subfile sort.
>There is a Work-with panel that contains about 7 fields, all of which the 
>end-user wants to be able to sort by.
>Nothing to unusual about this so far.
>But in this situation the file is actually a dynamic file created with an SQL 
>UNION ALL statement that ends up producing about 9 >million records. 
>I was thinking about a page=size subfile and will offer the end user a 
>filtering option to weed out unwanted transactions.
>What I'm wondering is how to attack the subfile sort in this context; 9 
>million records+ doesn't lend itself well to any conventional >techniques, 
>such as sorting a multiple occurrence data structure or even dynamic/runtime 
>querying via SQL or Open Query File as >the performance will suck.
>So I'm wondering about building an SQL view out of the UNION ALL statement, 
>and then using SQL further (pre-runtime) to create an >index over each of the 
>7 fields. But I'm not sure if this is the right solution.
>
>Comments? Suggestions?