Hi Booth
The only change that I would do is create a temporary file of the 1200 records
THEN do the random select based upon the rrn of the temporary file
This way - you can create the temporary file - multiple times but sorted in a different way
Hope that makes some sense
I just did the same thing - but the user wanted the randomness based upon different components of the actual file
First time
Order by field1
Second time
Order by field10
Third time
Order by field14
Hope you get the gist
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
‘If you're going through hell, keep going.’
Winston Churchill
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Booth Martin
Sent: Wednesday, October 2, 2019 2:49 PM
To: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] RPGLESQL - random records from large file
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
midrange.com
