I am on Oracle 11g and the case is as follows.
I have a select from subquery, like this:
select * from (
select rownum as rn
, 'just testing_' || rownum as just_string
from dual
connect by level <= 20
)
where rn = round(dbms_random.value(1,20))
so the result of subquery is 20 records with numeric column "rn" value from 1 to 20, and I expect to get one record from that subquery by comparing to result of DBMS_RANDOM.value, which I expect to get me an integer value from 1 to 20
However... result os such query tend to vary from no records to multiple records:

If I enclose the DBMS_RANDOM.value function in subquery like this:
select * from (
select rownum as rn
, 'just testing_' || rownum as just_string
from dual
connect by level <= 20
)
where rn = (select round(dbms_random.value(1,20)) from dual)
then it seems to always return one row as expected.
So can anyone explain how the first query works with DBMS_RANDOM.value so that it returns multiple or no rows ?
Aucun commentaire:
Enregistrer un commentaire