Populate columns with random numbers
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 30

Thread: Populate columns with random numbers

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    Populate columns with random numbers

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    dbms_random

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

  3. #3
    Join Date
    Jan 2004
    Posts
    162
    Something like this perhaps...
    Code:
    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...
    Code:
    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>

  4. #4
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    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 ??

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    That's why they have this wonderful thing called documentation!
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    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?

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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?

  10. #10
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Very funny Jeff, but ultimately useless........

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width