Forum Stats

  • 3,826,198 Users
  • 2,260,605 Discussions
  • 7,896,813 Comments

Discussions

Creating data with dbms_random

BeefStu
BeefStu Member Posts: 327 Blue Ribbon

I'm putting together sample test data using SQL trying to populate the table below but I appear to be running into some syntax errors that I can't figure out.


I was hoping someone can help me out. Thanks in advance to all who respond and for your expertise.


CREATE TABLE students (
   student_id  number(*,0),
  first_name VARCHAR(25) NOT NULL,
  last_name VARCHAR(25) NOT NULL,
  active VARCHAR2(1) DEFAULT 'Y',
constraint student_pk primary key (student_id));


   insert into students (student_id, first_name, last_name, active)
WITH cte AS ( SELECT  level lvl FROM dual CONNECT BY level <= 5 )
     SELECT rownum, case t1.lvl
            WHEN 1 THEN 'Faith' 
            WHEN 2 THEN 'Tom' 
            WHEN 3 THEN 'Anna'
            WHEN 4 THEN 'Lisa' 
            WHEN 5 THEN 'Andy' 
            end first_name,
         
        SELECT rownum, case t1.lvl
            WHEN 1 THEN 'Andrews' 
            WHEN 2 THEN 'Thorton' 
            WHEN 3 THEN 'Smith'
            WHEN 4 THEN 'Jones' 
            WHEN 5 THEN 'Beirs' 
            end END last_name,
      
       CASE MOD(LEVEL, 5)
           WHEN 0
          THEN 'N'
          ELSE 'Y'
       end
FROM 
(SELECT lvl FROM cte 
ORDER BY dbms_random.value() ) t1;
      
Tagged:

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    Answer ✓

    There are various ways you could do it. Here's one way (though probably a bit long-winded)...

    SQL> with fn as (select rownum r, first_name
      2              from (
      3                    select case rownum
      4                            WHEN 1 THEN 'Faith'
      5                            WHEN 2 THEN 'Tom'
      6                            WHEN 3 THEN 'Anna'
      7                            WHEN 4 THEN 'Lisa'
      8                            WHEN 5 THEN 'Andy'
      9                           end as first_name
     10                    from  (select level l from dual connect by level <= 5)
     11                    order by dbms_random.value()
     12                   )
     13             )
     14      ,ln as (select rownum r, last_name
     15              from (
     16                    select case rownum
     17                            WHEN 1 THEN 'Andrews'
     18                            WHEN 2 THEN 'Thorton'
     19                            WHEN 3 THEN 'Smith'
     20                            WHEN 4 THEN 'Jones'
     21                            WHEN 5 THEN 'Beirs'
     22                           end as last_name
     23                    from  (select level l from dual connect by level <= 5)
     24                    order by dbms_random.value()
     25                   )
     26             )
     27      ,ac as (select rownum r, active
     28              from (
     29                    select case mod(rownum,2) when 0 then 'N' else 'Y' end as active
     30                    from  (select level l from dual connect by level <= 5)
     31                    order by dbms_random.value()
     32                   )
     33             )
     34  select fn.r, fn.first_name, ln.last_name, ac.active
     35  from   fn
     36         join ln on (fn.r = ln.r)
     37         join ac on (ac.r = ln.r)
     38  /
    
             R FIRST LAST_NA A
    ---------- ----- ------- -
             1 Andy  Thorton Y
             2 Tom   Andrews Y
             3 Faith Jones   N
             4 Lisa  Beirs   N
             5 Anna  Smith   Y
    
    SQL> /
    
             R FIRST LAST_NA A
    ---------- ----- ------- -
             1 Anna  Jones   Y
             2 Andy  Andrews Y
             3 Tom   Thorton Y
             4 Faith Smith   N
             5 Lisa  Beirs   N
    
    SQL> /
    
             R FIRST LAST_NA A
    ---------- ----- ------- -
             1 Andy  Beirs   Y
             2 Anna  Jones   Y
             3 Faith Andrews Y
             4 Tom   Thorton N
             5 Lisa  Smith   N
    
    SQL> /
    
             R FIRST LAST_NA A
    ---------- ----- ------- -
             1 Tom   Beirs   Y
             2 Andy  Thorton N
             3 Lisa  Smith   Y
             4 Anna  Jones   Y
             5 Faith Andrews N
    


