RE: SQL RAND function question

Interesting experiment. Changing to _floor(rand())*11_ resulted in:

....+....1....+....2....+....3
NUMBER COUNT ( * )
0 9,998
11 2
******** End of data ********

So rand() definitely does both 0 and 1, it's just that a true "1" result
is extremely rare.

Charles, perhaps using something like the below would work for you, as
all the other numbers in the range are evenly distributed. A little ugly
but appears to work.

exec sql insert into loyd/randno (number)
values(case when cast(rand()*12 as decimal(2,0))
between 0 and 11 then cast(rand()*12 as decimal(2,0))
end);

....+....1....+....2....+....3
NUMBER COUNT ( * )
0 806
1 853
2 804
3 902
4 844
5 827
6 851
7 839
8 826
9 832
10 831
11 785
******** End of data ********

While #11 is only 94% of an even distribution (833), #3 is 108% of even
distribution.

Loyd Goodbar
Senior programmer/analyst
BorgWarner
TS Water Valley
662-473-5713

-----Original Message-----
From: Goodbar, Loyd (Water Valley)
Sent: Wednesday, February 13, 2008 09:34
To: 'Midrange Systems Technical Discussion'
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