Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to generate specified Random
There were a couple of mistakes in my previous posting.
select .... from doctors where doctor_id = rand;
doesn't work because the rand function as written by Thomas Kyte, which I'm
using, and,
indeed, almost any other pseudo-random number generator function, changes
package
variables, which means that the function cannot be used in the where clause
of a
select statement. The way to solve this is to select the random number
first, in a
separate piece of SQL:
select random.rand_max(1000) into random_var from dual;
select .... from doctors where doctor_id = random_var;
My other solution, where there isn't a numeric key on your table also won't
work,
firstly for the same reason as above, and secondly because ( as someone
else
pointed out )
select ....
from doctors where rownum = something
never works ( unless something = 1 ), as the value for rownum is assigned
after the
where clause has been evaluated.
Anyway, from the point of efficiency, you're onto a loser unless you have
an indexed numeric
key.
Dave.
--
To reply, please remove the no-spam bit from my email address
Received on Mon Aug 17 1998 - 06:33:43 CDT