RE: SQL RAND function question

["Wilt, Charles" <WiltC@xxxxxxxxxx>]

Loyd,

Use floor(rand() * 11.99999)  to get 0-11.

Charles Wilt 
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx


> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
> bounces@xxxxxxxxxxxx] On Behalf Of lgoodbar@xxxxxxxxxxxxxx
> Sent: Wednesday, February 13, 2008 10:34 AM
> To: midrange-l@xxxxxxxxxxxx
> Subject: RE: SQL RAND function question
>
> Hi Luis,
>
> Changing to floor() made no appreciable difference. Here is the revised
> program and results. The workaround I see so far is to calculate 0-12,
> but use only 0-11. The results for the topmost number are so small they
> would not skew the distribution.
>
> d x               s             10i 0 inz
>  /free
>     exec sql set option commit=*none;
>     for x = 1 to 10000;
>       exec sql insert into loyd/randno (number)
>              values(floor(rand()*11));
>     endfor;
>     *inlr = *on;
>     return;
>  /end-free
>
> 10,000:
> ....+....1....+....2....+....3
>        NUMBER     COUNT ( * )
>             0             931
>             1             934
>             2             922
>             3             913
>             4             906
>             5             896
>             6             908
>             7             934
>             8             851
>             9             920
>            10             884
>            11               1
> ********  End of data  ********
>
> 100,000:
> ....+....1....+....2....+....3
>        NUMBER     COUNT ( * )
>             0           9,196
>             1           9,045
>             2           8,989
>             3           9,204
>             4           9,174
>             5           8,925
>             6           9,169
>             7           9,122
>             8           9,162
>             9           8,921
>            10           9,091
>            11               2
> ********  End of data  ********
>
> 1,000,000:
> ....+....1....+....2....+....3
>        NUMBER     COUNT ( * )
>             0          90,792
>             1          91,277
>             2          90,710
>             3          91,019
>             4          90,531
>             5          91,222
>             6          90,865
>             7          90,754
>             8          90,962
>             9          90,898
>            10          90,947
>            11              23
> ********  End of data  ********
>
>
> Loyd Goodbar
> Senior programmer/analyst
> BorgWarner
> TS Water Valley
> 662-473-5713
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Luis Colorado
> Sent: Wednesday, February 13, 2008 09:09
> To: Midrange Systems Technical Discussion
> Subject: RE: SQL RAND function question
>
> Loyd,
>
> This is a bit surprising. Every implementation that I have seen of
> rand() or rnd() in other languages gives a random number in the
> following range: [0,1)
>
> I would say that maybe there is a bug in the cast to int, which
> apparently rounds up to 1 the number 0.99999999... at some unknown
> precision.
>
> Would you try this again, using floor() instead of the cast? Something
> like
>
>       exec sql insert into loyd/randno (number)
>              values(floor(rand()*11));
>
> It would be interesting to see if the problem is the cast or the rand()
> function itself.
>
> Great job!
>
> Luis
>
> --
> 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.



This e-mail transmission contains information that is intended to be confidential and privileged.  If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful.  Please reply to the message immediately by informing the sender that the message was misdirected.  After replying, please delete and otherwise erase it and any attachments from your computer system.  Your assistance in correcting this error is appreciated.