Re: SQL RAND function question

lgoodbar@xxxxxxxxxxxxxx wrote:

Have you filed a PMR? This is reproducible.

<snip>

exec sql insert into loyd/randno (number)
values(cast(rand()*11 as decimal(2,0)));

<snip>

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 ********

This is to be expected given the way RAND() works in DB2. It could be clearer in the documentation, but if you see my earlier post http://archive.midrange.com/midrange-l/200802/msg00481.html and Chuck's post http://archive.midrange.com/midrange-l/200802/msg00496.html, I think your results will make more sense. In your case, since you are truncating the result of the multiplication by using cast(), 11 will only show up if RAND() returned _exactly_ 1.