Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_RANDOM of LOG. It is possible?
DBMS_RANDOM of LOG. It is possible? [message #361198] Tue, 25 November 2008 06:10 Go to next message
lopes_andre
Messages: 14
Registered: October 2008
Junior Member
Hi,

I'am porting an Excel app to PL/SQL, and I'am with some trouble in doing this excel formula in PL/SQL.

=RAND()^LOG(1-1/(2+1);0,5)


How can I do this formula in PL/SQL using DBMS_RANDOM?


Best Regards,
André.
Re: DBMS_RANDOM of LOG. It is possible? [message #361200 is a reply to message #361198] Tue, 25 November 2008 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you explain what does this expression do and what is your Oracle version (4 decimals) maybe we can tell you.

Regards
Michel
Re: DBMS_RANDOM of LOG. It is possible? [message #361203 is a reply to message #361198] Tue, 25 November 2008 06:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's not a valid vunction in my version of Excel (2003)

LOG only takes two operators, number and base. There's no room in it for a ';' character.

Assuming that RAND() returns a value between 0 and 1 exclusive then
you can replace that with DBMS_RANDOM.VALUE(0,1)

The exponent operator ^ is implemented with the POWER function in oracle.
Re: DBMS_RANDOM of LOG. It is possible? [message #361206 is a reply to message #361198] Tue, 25 November 2008 06:34 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Andre,

You can use DBMS_RANDOM.VALUE(0, 1) instead of RAND() in Excel
You can read more about this package here.
SQL> SELECT DBMS_RANDOM.VALUE (0, 1) ran2
  2    FROM DUAL;

      RAN2
----------
.121265186


You can use POWER Function in numbers instead of "^" in Excel.
SQL> SELECT POWER(2, 3) FROM Dual;

POWER(2,3)
----------
         8



You can use the LOG Function for the LOG Function in Excel.

Combine these three according to your requirement and let us know where you are facing the problem.

Hope this helps.
Regards,
Jo

[Updated on: Tue, 25 November 2008 06:35]

Report message to a moderator

Re: DBMS_RANDOM of LOG. It is possible? [message #361208 is a reply to message #361198] Tue, 25 November 2008 06:37 Go to previous messageGo to next message
lopes_andre
Messages: 14
Registered: October 2008
Junior Member
Hi,

Sorry for my lack of explanation.

This expression means random number between 0 and 1 powered to log(0.5, 1-1/(8+1))

This is for a program that will make a tender based in weighted random picks. The base for my PL/SQL program will be this: http://www.dailydoseofexcel.com/archives/2008/03/23/random-sorts/

The expression works in Excel 2003. I have the european version, I think this version have some sintax differences. Try this expression, =RAND()^LOG(1-1/(8+1),0.5)


Best Regards
André.
Re: DBMS_RANDOM of LOG. It is possible? [message #361210 is a reply to message #361198] Tue, 25 November 2008 06:41 Go to previous messageGo to next message
lopes_andre
Messages: 14
Registered: October 2008
Junior Member
Thanks!!

I think is working:

SELECT POWER(DBMS_RANDOM.value(0, 1), log(0.5, 1-1/(8+1))) FROM Dual;


Best Regards,
André.
Re: DBMS_RANDOM of LOG. It is possible? [message #361216 is a reply to message #361198] Tue, 25 November 2008 06:51 Go to previous messageGo to next message
lopes_andre
Messages: 14
Registered: October 2008
Junior Member
Hi again,

I'am trying to make an select into a variable but is not working.

vARRAY_PONDERACAO := 0;
               
                      WHILE vARRAY_PONDERACAO <= cDADOS_P_SORTEIO.Ponderacao
                      LOOP
                      vARRAY_PONDERACAO := vARRAY_PONDERACAO + 1;
               
                      SELECT POWER(DBMS_RANDOM.value(0, 1), log(0.5, 1-1/(2+1))) into vLOG FROM Dual;
                      
                      vLOG := vLOG;
                      
                      END LOOP;


What is wrong here? I'am debugging this code, the vLOG value is 0. Why?


Best Regards,
André.
Re: DBMS_RANDOM of LOG. It is possible? [message #361218 is a reply to message #361216] Tue, 25 November 2008 07:04 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Andre,

It sure works for me.


SQL> DECLARE
  2  vLog NUMBER := 1;
  3  BEGIN
  4  SELECT POWER(DBMS_RANDOM.value(0, 1), log(0.5, 1-1/(2+1))) into vLOG FROM Dual;
  5  DBMS_OUTPUT.PUT_LINE('vLog Value: ' || to_char(vLog));
  6  END;
  7  /
vLog Value: .6539734672075722197077827159155077453293

PL/SQL procedure successfully completed.

SQL> /
vLog Value: .6822907471723485338866083537553656312774

PL/SQL procedure successfully completed.

SQL> /
vLog Value: .9683476688670471384530208823210734193115

PL/SQL procedure successfully completed.



I guess it is takig the default value assigned to it. It might be not going inside the while loop to get the new value. Can't tell for sure as you have posted only a snippet of your whole PL/SQL.

[***Added***]
Forgot to mention this. What use is the following code in your snippet. I presume its useless.
vLOG := vLOG;


Hope this helps.
Regards,
Jo

[Updated on: Tue, 25 November 2008 08:12]

Report message to a moderator

Re: DBMS_RANDOM of LOG. It is possible? [message #361247 is a reply to message #361198] Tue, 25 November 2008 09:20 Go to previous messageGo to next message
lopes_andre
Messages: 14
Registered: October 2008
Junior Member
Thanks!

It is working now!!

The code is almost done.

I need only to know how to make a SELECT that will show only the 8 greatest values by order. By other words, how to limit a select to show only 8 results?


Best Regards,
André.
Re: DBMS_RANDOM of LOG. It is possible? [message #361249 is a reply to message #361247] Tue, 25 November 2008 09:29 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Andre,

Look into some of the Analytical Functions like RANK or DENSE_RANK.

You will find a good explanation in OraFAQ itself.
Try Analytic functions by Example
OR
Some examples of RANK Function can be found here.

Hope this helps.
Regards,
Jo
Re: DBMS_RANDOM of LOG. It is possible? [message #361263 is a reply to message #361198] Tue, 25 November 2008 10:22 Go to previous message
lopes_andre
Messages: 14
Registered: October 2008
Junior Member
Thanks a lot!

Best Regards,
André.
Previous Topic: giving user/pass in a DBURI call to the database
Next Topic: how see the real value from PLSQL
Goto Forum:
  


Current Time: Sun Dec 04 12:51:55 CST 2016

Total time taken to generate the page: 0.31056 seconds