Random Number Based on another Cell in Row

[ericjb]

Hi,

I have a table with a column in which I would like to generate random numbers. I know how to use the dbms_random function but don't know how I would do what I want to do.

I would like to have the column filled with random numbers that are based on another number in each row.

For example, I have a table with column A and B where A is filled with various number. I want column B to be random numbers that are +or- whatever number is in column A. So say a row in column A is 5, B should be a random number with boundary 5 +or- 2.

Is there a way to do this?

Thanks!

[debasisdas]

Try to write a trigger on insert or update event of data on column A and update columnb B accordingly.
You can also put the logic in a procedure and call in the triggering event.

[amitpatel66]

check this:

SQL> ed
Wrote file afiedt.buf
 
  1* update t1 set b = dbms_random.value(a,a-2)
SQL> /
 
2 rows updated.
 
SQL> select * from t1;
 
         A          B
---------- ----------
         1 .809607126
         6 4.67696837
 
SQL> update t1 set b = dbms_random.value(a,a+2);
 
2 rows updated.
 
SQL> select * from t1;
 
         A          B
---------- ----------
         1 1.81326377
         6 6.36075424
 
SQL> 
 

PS: DBMS_RQANDOM.VA LUE(LOW VALUE, HIGH VALUE) RETURNS NUMBER

[ericjb]

check this:

SQL> ed
Wrote file afiedt.buf
 
  1* update t1 set b = dbms_random.value(a,a-2)
SQL> /
 
2 rows updated.
 
SQL> select * from t1;
 
         A          B
---------- ----------
         1 .809607126
         6 4.67696837
 
SQL> update t1 set b = dbms_random.value(a,a+2);
 
2 rows updated.
 
SQL> select * from t1;
 
         A          B
---------- ----------
         1 1.81326377
         6 6.36075424
 
SQL> 
 

PS: DBMS_RQANDOM.VA LUE(LOW VALUE, HIGH VALUE) RETURNS NUMBER

Thanks! That worked perfectly.

[Dave44]

check this:

SQL> ed
Wrote file afiedt.buf
 
  1* update t1 set b = dbms_random.value(a,a-2)
SQL> /
 
2 rows updated.
 
SQL> select * from t1;
 
         A          B
---------- ----------
         1 .809607126
         6 4.67696837
 
SQL> update t1 set b = dbms_random.value(a,a+2);
 
2 rows updated.
 
SQL> select * from t1;
 
         A          B
---------- ----------
         1 1.81326377
         6 6.36075424
 
SQL> 
 

PS: DBMS_RQANDOM.VA LUE(LOW VALUE, HIGH VALUE) RETURNS NUMBER

alternatively he could use an approach like dbms_random.val ue(A - <boundary_numbe r>,A + <boundary_numbe r>) to get either + or - column A.