Re: Faster HTML Output.

[Michael_Schutte@xxxxxxxxxxxx]

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.