The ordering the user wants is over fields in multiple files.
With this said, assuming each of these files are relative to eachother (i.e.
have keys that tie them all together) I think a simple SQL JOIN is in order,
and then you can use an ORDER BY clause to get what the temp file was
providing. If the JOIN is too messy you could always create an SQL VIEW to
accomplish a similar goal (note I am not a prolific user of VIEWs).
Sample SQL JOIN statement (untested for syntax)
SELECT a.fld1, b.fld5, c.fld3 FROM table1 AS a
LEFT JOIN table2 as b on a.key = b.key
LEFT JOIN table3 as c on a.key = c.key
WHERE a.fld5 = 'something'
ORDER BY dynamic_fld
Here is some archive material for you:
http://archive.midrange.com/rpg400-l/200507/msg00698.html
BTW, the user is wanting to sort the data one of three ways.
Perfect fit for dynamic SQL!
Also, I was always thought, you'd get better execute time when you select
the data you need from large tables into a temp table.
That's pretty much what an SQL result set is, a temp file. I would imagine
that this type of scenario would always be faster using SQL vs. your
approach, but that is pure conjecture on my part.
HTH,
Aaron Bartell
http://mowyourlawn.com
-----Original Message-----
From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx] On
Behalf Of Michael_Schutte@xxxxxxxxxxxx
Sent: Wednesday, October 31, 2007 8:44 AM
To: Web Enabling the AS400 / iSeries
Subject: Re: [WEB400] Faster HTML Output.
The reason for using the temp table is a pretty big one. We do not do
logical DDS joins in our shop. The ordering the user wants is over fields
in multiple files. So as I stated, I'm creating a temp table, then
creating indexes over that empty temp table, then executing the select into
that temp table. BTW, the user is wanting to sort the data one of three
ways.
If you can read through a cursor faster than regular I/O please inform me,
cuz as I see it... I still have to loop the same amount of times.
Also, I was always thought, you'd get better execute time when you select
the data you need from large tables into a temp table.
Michael Schutte
Admin Professional
Bob Evans Farms, Inc.
3776 South High Street, Columbus, OH 43207
Phone: 614/492-7419 Fax: 614/409-2017
Visit us on the web! www.bobevans.com
midrange.com
