Re: Faster HTML Output.

So...

Do any of you see a problem with an order by structured like so...

ORDER BY
ARCO,
CASE WHEN :ASSORT = '0' THEN ARRC04
WHEN :ASSORT = '1' THEN ARRC02
WHEN :ASSORT = '2' THEN ARRC04
END,
CASE WHEN :ASSORT = '0' THEN ARRC05
WHEN :ASSORT = '1' THEN ARRC03
WHEN :ASSORT = '2' THEN ARRC05
END,
CASE WHEN :ASSORT = '0' THEN ARRC02
WHEN :ASSORT = '1' THEN ARRC04
WHEN :ASSORT = '2' THEN ARJOB
END,
CASE WHEN :ASSORT = '0' THEN ARRC03
WHEN :ASSORT = '1' THEN ARRC05
WHEN :ASSORT = '2' THEN ARRC02
END,
CASE WHEN :ASSORT = '0' THEN ARYEAR
WHEN :ASSORT = '1' THEN ARYEAR
WHEN :ASSORT = '2' THEN ARRC03
END,
CASE WHEN :ASSORT = '0' THEN ARMNTH
WHEN :ASSORT = '1' THEN ARMNTH
WHEN :ASSORT = '2' THEN ARYEAR
END,
CASE WHEN :ASSORT = '0' THEN ARSRC
WHEN :ASSORT = '1' THEN ARSRC
WHEN :ASSORT = '2' THEN ARMNTH
END,
CASE WHEN :ASSORT = '0' THEN ARINVD
WHEN :ASSORT = '1' THEN ARINVD
WHEN :ASSORT = '2' THEN ARSRC
END,
CASE WHEN :ASSORT = '2' THEN ARINVD
ELSE NULL
END




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

(Embedded image moved to file: pic00491.gif)

"The Secret's the Sauce! Enjoy our new Bob-B-Q Pulled Pork Knife & Fork
Sandwich!"


web400-bounces@xxxxxxxxxxxx wrote on 10/31/2007 10:09:41 AM:

<snip>
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.
</snip>

You don't need join logicals when using SQL. You can put values from
multiple tables in your ORDER BY clause. The DB manager may end up
creating a temp table but you don't need to manage it.

<snip>
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.
</snip>

It's possible to do a multi-row FETCH which has the same effect as
buffering with traditional IO opcodes. You need to create a data
structure with the occurs keyword and then on your FETCH, you use the
syntax FETCH <number of rows> INTO :<name of datastructre>. Variable
SQLER5 will have the number of rows that were actually returned in it.
You may want to search through the RPG-400 list archives for more
information on this (The SQL programming books cover this as well).

SQL is tuned for working with large data sets. Once you start working
with large numbers of records it usually performs better than
traditional IO does. The large the number of records, the bigger the
difference. Of course, there are always trade offs. If you dedicate huge
amounts of memory to buffering, you can get into the situation where you
start paging.

Matt

--
This is the Web Enabling the AS400 / iSeries (WEB400) mailing list
To post a message email: WEB400@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/web400
or email: WEB400-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/web400.