Populate columns with random numbers

[Horace]

Hi All,

Gonna research this this morning, but someone may have a 'quick and ready' solution for me.........

Need to populate 2 new columns on a demo database on the same table. The columns are NUMBER_OF_TIMES_MAILED and NUMBER_OF_TIMES_RESPONDED.

MAILED column needs to be a random number between 1 and 6. RESPONDED column needs to be random, but less than MAILED.

Any input would be appreciated.

[DaPi]

dbms_random

(I suspect the 8i & 9i doc may not be complete)

[padders]

Something like this perhaps...

SELECT n, TRUNC (DBMS_RANDOM.VALUE (0, n)) m 
FROM  (SELECT TRUNC (DBMS_RANDOM.VALUE (1, 7)) n 
       FROM   {your_row_source_here})

for example...

Personal Oracle Database 10g Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> SELECT n, TRUNC (DBMS_RANDOM.VALUE (0, n)) m 
  2  FROM  (SELECT TRUNC (DBMS_RANDOM.VALUE (1, 7)) n 
  3         FROM   dual 
  4         MODEL DIMENSION BY (0 x) MEASURES (0 y) 
  5         RULES ITERATE (10) (y [iteration_number] = 0));

         N          M
---------- ----------
         4          3
         2          0
         3          0
         1          0
         3          2
         2          1
         2          1
         4          3
         3          0
         3          1

10 rows selected.

SQL>

[Horace]

I'm impressed. Thanks for the input padders.

Of course, I have no idea what the code is doing.

Does this only apply to 10g, as unfortunately we're productionised on 9i ??

[marist89]

That's why they have this wonderful thing called documentation!

[Horace]

c'mon. I'm swamped with building the flamin' demo database (last minute request from sales), on top of the daily fire fighting, and answering stupid questions from the clients.

Gimme a break with the RTFM jibe. I posted in good faith, looking for a little help. Can you answer my question?

[jmodic]

Originally posted by Horace
Does this only apply to 10g, as unfortunately we're productionised on 9i ??

It should work with any release that has DBMS_RANDOM package available (at least from 8i on, I belive).

Of course the specific implementation of
{your_row_source_here} that padders used in the second query could not work prior to 10g, since MODEL clause is not available in pre-10g. But that is not essential for your problem, you simply replace the {your_row_source_here} with your table name.

[marist89]

Originally posted by Horace
c'mon. I'm swamped with building the flamin' demo database (last minute request from sales), on top of the daily fire fighting, and answering stupid questions from the clients.

Gimme a break with the RTFM jibe. I posted in good faith, looking for a little help. Can you answer my question?

[DaPi]

Jeff, Jurij,

this is part of my problem: PL/SQL supplied packages doc for 8i & 9i doesn't mention dbms_random.value - where is it documented?

[Horace]

Very funny Jeff, but ultimately useless........