Re: Limiting SQL results returned

Charles and Elvis,

That did exactly what I needed. Now I just need to expand it to fit my
requirements for the auditors. A random selection of nn% of the total
number of records matching a criteria. I can get all that. Great help and
THANKS!

Chuck,

I think I understand what you are saying about performance of the
statements. If I have 5 million records in the original table, it creates 5
million records in T1, and then the subset in T2. Could be disk as well as
CPU hog. What I don't understand is your alternative method. I very much
want to understand concept you are giving me. Can you (or someone) post a
sample of an external table function? Would that be SQL statement or a RPG
or COBOL program?

Thanks!

Jim

On 8/1/07, CRPence <crp@xxxxxxxxxxxxxxxxxxxx> wrote:

Yes that should work, but to those for which it might not be obvious,
with the _caveat_ that T1 generates a temporary table of all rows in the
original file [of the named fields & expression]. From the original
description it would not seem to be an issue [given there might not even
be five rows for any one status], but... The number of rows might make
such an implementation prohibitive for some tables and/or environments.

For very large result sets, an alternate method might be to use an
external table function which returns a given number of rows from that
table for a specific key value. That program could use row level
access, to decide what rows to return. Some index might be available
and conducive to generating a relatively quick sampling; although
/random/ probably not nearly as easy as with rand() and ordering. If
the program is in its own activation group and can leave the file open
for the repeated calls for each status, the file would be opened only
for that named activation, thus avoiding open overhead.

Regards, Chuck
--
All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
employer

Elvis Budimlic wrote:

What Charles has should work for you Jim, just take out the 2nd WITH

clause

(CTEs list only requires WITH at the beginning of the list).

Elvis

Wilt, Charles wrote:
Jim,

Off the top of my head....

With T1 as (select status, infoField, rand() as randomNbr
from filename
),
With T2 AS (SELECT Status,
InfoField,
ROW_NUMBER() over (PARTITION BY Status ORDER BY Status, randomNbr)

as

Nbr
from T1 ORDER BY Status )

SELECT * FROM T2 WHERE Nbr < 6

Sorry, don't have v5r4 to test against.

HTH,
Charles

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.