[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


using custom distribution in Oracle / PL/SQL

Posted on 2007-07-24
Medium Priority
Last Modified: 2013-12-18
Looking for some urgent help with implementing a custom statistical distribution in Oracle 10g: The scenario goes like this: I want a function that returns a single value based on a table that looks like this:
Value   Freq%
*******  *********
A         5.3%
B         4.8 %
C         5.1 %
D         10.5 %

When I call this function, I want it to return the value of A 5.3% of the time, the value of B 4.8% of the time and so on and so forth.

Is this possible using PL/SQL? if so could someone shed some light on how to approach this problem.

Much appreciated.
Question by:choochim
  • 2
  • 2
LVL 35

Expert Comment

ID: 19558084
The way I have done this in the past is to use a random number generator (DBMS_RANDOM for example) and generate a number between 0 and 1, then take your percentages and convert then to numbers (5.3% = .053) and check them in order to see where the number falls and return that value.

Author Comment

ID: 19558250
thanks for the quick respone. One doubt though, what if I have multiple hits on a single random number value say - there are A,B,C as three possible hits for the same random number? Then what is the remedy?
LVL 35

Accepted Solution

johnsone earned 2000 total points
ID: 19558718
You have to make the numbers cumulative.

Basically, if there are 3 values:

A 40%
B 30%
C 30%

Then the numbers you need to look for are:

A 40%  0.4
B 30% 0.7
C 30% 1.0

So if the number is <= .4 then you pick A, <= .7 then you pick B, <= 1 you pick C.  You have to do a small amount of research on the random number generator, but usually they generate numbers that are 0 < num <= 1, if this is the case, this should work.

Author Comment

ID: 19558766
Thanks. After posting my initial response, I figured that out and modified to a cume percentage and that did the trick.

You guys are good !

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

834 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