Slkrasnodar Slkrasnodar - 1 month ago 9
SQL Question

where clause with = sign matches multiple records while expected just one record

I have a simple inline view that contains 2 columns.


-----------------


rn | val


-----------------


0 | A


... | ...


25 | Z



I am trying to select a
val
by matching the
rn
randomly by using the
dbms_random.value()
method as in

with d (rn, val) as
(
select level-1, chr(64+level) from dual connect by level <= 26
)
select * from d
where rn = floor(dbms_random.value()*25)
;


My expectation is it should return one row only without failing.
But now and then I get multiple rows returned or no rows at all.

on the other hand,

>>select floor(dbms_random.value()*25) from dual connect by level <1000


returns a whole number for each row and I failed to see any abnormality.

What am I missing here?

Answer Source

The problem is that the random value is recalculated for each row. So, you might get two random values that match the value -- or go through all the values and never get a hit.

One way to get around this is:

select d.*
from (select d.*
      from d 
      order by dbms_random.value()
     ) d
where rownum = 1;

There are more efficient ways to calculate a random number, but this is intended to be a simple modification to your existing query.

You also might want to ask another question. This question starts with a description of a table that is not used, and then the question is about a query that doesn't use the table. Ask another question, describing the table and the real problem you are having -- along with sample data and desired results.