Re: RPGLESQL - random records from large file

[Colin Williams <colinwilliams007@xxxxxxxxx>]

or use a CTE to select the 12,000 first

On Wed, 2 Oct 2019 at 19:53, Colin Williams <colinwilliams007@xxxxxxxxx>
wrote:

> 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