Re: Random Sample Using SQL?

beachst.cerick01_at_eds.com wrote:

> I believe the problem is not in forcing the function to evaluate each
> row, but rather in obtaining a seed for each row. Procedural
> implementations of random functions usually use the result of the current
> call as the seed for the next call. That's OK in PL/SQL programs, but
> ORACLE will not allow that for functions used in basic SQL--due to side
> effects. It won't allow you to save the seed in a package variable to be
> used on the next call.
>
> What seed does your random function use? What is an "overlaid" function?
> If that means simply one function calling another, and the original
> function violates the side effect rule, it still won't work. Oracle
> checks the first function and any that it calls, and so on.
>
> Thanks.
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

1. I use the last value as the seed as you said. (I also have a separate function to re-set the seed for repeatable runs)
2. It works fine up to V7.1.4 (using the restrict_references pragma). I believe the restrictions may be more restrictive in later versions.
3. You *do* need to force the function to be re-evaluated for each row in the case given. Otherwise, the SQL engine evaluates the apparently fixed expression (no parameters) once per SQL statement execution.
4. An overlaid function (as you suggested) is a copy of a function which uses different input / output parameters.

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"