mardi 10 juillet 2018

Why does dbms_random.value return the same value in graph queries (connect by)

On Oracle 11.2.0.4.0, when I run the following query then each row gets a different result:

select r.n from (
  select trunc(dbms_random.value(1, 100)) n from dual
) r
connect by level < 100; -- returns random values

But as soon as I use the obtained random value in a join or subquery then each row gets the same value from dbms_random.value:

select r.n, (select r.n from dual) from (
  select trunc(dbms_random.value(1, 100)) n from dual
) r
connect by level < 100; -- returns the same value each time

Is it possible to make the second query return random values for each row?




Aucun commentaire:

Enregistrer un commentaire