SQL Language (MOSC)

MOSC Banner

Filtering rows with "DBMS_RANDOM": a small bug? (tested in Oracle 10.2 and 11.1)

edited Apr 3, 2011 10:31PM in SQL Language (MOSC) 12 commentsAnswered ✓
Hello,

I want to share something that has annoyed me. My goal was to filter "randomly" x% of a SELECT statement, and the result was not what I was expecting... I provide the code for a test:

CREATE TABLE mytable( myfield VARCHAR2(20) );
INSERT INTO mytable
  SELECT TO_CHAR( TO_DATE( TO_CHAR( LEVEL ), 'J' ), 'Jsp' )
    FROM dual
    CONNECT BY LEVEL <= 25
;
COL yes_or_no FOR 0.000
SELECT yes_or_no, '10%? ' || myfield
  FROM ( SELECT myfield
              , dbms_random.value yes_or_no
           FROM mytable
       ) x
  WHERE x.yes_or_no <= 0.1
;
YES_OR_NO '10%?'||MYFIELD
--------- -------------------------
    0.069 10%? Five
    0.019 10%? Eight
/
YES_OR_NO '10%?'||MYFIELD

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center