RE: SQL RAND function question

Wow. The distribution is fairly even except for the topmost number in
the range, regardless of the multiplier. Even tried a permutation such
as (rand()*121)/11 but the top number was still short in the
distribution.

The argument for the RAND function is the seed value. I omitted it in my
runs to get a new seed every time the RAND function is called. For
instance, when I called with rand(-1)*11, I got the number 5, 10000
times for a run of 10000.

Have you filed a PMR? This is reproducible.

Created a table:
create table loyd/randno (number int)

Wrote a program to populate based on 0-11 range:
d x s 10i 0 inz
/free
exec sql set option commit=*none;
for x = 1 to 1000000;
exec sql insert into loyd/randno (number)
values(cast(rand()*11 as decimal(2,0)));
endfor;
*inlr = *on;
return;
/end-free

Verified the file contained the correct number of records (in the first
run, 1,000,000).

Results in SQL:
select number, count(*) from loyd/randno group by number order by 1

....+....1....+....2....+....3
NUMBER COUNT ( * )
0 90,751
1 90,312
2 91,026
3 90,885
4 91,061
5 91,168
6 90,453
7 91,287
8 90,914
9 90,861
10 91,245
11 37
******** End of data ********

Results for 100,000:
....+....1....+....2....+....3
NUMBER COUNT ( * )
0 9,183
1 8,986
2 9,275
3 9,190
4 9,109
5 8,926
6 9,132
7 9,092
8 9,082
9 8,987
10 9,034
11 4
******** End of data ********

Results for 10,000 (11 does not show):
....+....1....+....2....+....3
NUMBER COUNT ( * )
0 891
1 899
2 938
3 947
4 907
5 890
6 906
7 881
8 883
9 919
10 939
******** 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 Wilt, Charles
Sent: Wednesday, February 13, 2008 07:15
To: Midrange Systems Technical Discussion
Subject: RE: SQL RAND function question

Loyd,

Yes it does. BUT if you call it 1000's of times, it doesn't given you
an even distribution of numbers
between 0-11.

By definition, RAND() should given an even distribution.

Charles Wilt
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx