[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Randomizing in Oracle with limited outobs

Posted on 2014-08-21
2
Medium Priority
?
421 Views
Last Modified: 2014-08-21
Experts,

I am wondering if the use of :
order by
           dbms_random.value

in an Oracle query with outobs = 10 in the Proc Sql statement could cause records that are in the table not to be be selected.
I am trying to select 10 records that have a certain status code.  I wanted to randomly pick the records and limit the number of records.

If there are only 30 records on the table that meet the criteria, it is possible that all of the 30 records are being bypassed because of the randomizing routine causing me to get no records in my selection?
0
Comment
Question by:morinia
1 Comment
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40277352
>>I am trying to select 10 records that have a certain status code.

This is defined by the where clause.

>>I wanted to randomly pick the records and limit the number of records.

Just like your previous question:
http://www.experts-exchange.com/Database/Oracle/Q_28501923.html

There is an inner select and an outer select.  The "where status_code=some_value" goes on the inner select.

So you end up with:
SELECT  *
 FROM    (
         SELECT  *
         FROM    mytable
         WHERE status_code=10
         ORDER BY
                 dbms_random.value
         )
 WHERE rownum <= 1000

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question