jeudi 12 décembre 2019

Using DBMS_RANDOM.Value function in SQL query for equality comparison - returning multiple records

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: enter image description here enter image description here

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