Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Creating data with dbms_random

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;
Best 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
Answers
-
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
-
By the way, your code doesn't create random combinations because your case statements are relying on the same random value.
-
@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
-
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
-
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
-
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:
- I want to INSERT N rows into the students table.
- Those rows will have student_ids 1, 2, 3, ..., N.
- 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'.
- ...
-
@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!!
-
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>
-
@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.
-
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. 😉