RE: SQL RAND function question

[<lgoodbar@xxxxxxxxxxxxxx>]

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