«1

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond

    Yes, you have loads of syntax errors.

    If you'd formatted your code better, and read the error messages (and what lines they occur on) it would be obvious what needed fixing (and there were several)...

    WITH cte AS ( SELECT  level lvl FROM dual CONNECT BY level <= 5 )
    SELECT rownum
         ,case t1.lvl
            WHEN 1 THEN 'Faith' 
            WHEN 2 THEN 'Tom' 
            WHEN 3 THEN 'Anna'
            WHEN 4 THEN 'Lisa' 
            WHEN 5 THEN 'Andy' 
          end as first_name         
         ,case t1.lvl
            WHEN 1 THEN 'Andrews' 
            WHEN 2 THEN 'Thorton' 
            WHEN 3 THEN 'Smith'
            WHEN 4 THEN 'Jones' 
            WHEN 5 THEN 'Beirs' 
          end as last_name
         ,case MOD(lvl, 5)
            WHEN 0
            THEN 'N'
            ELSE 'Y'
          end as active
    FROM (SELECT lvl
          FROM   cte 
          ORDER BY dbms_random.value()
         ) t1
    


    BeefStu
  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond

    By the way, your code doesn't create random combinations because your case statements are relying on the same random value.

  • BeefStu
    BeefStu Member Posts: 327 Blue Ribbon

    @BluShadow thanks for your time and expertise. The purpose of this is to create different random values everytime the code is run. Can you show me how to achieve this goal? Any help would be greatly appreciated

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    Answer ✓

    There are various ways you could do it. Here's one way (though probably a bit long-winded)...

    SQL> with fn as (select rownum r, first_name
      2              from (
      3                    select case rownum
      4                            WHEN 1 THEN 'Faith'
      5                            WHEN 2 THEN 'Tom'
      6                            WHEN 3 THEN 'Anna'
      7                            WHEN 4 THEN 'Lisa'
      8                            WHEN 5 THEN 'Andy'
      9                           end as first_name
     10                    from  (select level l from dual connect by level <= 5)
     11                    order by dbms_random.value()
     12                   )
     13             )
     14      ,ln as (select rownum r, last_name
     15              from (
     16                    select case rownum
     17                            WHEN 1 THEN 'Andrews'
     18                            WHEN 2 THEN 'Thorton'
     19                            WHEN 3 THEN 'Smith'
     20                            WHEN 4 THEN 'Jones'
     21                            WHEN 5 THEN 'Beirs'
     22                           end as last_name
     23                    from  (select level l from dual connect by level <= 5)
     24                    order by dbms_random.value()
     25                   )
     26             )
     27      ,ac as (select rownum r, active
     28              from (
     29                    select case mod(rownum,2) when 0 then 'N' else 'Y' end as active
     30                    from  (select level l from dual connect by level <= 5)
     31                    order by dbms_random.value()
     32                   )
     33             )
     34  select fn.r, fn.first_name, ln.last_name, ac.active
     35  from   fn
     36         join ln on (fn.r = ln.r)
     37         join ac on (ac.r = ln.r)
     38  /
    
             R FIRST LAST_NA A
    ---------- ----- ------- -
             1 Andy  Thorton Y
             2 Tom   Andrews Y
             3 Faith Jones   N
             4 Lisa  Beirs   N
             5 Anna  Smith   Y
    
    SQL> /
    
             R FIRST LAST_NA A
    ---------- ----- ------- -
             1 Anna  Jones   Y
             2 Andy  Andrews Y
             3 Tom   Thorton Y
             4 Faith Smith   N
             5 Lisa  Beirs   N
    
    SQL> /
    
             R FIRST LAST_NA A
    ---------- ----- ------- -
             1 Andy  Beirs   Y
             2 Anna  Jones   Y
             3 Faith Andrews Y
             4 Tom   Thorton N
             5 Lisa  Smith   N
    
    SQL> /
    
             R FIRST LAST_NA A
    ---------- ----- ------- -
             1 Tom   Beirs   Y
             2 Andy  Thorton N
             3 Lisa  Smith   Y
             4 Anna  Jones   Y
             5 Faith Andrews N
    


  • Stax
    Stax Member Posts: 40 Red Ribbon

    Hi,BeefStu

    WITH cte AS ( SELECT  level lvl FROM dual CONNECT BY level <= 5 )
        SELECT rownum, 
          case t1.lvl
                WHEN 1 THEN 'Faith' 
                WHEN 2 THEN 'Tom' 
                WHEN 3 THEN 'Anna'
                WHEN 4 THEN 'Lisa' 
                WHEN 5 THEN 'Andy' 
                end first_name,
          case t1.lvl
                WHEN 1 THEN 'Andrews' 
                WHEN 2 THEN 'Thorton' 
                WHEN 3 THEN 'Smith'
                WHEN 4 THEN 'Jones' 
                WHEN 5 THEN 'Beirs' 
                end last_name,
           CASE MOD(rownum, 5) --???
               WHEN 0
              THEN 'N'
              ELSE 'Y'
           end
    FROM (
     SELECT lvl 
     FROM cte 
     ORDER BY dbms_random.value(0,sign(lvl))
    )
     t1
    
    SQL> /
    
    
        ROWNUM FIRST LAST_NA C
    ---------- ----- ------- -
             1 Faith Andrews Y
             2 Lisa  Jones   Y
             3 Tom   Thorton Y
             4 Anna  Smith   Y
             5 Andy  Beirs   N
    
    
    
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,048 Red Diamond

    Hi, @BeefStu

    Can you show me how to achieve this goal? 

    Exactly how to achieve the goal depends on exactly what the goal is. Explain precisely what you want to do. For example:

    1. I want to INSERT N rows into the students table.
    2. Those rows will have student_ids 1, 2, 3, ..., N.
    3. Each row will have a unique first_name from a given list ('Faith', 'Tom', ...) in random order. That is, the row with student_id = 1 will not necessarily have first_name = 'Faith'.
    4. ...
  • BeefStu
    BeefStu Member Posts: 327 Blue Ribbon

    @Frank Kulash thanks for taking the time to respond. Blushadow last solution is the output I was looking to achieve. However, if there is a way to get the same behavior with less code that would be great!!

  • Stax
    Stax Member Posts: 40 Red Ribbon
    SQL> ed
    Wrote file afiedt.buf
    
    
      1  WITH cte AS ( SELECT  level lvl FROM dual CONNECT BY level <= 5 )
      2      SELECT rownum,
      3        case t1.lvl
      4              WHEN 1 THEN 'Faith'
      5              WHEN 2 THEN 'Tom'
      6              WHEN 3 THEN 'Anna'
      7              WHEN 4 THEN 'Lisa'
      8              WHEN 5 THEN 'Andy'
      9              end first_name,
     10        case t1.lvl
     11              WHEN 1 THEN 'Andrews'
     12              WHEN 2 THEN 'Thorton'
     13              WHEN 3 THEN 'Smith'
     14              WHEN 4 THEN 'Jones'
     15              WHEN 5 THEN 'Beirs'
     16              end last_name,
     17         CASE MOD(rownum, 5)
     18             WHEN 0
     19            THEN 'N'
     20            ELSE 'Y'
     21         end
     22  FROM (
     23   SELECT lvl
     24   FROM cte
     25   ORDER BY dbms_random.value(0,sign(lvl))
     26  )
     27*  t1
    SQL> /
    
    
        ROWNUM FIRST LAST_NA C
    ---------- ----- ------- -
             1 Faith Andrews Y
             2 Lisa  Jones   Y
             3 Tom   Thorton Y
             4 Anna  Smith   Y
             5 Andy  Beirs   N
    
    
    SQL> /
    
    
        ROWNUM FIRST LAST_NA C
    ---------- ----- ------- -
             1 Lisa  Jones   Y
             2 Tom   Thorton Y
             3 Anna  Smith   Y
             4 Andy  Beirs   Y
             5 Faith Andrews N
    
    
    SQL> /
    
    
        ROWNUM FIRST LAST_NA C
    ---------- ----- ------- -
             1 Anna  Smith   Y
             2 Lisa  Jones   Y
             3 Tom   Thorton Y
             4 Faith Andrews Y
             5 Andy  Beirs   N
    
    
    SQL>
    
  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond

    @Stax your version is not creating random test data by combining different first names with last names, and row number 5 is always the inactive one. Mine mixes everything up, except for the fact that, in the way I've done it, I've always got 2 (in this case) inactive ones out of 5, but if more rows were generated it's about 50/50 split on the active, inactive because I did mod(n,2). That can be changed as needed, but I think the OP was looking for generating random mixes of data each time.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond

    Reduced a little...

    SQL> with gen as (select level l from dual connect by level <= 5)
      2      ,fn as (select row_number() over (order by dbms_random.value()) as r
      3                    ,case rownum
      4                            WHEN 1 THEN 'Faith'
      5                            WHEN 2 THEN 'Tom'
      6                            WHEN 3 THEN 'Anna'
      7                            WHEN 4 THEN 'Lisa'
      8                            WHEN 5 THEN 'Andy'
      9                           end as first_name
     10                    from  gen
     11             )
     12      ,ln as (select row_number() over (order by dbms_random.value()) as r
     13                    ,case rownum
     14                            WHEN 1 THEN 'Andrews'
     15                            WHEN 2 THEN 'Thorton'
     16                            WHEN 3 THEN 'Smith'
     17                            WHEN 4 THEN 'Jones'
     18                            WHEN 5 THEN 'Beirs'
     19                           end as last_name
     20                    from  gen
     21             )
     22      ,ac as (select row_number() over (order by dbms_random.value()) as r
     23                    ,case mod(rownum,2) when 0 then 'N' else 'Y' end as active
     24              from  gen
     25             )
     26  select fn.r, fn.first_name, ln.last_name, ac.active
     27  from   fn
     28         join ln on (fn.r = ln.r)
     29         join ac on (ac.r = ln.r)
     30  /
    
             R FIRST LAST_NA A
    ---------- ----- ------- -
             1 Anna  Andrews Y
             2 Andy  Jones   N
             3 Tom   Beirs   N
             4 Lisa  Smith   Y
             5 Faith Thorton Y
    
    SQL> /
    
             R FIRST LAST_NA A
    ---------- ----- ------- -
             1 Lisa  Beirs   N
             2 Anna  Thorton Y
             3 Andy  Jones   Y
             4 Faith Andrews N
             5 Tom   Smith   Y
    
    SQL> /
    
             R FIRST LAST_NA A
    ---------- ----- ------- -
             1 Lisa  Jones   Y
             2 Faith Andrews Y
             3 Anna  Thorton Y
             4 Andy  Smith   N
             5 Tom   Beirs   N
    


    I'm sure we could also do this with XMLTABLE/XQUERY or MATCH_RECOGNIZE or the MODEL clause or some other funky way too. I'm just sticking to the basics here. 😉