Re: RPGLESQL - random records from large file

[Colin Williams <colinwilliams007@xxxxxxxxx>]

can you dump the 12000 into a temporary table / another table first?

On Wed, 2 Oct 2019 at 19:49, Booth Martin <booth@xxxxxxxxxxxx> wrote:

> A large file,say a million records in which there are about 12,000
> records with a "W" in Field20.  I want 10 random records from the
> 12000.  I can't figure out how to write the WHERE clause so that only
> records from the 12,000 will be included .
>
> Here's what I use for random hits on the big file:
>
>         exec sql select rand(MICROSECOND(CURRENT_TIMESTAMP)) into :wField5
>             from SYSIBM/SYSDUMMY1;
>         wSeed = wField5 * 10000;
>         //
> ---------------------------------------------------------------------
>         // Get 10 random numbers
>         clear wCount;
>         wNdx = 0;
>         dow wNdx < 10 and wCount < 1200;  // (if we can't get 10 in
> 1200, bail)
>           wCount += 1;
>           exec sql select rand(:wSeed) into :wField5
>             from SYSIBM/SYSDUMMY1;
>           wSeed = wField5 * 10000;
>           exec sql select FIELD1 into :wField1
>                      from FILE01
>                      where rrn(FILE01) = :wSeed;
>
> ...
>
> "where rrn(FILE01) = :wSeed and FIELD20 = 'W';" seems easy but somehow
> wrong.
>
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com