Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to generate specified Random

Re: How to generate specified Random

From: Dave Wotton <Dave.Wotton_at_it.no-spam.camcnty.gov.uk>
Date: 17 Aug 1998 11:33:43 GMT
Message-ID: <01bdc9d2$7f0fd0e0$7501020a@res1181.Camcnty.gov.uk>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US