× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.