Home » SQL & PL/SQL » SQL & PL/SQL » Creating a record from existing random records (5 merged) (RDBMS 11.2.0.4)
Creating a record from existing random records (5 merged) [message #681142] Fri, 19 June 2020 10:27 Go to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
I have 2 source tables employees, locations, which don't have the same record counts and a destination table access_history.

I want to randomly pick records (some columns from each table) and populate my destination table with those values.

I know I can do a subquery (see below) to populate the employee part of the access_history table with N number if rows

   
    SELECT *
    from ( select employee_id, 
                         card_num 
                        from employees 
                        where 
                          last_name like '%' 
            order by dbms_random.value  ) 
          where rownum <= 4
My question is how can I populate the locations part of the access_history table with random values from existing locations records. For the access_date I want to populate that with SYSDATE, which isn't in any of the tables.

This is ONLY test data and I'm not worried about performance issues as I know there is overhead with DBMS_RANDOM.

Thanks in advance to all who answer.

My data should look something like this:

 
   SELECT * from access_history 
    ORDER BY employee_id, access_date 

1, 'AAA1', 101,  '06212020 21:02:04', 0    2, 'BBB2', 103,  '06212020 21:22:54', 0  
1, 'AAA1', 104,  '06222020 01:13:11', 0  4, 'DDD4', 105,  '06212020 05:29:22', 0  3, 'CCC3', 102,  '06212020 08:42:34',0
   
    CREATE TABLE employees
    (
       employee_id NUMBER(6),
       first_name VARCHAR2(20),
       last_name VARCHAR2(25) NOT NULL,
       card_num varchar2(10)  NOT NULL
    );
       
    ALTER TABLE employees
         ADD ( CONSTRAINT employee_id_pk
       PRIMARY KEY (employee_id));


    Insert into employees values (1, 'Mike', 'Jones', 'AAA1');

    Insert into employees values (2, 'Jane', 'Doe', 'BBB2');


    Insert into employees values (3, 'Paul', 'Smith', 'CCC3');

    Insert into employees values (4, 'John', 'Henry', 'DDD4');



     Create table locations(
       location_id NUMBER(4),
       location_name varchar2(30),
       location_type char(1));
    
     -- A=access T=Time & Attendance 
     
    ALTER TABLE locations 
         ADD ( CONSTRAINT lication_id_pk
       PRIMARY KEY (location_id));



    Insert into locations values (101, 'South Front Door 1', 'T');
  
      Insert into locations values (102, 'South Front Door 2', 'T');
  
      Insert into locations values (103, 'East Back Door 1', 'T');
 
       Insert into locations values (104, 'East Back Door 2', 'T');
  
       Insert into locations values (105,'Computer Room', 'A');
 
       Insert into locations values (106,'1st Floor North',  'A');


    Create table access_history(
       employee_id NUMBER(6), 
       card_num varchar2(10),
       location_id number(4),
       access_date date,
       processed NUMBER(1) default 0
    );

Re: Creating a record from existing random records [message #681145 is a reply to message #681142] Fri, 19 June 2020 10:59 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You probably need to use the SAMPLE clause of SELECT. To get a random fifth of a table:
orclz> select * from emp sample(20);

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 1981-06-09:00:00:00       2450                    10
      7844 TURNER     SALESMAN        7698 1981-09-08:00:00:00       1500          0         30
      7902 FORD       ANALYST         7566 1981-12-03:00:00:00       3000                    20
I've no idea what the algorithm is, there are all sorts of options.
Re: Creating a record from existing random records [message #681146 is a reply to message #681142] Fri, 19 June 2020 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The SAMPLE clause may be a better option to select from the "employees" table.

To select a single row in the "locations" table, assuming the ids are consecutive you can use trunc(dbms_random.value(min_location_id,max_location_id) where min and max are previously computed.

In the end, something like (here I put 99.999% for the sample given the small test case):
SQL> INSERT INTO access_history
  2  WITH
  3    minmax AS (SELECT MIN(location_id) minl, MAX(location_id) maxl FROM locations),
  4    emp AS (SELECT e.employee_id, e.card_num, ROWNUM rn FROM employees sample (99.999) e),
  5    loc AS (SELECT TRUNC(dbms_random.value(m.minl,m.maxl)) loc_id, ROWNUM rn
  6            FROM minmax m CONNECT BY LEVEL <= (SELECT COUNT(*) FROM emp))
  7  SELECT e.employee_id, e.card_num, l.loc_id, sysdate, 0
  8  FROM emp e, loc l
  9  WHERE e.rn = l.rn
 10  /

4 rows created.

SQL> select * from access_history;
EMPLOYEE_ID CARD_NUM   LOCATION_ID ACCESS_DATE          PROCESSED
----------- ---------- ----------- ------------------- ----------
          1 AAA1               105 19/06/2020 17:59:32          0
          2 BBB2               102 19/06/2020 17:59:32          0
          3 CCC3               103 19/06/2020 17:59:32          0
          4 DDD4               103 19/06/2020 17:59:32          0

4 rows selected.
Re: Creating a record from existing random records [message #681147 is a reply to message #681146] Fri, 19 June 2020 12:22 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
I am always open to new ideas such as the SAMPLE clause, which I never used. I am curious why not use DBMS_RANDOM.VALUE like I did in my example.

Its fine to have the same empid and or location_id as part of the result. In fact, my example output shows it with employee_id=1.

Note, location_id may or may not be consecutive. Can you explain why that would make a difference.

Thank you both for responding
Re: Creating a record from existing random records [message #681148 is a reply to message #681147] Fri, 19 June 2020 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I am curious why not use DBMS_RANDOM.VALUE like I did in my example.

Just SAMPLE is built-in and then much more efficient, it accesses only (about) the number of rows you specified when the dbms_random trick has to access the whole table.

Quote:
Note, location_id may or may not be consecutive. Can you explain why that would make a difference.

If they are not consecutive the expression "dbms_random.value(m.minl,m.maxl)" may return a value that is not a location id and then the query has to be rewritten.

Re: Creating a record from existing random records [message #681149 is a reply to message #681148] Fri, 19 June 2020 13:46 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
I noticed that there is only 1 record for each employee. There are times when no records or N number of records, usually the later is the case.

I tried adding another CONNECT by LEVEL before the SELECT and got an error 'expecting select'. I than tried adding x 10 to the count(*) and still only 1 record per employee. Other than running the query multiple times or wrapping it in a loop how can I get more than 1 record per employee. I would actually prefer a random number access_history records (between 0-15) for each employee to stress test the application and find any bugs before this project goes live.



As for the consecutive location_id I will try to deal with that later as it's not a priority yet.

FYI, I an a DBA who got thrown this project as several of our developers are out. So I apologize for the many questions as I am far from a SQL expert. Thanks for your patience and expertise

Thanks for your help and patience
Re: Creating a record from existing random records [message #681150 is a reply to message #681149] Fri, 19 June 2020 14:10 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I shall defer to others who write much better SQL than me. Except to say that a relational engineer will NEVER say "record" when he means "row". I hope you do not refer to "columns" as "fields" Smile

Hope you get it fixed before your weekend.
Re: Creating a record from existing random records [message #681151 is a reply to message #681149] Fri, 19 June 2020 14:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a starter:
SQL> WITH
  2    emp AS (SELECT e.employee_id, e.card_num, ROWNUM rn FROM employees SAMPLE (99.999) e),
  3    numbers AS (SELECT dbms_random.value(1,15) val, ROWNUM rn
  4                FROM dual CONNECT BY LEVEL <= (SELECT COUNT(*) FROM emp)),
  5    emps AS (SELECT e.employee_id, e.card_num, ROWNUM rn
  6                    , (SELECT val FROM numbers n WHERE n.rn = e.rn) nop
  7             FROM emp e,
  8                  TABLE(CAST(MULTISET(
  9                    SELECT LEVEL lvl FROM dual
 10                    CONNECT BY LEVEL <= (SELECT val FROM numbers n WHERE n.rn = e.rn))
 11                             AS sys.odciNumberList))),
 12    minmax AS (SELECT MIN(location_id) minl, MAX(location_id) maxl FROM locations),
 13    loc AS (SELECT TRUNC(dbms_random.value(m.minl,m.maxl)) loc_id, ROWNUM rn
 14            FROM minmax m CONNECT BY LEVEL <= (SELECT COUNT(*) FROM emps)
 15            )
 16  SELECT e.employee_id, e.card_num, l.loc_id, sysdate, 0
 17  FROM emps e, loc l
 18  WHERE e.rn = l.rn
 19  /
EMPLOYEE_ID CARD_NUM       LOC_ID SYSDATE                      0
----------- ---------- ---------- ------------------- ----------
          1 AAA1              104 19/06/2020 21:49:20          0
          1 AAA1              105 19/06/2020 21:49:20          0
          1 AAA1              105 19/06/2020 21:49:20          0
          1 AAA1              102 19/06/2020 21:49:20          0
          1 AAA1              101 19/06/2020 21:49:20          0
          1 AAA1              104 19/06/2020 21:49:20          0
          1 AAA1              105 19/06/2020 21:49:20          0
          1 AAA1              102 19/06/2020 21:49:20          0
          1 AAA1              101 19/06/2020 21:49:20          0
          1 AAA1              104 19/06/2020 21:49:20          0
          1 AAA1              102 19/06/2020 21:49:20          0
          1 AAA1              103 19/06/2020 21:49:20          0
          2 BBB2              102 19/06/2020 21:49:20          0
          3 CCC3              101 19/06/2020 21:49:20          0
          3 CCC3              103 19/06/2020 21:49:20          0
          3 CCC3              102 19/06/2020 21:49:20          0
          3 CCC3              101 19/06/2020 21:49:20          0
          3 CCC3              105 19/06/2020 21:49:20          0
          3 CCC3              101 19/06/2020 21:49:20          0
          3 CCC3              104 19/06/2020 21:49:20          0
          4 DDD4              103 19/06/2020 21:49:20          0
          4 DDD4              105 19/06/2020 21:49:20          0
          4 DDD4              105 19/06/2020 21:49:20          0
          4 DDD4              103 19/06/2020 21:49:20          0
          4 DDD4              105 19/06/2020 21:49:20          0
          4 DDD4              103 19/06/2020 21:49:20          0

26 rows selected.

SQL> /
EMPLOYEE_ID CARD_NUM       LOC_ID SYSDATE                      0
----------- ---------- ---------- ------------------- ----------
          1 AAA1              105 19/06/2020 21:49:21          0
          1 AAA1              105 19/06/2020 21:49:21          0
          1 AAA1              103 19/06/2020 21:49:21          0
          2 BBB2              103 19/06/2020 21:49:21          0
          2 BBB2              102 19/06/2020 21:49:21          0
          2 BBB2              103 19/06/2020 21:49:21          0
          2 BBB2              101 19/06/2020 21:49:21          0
          2 BBB2              103 19/06/2020 21:49:21          0
          3 CCC3              101 19/06/2020 21:49:21          0
          3 CCC3              102 19/06/2020 21:49:21          0
          3 CCC3              103 19/06/2020 21:49:21          0
          3 CCC3              102 19/06/2020 21:49:21          0
          3 CCC3              103 19/06/2020 21:49:21          0
          3 CCC3              104 19/06/2020 21:49:21          0
          3 CCC3              101 19/06/2020 21:49:21          0
          4 DDD4              101 19/06/2020 21:49:21          0
          4 DDD4              101 19/06/2020 21:49:21          0
          4 DDD4              102 19/06/2020 21:49:21          0
          4 DDD4              103 19/06/2020 21:49:21          0

19 rows selected.
The multiplier factor is done with this part: "CONNECT BY LEVEL <= (SELECT val FROM numbers n WHERE n.rn = e.rn)"

[Updated on: Fri, 19 June 2020 14:53]

Report message to a moderator

Re: Creating a record from existing random records [message #681152 is a reply to message #681151] Fri, 19 June 2020 18:15 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
Thanks looks good so far. I need to read up on sys.odciNumberList and MULTISET. In addition, read through and try to understand the code.

Curious, does the problem with non consecutive location_id still exist?

For fun I am trying to solve the problem with PSSQL as that comes easier to me and it's a good learning experience.

Thanks again for your expertise and patience. I've learned a lot from you. I'll have to put you on my xmas card list Smile
Re: Creating a record from existing random records [message #681153 is a reply to message #681152] Sat, 20 June 2020 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

MULTISET is a keyword converting a cursor to a collection.
CAST tells which type of collection it is.
sys.odciNumberList is a predefined type for denoting a collection of numbers (exactly Varying Array(32767) of NUMBER).
TABLE is a syntactical keyword for the optimizer to tell it has to handle what is inside the parentheses as a collection.
All this stuff has been simplified in 12c with the keyword LATERAL.

Quote:
does the problem with non consecutive location_id still exist?

Yes, I didn't treat this issue.

In the query:

  • "emp" subquery returns the sample of rows from "employees" table
  • "numbers" generates a number between 1 and 15 for each "emp" row which will be the number of times this row will be duplicated
  • "emps" is the combination of both
  • "minmax" computes the minimum and maximum value of "location_id" for the next subquery
  • "loc" generates a location_id (between min and max) for each "emps" row
  • the final query merges "emps" and "loc" to generate the result

[Updated on: Sat, 20 June 2020 07:50]

Report message to a moderator

Re: Creating a record from existing random records [message #681154 is a reply to message #681153] Sat, 20 June 2020 03:16 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
Thank you for the detailed explanation and documentation.

As for the sequential location_id issue would it make sense to chose a random location from something like this.

where location_id in ( select location_id from locations)

I suspect that would cause performance issues if done every time but could it be done once than randomly pick from those values. Do see any issues with that approach? Would that mean a major rewrite? If problematic can you briefly explain why? Sorry for all the questions.

As this project hasn't been released yet, I can make changes like adding indexes if it will help the cause
Re: Creating a record from existing random records [message #681155 is a reply to message #681154] Sat, 20 June 2020 03:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it will lead to performances issue and it is not so easy to modify the query in this way.

But if "locations" table is not too big you can number each row as you did for "employees":
SQL> WITH
  2    emp AS (SELECT e.employee_id, e.card_num, ROWNUM rn FROM employees SAMPLE (99.999) e),
  3    numbers AS (SELECT dbms_random.value(1,15) val, ROWNUM rn
  4                FROM dual CONNECT BY LEVEL <= (SELECT COUNT(*) FROM emp)),
  5    emps AS (SELECT e.employee_id, e.card_num, ROWNUM rn
  6                    , (SELECT val FROM numbers n WHERE n.rn = e.rn) nop
  7             FROM emp e,
  8                  TABLE(CAST(MULTISET(
  9                    SELECT LEVEL lvl FROM dual
 10                    CONNECT BY LEVEL <= (SELECT val FROM numbers n WHERE n.rn = e.rn))
 11                             AS sys.odciNumberList))),
 12    loc0 AS (SELECT location_id,
 13                    ROW_NUMBER() OVER (ORDER BY dbms_random.value) rn,
 14                    COUNT(*) OVER() cnt
 15             FROM locations),
 16    loc AS (SELECT TRUNC(dbms_random.value(1,m.cnt)) loc_row, ROWNUM rn
 17            FROM (SELECT 1, cnt FROM loc0 WHERE ROWNUM = 1) m
 18            CONNECT BY LEVEL <= (SELECT COUNT(*) FROM emps))
 19  SELECT e.employee_id, e.card_num, l0.location_id, sysdate, 0
 20  FROM emps e, loc l, loc0 l0
 21  WHERE e.rn = l.rn
 22    AND l.loc_row = l0.rn
 23  /
EMPLOYEE_ID CARD_NUM   LOCATION_ID SYSDATE                      0
----------- ---------- ----------- ------------------- ----------
          1 AAA1               101 20/06/2020 10:31:40          0
          1 AAA1               102 20/06/2020 10:31:40          0
          1 AAA1               106 20/06/2020 10:31:40          0
          1 AAA1               103 20/06/2020 10:31:40          0
          1 AAA1               104 20/06/2020 10:31:40          0
          1 AAA1               106 20/06/2020 10:31:40          0
          1 AAA1               106 20/06/2020 10:31:40          0
          2 BBB2               102 20/06/2020 10:31:40          0
          2 BBB2               106 20/06/2020 10:31:40          0
          2 BBB2               104 20/06/2020 10:31:40          0
          2 BBB2               101 20/06/2020 10:31:40          0
          2 BBB2               103 20/06/2020 10:31:40          0
          2 BBB2               101 20/06/2020 10:31:40          0
          2 BBB2               104 20/06/2020 10:31:40          0
          3 CCC3               101 20/06/2020 10:31:40          0
          3 CCC3               106 20/06/2020 10:31:40          0
          3 CCC3               101 20/06/2020 10:31:40          0
          3 CCC3               101 20/06/2020 10:31:40          0
          3 CCC3               106 20/06/2020 10:31:40          0
          3 CCC3               103 20/06/2020 10:31:40          0
          4 DDD4               101 20/06/2020 10:31:40          0

21 rows selected.

SQL> /
EMPLOYEE_ID CARD_NUM   LOCATION_ID SYSDATE                      0
----------- ---------- ----------- ------------------- ----------
          1 AAA1               104 20/06/2020 10:31:54          0
          1 AAA1               102 20/06/2020 10:31:54          0
          1 AAA1               105 20/06/2020 10:31:54          0
          1 AAA1               106 20/06/2020 10:31:54          0
          1 AAA1               104 20/06/2020 10:31:54          0
          1 AAA1               102 20/06/2020 10:31:54          0
          2 BBB2               106 20/06/2020 10:31:54          0
          3 CCC3               105 20/06/2020 10:31:54          0
          3 CCC3               105 20/06/2020 10:31:54          0
          4 DDD4               101 20/06/2020 10:31:54          0
          4 DDD4               105 20/06/2020 10:31:54          0
          4 DDD4               106 20/06/2020 10:31:54          0
          4 DDD4               101 20/06/2020 10:31:54          0
          4 DDD4               101 20/06/2020 10:31:54          0
          4 DDD4               102 20/06/2020 10:31:54          0
          4 DDD4               105 20/06/2020 10:31:54          0
          4 DDD4               106 20/06/2020 10:31:54          0
          4 DDD4               105 20/06/2020 10:31:54          0
          4 DDD4               102 20/06/2020 10:31:54          0
          4 DDD4               105 20/06/2020 10:31:54          0
          4 DDD4               104 20/06/2020 10:31:54          0
          4 DDD4               105 20/06/2020 10:31:54          0
          4 DDD4               101 20/06/2020 10:31:54          0

23 rows selected.

[Updated on: Sat, 20 June 2020 07:54]

Report message to a moderator

Re: Creating a record from existing random records [message #681156 is a reply to message #681155] Sat, 20 June 2020 08:33 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
Thank you for all your expertise and time, you taught me plenty and I'm grateful. Your solution, why very eloquent is over my head so I simplified it for myself with PSSQL.

Since this is data used to stress test an application I'm not worried about my solution being less efficient. Below is my solution if your curious I randomly create an employees table w6 50 records and a locations table with 10 records.

If you're curious here is what I did.


ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

-- create and populate an employee 
-- table with 50 rows
   
    CREATE TABLE employees AS
    SELECT level AS employee_id,
    
    CASE round(dbms_random.value(1,20)) 
            WHEN 1 THEN 'Albert' 
            WHEN 2 THEN 'Tom' 
            WHEN 3 THEN 'Anna'
            WHEN 4 THEN 'Ty' 
            WHEN 5 THEN 'Andy' 
            WHEN 6 THEN 'Thomas' 
            WHEN 7 THEN 'Alan'
            WHEN 8 THEN 'Tara' 
            WHEN 9 THEN 'Cheryl' 
            WHEN 10 THEN 'Ed' 
            WHEN 11 THEN 'Steve'
            WHEN 12 THEN 'Mel' 
            WHEN 13 THEN 'Micheal' 
            WHEN 14 THEN 'Ron' 
            WHEN 15 THEN 'Donald'
            WHEN 16 THEN 'Donny' 
            WHEN 17 THEN 'Racheal' 
            WHEN 18 THEN 'Debbie' 
            WHEN 19 THEN 'Madison'
            WHEN  20 THEN 'Danny' 
         END AS first_name,

CASE round(dbms_random.value(1,20)) 
            WHEN 1 THEN 'Andrews' 
            WHEN 2 THEN 'Thorton' 
            WHEN 3 THEN 'Smith'
            WHEN 4 THEN 'Jones' 
            WHEN 5 THEN 'Ott' 
            WHEN 6 THEN 'Stevens' 
            WHEN 7 THEN 'Feldman'
            WHEN 8 THEN 'Stein' 
            WHEN 9 THEN 'Ross' 
            WHEN 10 THEN 'Eden' 
            WHEN 11 THEN 'Saltzman'
            WHEN 12 THEN 'Kramer'
            WHEN 13 THEN 'Monroe' 
            WHEN 14 THEN 'Hanks' 
            WHEN 15 THEN 'Dunn'
            WHEN 16 THEN 'Dunbar' 
            WHEN 17 THEN 'Rucker' 
            WHEN 18 THEN 'Silverberg' 
            WHEN 19 THEN 'Daniels'
            WHEN  20 THEN 'Kahn' 
         END AS last_name, 
        
    dbms_random.string('X',        dbms_random.value(5, 10))  AS card_num

    FROM   dual
    CONNECT BY level <= 50;


    ALTER TABLE employees
         ADD ( CONSTRAINT employee_id_pk
       PRIMARY KEY (employee_id));

    -- create and populate a location 
    -- table with 10 rows. Randomly
    -- make some types 'A' for access
    -- or 'T' for time and attendance 

CREATE TABLE locations AS
SELECT level AS location_id,
       'Door ' || level AS location_name,

CASE round(dbms_random.value(1,2)) 
            WHEN 1 THEN 'A' 
            WHEN 2 THEN 'T' 
         END AS location_type

FROM   dual
CONNECT BY level <= 10;


  ALTER TABLE locations 
         ADD ( CONSTRAINT location_id_pk
       PRIMARY KEY (location_id));


    Create table access_history(
       employee_id NUMBER(6), 
       card_num varchar2(10),
       location_id number(4),
       access_date date,
       processed NUMBER(1) default 0
    );

--  Randomly populate 
-- access_history with random
-- employee and location
-- records. Type 'T' rows and 
-- processed=0 rows will be used to
-- pair the records to create 
-- emp_attendance records in another
-- process. 
-- 
-- this code is easier to understand 
-- above example. Need to test this!!

declare
  v_loc number;
  v_dt date;
  v_counter integer := 0;
begin
  -- take two random employees
  for emp in (select employee_id, card_num 
              from (select employee_id, card_num 
                    from employees order by dbms_random.value ) 
              where rownum <=  (SELECT COUNT(*)  FROM employees)) loop

    -- set starting date
    v_dt  := trunc(sysdate) +  dbms_random.value (0, 1) + dbms_random.value (0, .75);


    -- for each employee generate 3 to 25 rows
    for i in 1..dbms_random.value(3, 25) loop

       If MOD (v_counter, 10) = 0
       THEN 
       
          -- reset time so data 
          --  is not far in the future 
          -- only use for test data.

            v_dt :=  trunc(sysdate) +   dbms_random.value (0, 1) + dbms_random.value (0, .75);

        ELSE
    
        -- increase last used date by random 10 to 500 minutes
          
       v_dt := v_dt + dbms_random.value(10, 500)/(24*60);

       END IF;
 
     -- get random location
      select location_id 
        into v_loc 
        from (select location_id from locations order by dbms_random.value) 
        where rownum = 1;

        insert into access_history (employee_id, card_num, location_id, access_date)
        values (emp.employee_id, emp.card_num, v_loc, v_dt);

     v_counter := v_counter + 1;
    end loop;
  end loop;
end;


     CREATE TABLE  emp_attendance    
      (seq_num NUMBER(10),
       employee_id NUMBER(6),
       start_date DATE,
       end_date DATE,
       create_date DATE DEFAULT SYSDATE
       );
   
       Create sequence emp_attendance_seq;


  -- create emp_attendance records 
  -- from access_history table. Once
  -- rows are created update processed 
  -- flag so subsequent runs will not
  -- pick up the same records again.

 declare
  type hist_rec is record (
    employee_id int,
    start_date  timestamp,
    end_date    timestamp
  );
  type hist_rec_arr 
    is table of hist_rec 
    index by pls_integer;
  
  hist_recs hist_rec_arr;
begin
  
  with prep ( employee_id, start_date, rn, end_date) as (
    select employee_id, access_date
         , row_number()      over (partition by card_num order by access_date)
         , lead(access_date) over (partition by card_num order by access_date)
    from   access_history
    where  location_id in ( 
      select location_id
      from   locations
      where  location_type = 'T'
    ) 
    and processed = 0
  )
   select employee_id,
          start_date,
          nvl(end_date, start_date)
   bulk collect into hist_recs
   from   prep
   where  mod(rn, 2) = 1;

forall i in 1 .. hist_recs.count
insert into emp_attendance (seq_num, employee_id, start_date, end_Date)
    values ( emp_attendance_seq.nextval, hist_recs(i).employee_id, 
      hist_recs(i).start_date, hist_recs(i).end_date
    );
    
  forall i in 1 .. hist_recs.count
    update access_history
    set    processed = 1
    where  employee_id = hist_recs(i).employee_id
    and    access_date = hist_recs(i).start_date
    and    access_date = hist_recs(i).end_date;

end;

-- sum up some attendance records

select  e.employee_id,
           e.last_name, 
           e.first_name,

      LPAD(trunc(sum(a.end_date - a.start_date) * 24), 2, '0') hours,
    
    LPAD(trunc(mod(sum(a.end_date - a.start_date) * 24 * 60,60)), 2, '0') minutes,

       LPAD(round(mod(sum(a.end_date - a.start_date) * 24 * 60 * 60,60)) , 2, '0') seconds

  from  employees e,
        emp_attendance a

  where 
    a.employee_id = e.employee_id
    and start_date between  TRUNC(SYSDATE)
and TRUNC(SYSDATE +7)+ (1-1/24/60/60) -- add 23:59:59

  group by e.employee_id,
         e.last_name, e.first_name 
      order by e.employee_id,
         e.last_name, e.first_name;
  
Syntax error on INSERT (3 merged) [message #681178 is a reply to message #681142] Tue, 23 June 2020 07:04 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
While trying to INSERT access_history records through CTE I am getting a syntax error. I was hoping a pair of fresh eyes can help me out.

Thanks in advance to all who respond.

Test case

[code]

ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

-- create and populate an employee
-- table with 50 rows

CREATE TABLE employees AS
SELECT level AS employee_id,

CASE round(dbms_random.value(1,20))
WHEN 1 THEN 'Albert'
WHEN 2 THEN 'Tom'
WHEN 3 THEN 'Anna'
WHEN 4 THEN 'Ty'
WHEN 5 THEN 'Andy'
WHEN 6 THEN 'Thomas'
WHEN 7 THEN 'Alan'
WHEN 8 THEN 'Tara'
WHEN 9 THEN 'Cheryl'
WHEN 10 THEN 'Ed'
WHEN 11 THEN 'Steve'
WHEN 12 THEN 'Mel'
WHEN 13 THEN 'Micheal'
WHEN 14 THEN 'Ron'
WHEN 15 THEN 'Donald'
WHEN 16 THEN 'Donny'
WHEN 17 THEN 'Racheal'
WHEN 18 THEN 'Debbie'
WHEN 19 THEN 'Madison'
WHEN 20 THEN 'Danny'
END AS first_name,

CASE round(dbms_random.value(1,20))
WHEN 1 THEN 'Andrews'
WHEN 2 THEN 'Thorton'
WHEN 3 THEN 'Smith'
WHEN 4 THEN 'Jones'
WHEN 5 THEN 'Ott'
WHEN 6 THEN 'Stevens'
WHEN 7 THEN 'Feldman'
WHEN 8 THEN 'Stein'
WHEN 9 THEN 'Ross'
WHEN 10 THEN 'Eden'
WHEN 11 THEN 'Saltzman'
WHEN 12 THEN 'Kramer'
WHEN 13 THEN 'Monroe'
WHEN 14 THEN 'Hanks'
WHEN 15 THEN 'Dunn'
WHEN 16 THEN 'Dunbar'
WHEN 17 THEN 'Rucker'
WHEN 18 THEN 'Silverberg'
WHEN 19 THEN 'Daniels'
WHEN 20 THEN 'Kahn'
END AS last_name,

dbms_random.string('X', dbms_random.value(5, 10)) AS card_num

FROM dual
CONNECT BY level <= 50;


ALTER TABLE employees
ADD ( CONSTRAINT employee_id_pk
PRIMARY KEY (employee_id));

-- create and populate a location
-- table with 10 rows. Randomly
-- make some types 'A' for access
-- 'T' for time and attendance,
-- 'G' for guard tour.

CREATE TABLE locations AS
SELECT level AS location_id,
'Door ' || level AS location_name,

CASE round(dbms_random.value(1,3))
WHEN 1 THEN 'A'
WHEN 2 THEN 'T'
WHEN 3 THEN 'G'
END AS location_type

FROM dual
CONNECT BY level <= 25;


ALTER TABLE locations
ADD ( CONSTRAINT location_id_pk
PRIMARY KEY (location_id));


create table access_history(
employee_id NUMBER(6),
card_num varchar2(10),
location_id number(4),
access_date date,
processed NUMBER(1) default 0
);


INSERT into access_history
(employee_id,
card_num,
location_id,
access_date)
with all_combos as
( select e.*, l.*
from employees e, locations l
)
select *
from (
select employee_id, card_num,
location_id, sysdate
from all_combos
order by dbms_random.value
)
select employee_id, card_num,
location_id, access_date
from all_combos;
[code]
Syntax error on INSERT i [message #681179 is a reply to message #681178] Tue, 23 June 2020 07:05 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
While trying to INSERT access_history records through CTE I am getting a syntax error. I was hoping a pair of fresh eyes can help me out.

Thanks in advance to all who respond.

Test case

[code]

ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

-- create and populate an employee
-- table with 50 rows

CREATE TABLE employees AS
SELECT level AS employee_id,

CASE round(dbms_random.value(1,20))
WHEN 1 THEN 'Albert'
WHEN 2 THEN 'Tom'
WHEN 3 THEN 'Anna'
WHEN 4 THEN 'Ty'
WHEN 5 THEN 'Andy'
WHEN 6 THEN 'Thomas'
WHEN 7 THEN 'Alan'
WHEN 8 THEN 'Tara'
WHEN 9 THEN 'Cheryl'
WHEN 10 THEN 'Ed'
WHEN 11 THEN 'Steve'
WHEN 12 THEN 'Mel'
WHEN 13 THEN 'Micheal'
WHEN 14 THEN 'Ron'
WHEN 15 THEN 'Donald'
WHEN 16 THEN 'Donny'
WHEN 17 THEN 'Racheal'
WHEN 18 THEN 'Debbie'
WHEN 19 THEN 'Madison'
WHEN 20 THEN 'Danny'
END AS first_name,

CASE round(dbms_random.value(1,20))
WHEN 1 THEN 'Andrews'
WHEN 2 THEN 'Thorton'
WHEN 3 THEN 'Smith'
WHEN 4 THEN 'Jones'
WHEN 5 THEN 'Ott'
WHEN 6 THEN 'Stevens'
WHEN 7 THEN 'Feldman'
WHEN 8 THEN 'Stein'
WHEN 9 THEN 'Ross'
WHEN 10 THEN 'Eden'
WHEN 11 THEN 'Saltzman'
WHEN 12 THEN 'Kramer'
WHEN 13 THEN 'Monroe'
WHEN 14 THEN 'Hanks'
WHEN 15 THEN 'Dunn'
WHEN 16 THEN 'Dunbar'
WHEN 17 THEN 'Rucker'
WHEN 18 THEN 'Silverberg'
WHEN 19 THEN 'Daniels'
WHEN 20 THEN 'Kahn'
END AS last_name,

dbms_random.string('X', dbms_random.value(5, 10)) AS card_num

FROM dual
CONNECT BY level <= 50;


ALTER TABLE employees
ADD ( CONSTRAINT employee_id_pk
PRIMARY KEY (employee_id));

-- create and populate a location
-- table with 10 rows. Randomly
-- make some types 'A' for access
-- 'T' for time and attendance,
-- 'G' for guard tour.

CREATE TABLE locations AS
SELECT level AS location_id,
'Door ' || level AS location_name,

CASE round(dbms_random.value(1,3))
WHEN 1 THEN 'A'
WHEN 2 THEN 'T'
WHEN 3 THEN 'G'
END AS location_type

FROM dual
CONNECT BY level <= 25;


ALTER TABLE locations
ADD ( CONSTRAINT location_id_pk
PRIMARY KEY (location_id));


create table access_history(
employee_id NUMBER(6),
card_num varchar2(10),
location_id number(4),
access_date date,
processed NUMBER(1) default 0
);


INSERT into access_history
(employee_id,
card_num,
location_id,
access_date)
with all_combos as
( select e.*, l.*
from employees e, locations l
)
select *
from (
select employee_id, card_num,
location_id, sysdate
from all_combos
order by dbms_random.value
)
select employee_id, card_num,
location_id, access_date
from all_combos;
[code]
Syntax error on INSERT [message #681180 is a reply to message #681178] Tue, 23 June 2020 07:07 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
While trying to INSERT access_history records through CTE I am getting a syntax error. I was hoping a pair of fresh eyes can help me out.

Thanks in advance to all who respond.

Test case


   ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

   -- create and populate an employee 
   -- table with 50 rows
   
    CREATE TABLE employees AS
    SELECT level AS employee_id,
    
    CASE round(dbms_random.value(1,20)) 
            WHEN 1 THEN 'Albert' 
            WHEN 2 THEN 'Tom' 
            WHEN 3 THEN 'Anna'
            WHEN 4 THEN 'Ty' 
            WHEN 5 THEN 'Andy' 
            WHEN 6 THEN 'Thomas' 
            WHEN 7 THEN 'Alan'
            WHEN 8 THEN 'Tara' 
            WHEN 9 THEN 'Cheryl' 
            WHEN 10 THEN 'Ed' 
            WHEN 11 THEN 'Steve'
            WHEN 12 THEN 'Mel' 
            WHEN 13 THEN 'Micheal' 
            WHEN 14 THEN 'Ron' 
            WHEN 15 THEN 'Donald'
            WHEN 16 THEN 'Donny' 
            WHEN 17 THEN 'Racheal' 
            WHEN 18 THEN 'Debbie' 
            WHEN 19 THEN 'Madison'
            WHEN  20 THEN 'Danny' 
         END AS first_name,

    CASE  round(dbms_random.value(1,20)) 
            WHEN 1 THEN 'Andrews' 
            WHEN 2 THEN 'Thorton' 
            WHEN 3 THEN 'Smith'
            WHEN 4 THEN 'Jones' 
            WHEN 5 THEN 'Ott' 
            WHEN 6 THEN 'Stevens' 
            WHEN 7 THEN 'Feldman'
            WHEN 8 THEN 'Stein' 
            WHEN 9 THEN 'Ross' 
            WHEN 10 THEN 'Eden' 
            WHEN 11 THEN 'Saltzman'
            WHEN 12 THEN 'Kramer'
            WHEN 13 THEN 'Monroe' 
            WHEN 14 THEN 'Hanks' 
            WHEN 15 THEN 'Dunn'
            WHEN 16 THEN 'Dunbar' 
            WHEN 17 THEN 'Rucker' 
            WHEN 18 THEN 'Silverberg' 
            WHEN 19 THEN 'Daniels'
            WHEN  20 THEN 'Kahn' 
         END AS last_name, 
        
    dbms_random.string('X',        dbms_random.value(5, 10))  AS card_num

    FROM   dual
    CONNECT BY level <= 50;


    ALTER TABLE employees
         ADD ( CONSTRAINT employee_id_pk
       PRIMARY KEY (employee_id));

    -- create and populate a location 
    -- table with 10 rows. Randomly
    -- make some types 'A' for access
    -- 'T' for time and attendance,
    -- 'G' for guard tour.

    CREATE TABLE locations AS
    SELECT level AS location_id,
       'Door ' || level AS location_name,

    CASE round(dbms_random.value(1,3)) 
            WHEN 1 THEN 'A' 
            WHEN 2 THEN 'T' 
            WHEN 3 THEN 'G' 
         END AS location_type

    FROM   dual
    CONNECT BY level <= 25;


     ALTER TABLE locations 
         ADD ( CONSTRAINT location_id_pk
       PRIMARY KEY (location_id));


    create table access_history(
       employee_id NUMBER(6), 
       card_num varchar2(10),
       location_id number(4),
       access_date date,
       processed NUMBER(1) default 0
    );


    INSERT into access_history 
    (employee_id,
     card_num, 
     location_id, 
    access_date)
    with all_combos as
       ( select e.*, l.*
         from   employees e, locations l
       )
       select *
       from (
         select employee_id, card_num, 
                 location_id,   sysdate  
          from   all_combos
          order by dbms_random.value
      )
      select employee_id,  card_num,
                  location_id, access_date 
        from all_combos;
Re: Syntax error on INSERT [message #681181 is a reply to message #681180] Tue, 23 June 2020 07:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL>   with all_combos as
  2         ( select e.*, l.*
  3           from   employees e, locations l
  4         )
  5         select *
  6         from (
  7           select employee_id, card_num,
  8                   location_id,   sysdate
  9            from   all_combos
 10            order by dbms_random.value
 11        )
 12        select employee_id,  card_num,
 13                    location_id, access_date
 14          from all_combos;
      select employee_id,  card_num,
      *
ERROR at line 12:
ORA-00933: SQL command not properly ended
All subqueries must be named.

Is this not the following you wanted to do (removing the last lines)?
SQL>    INSERT into access_history
  2      (employee_id,
  3       card_num,
  4       location_id,
  5      access_date)
  6      with all_combos as
  7         ( select e.*, l.*
  8           from   employees e, locations l
  9         )
 10         select *
 11         from (
 12           select employee_id, card_num,
 13                   location_id,   sysdate
 14            from   all_combos
 15            order by dbms_random.value
 16        )
 17  /

1250 rows created.
Not that a table is like a basket: there is no order in it, so "order by dbms_random.value" is pointless.
You choose the order when you query the table.



Re: Syntax error on INSERT (3 merged) [message #681183 is a reply to message #681178] Tue, 23 June 2020 10:03 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
Thanks so much, that is what I was expecting to do except for 1 caveat. I was trying to create a random number of rows 10-25 for each employee instead of a fixed number. Hence the random value in the original query.
Re: Syntax error on INSERT (3 merged) [message #681185 is a reply to message #681183] Tue, 23 June 2020 12:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You will not do it like that.
Refer to my query in your previous topic.

Re: Syntax error on INSERT (3 merged) [message #681186 is a reply to message #681185] Tue, 23 June 2020 15:01 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
I think I found something similar by connor McDonald. Will this do the trick? It looks like the CONNECT by level is a variable. I don't think I have the expertise to MERGE his suggestions into the code.

with
2 emps as
3 ( select level empid, dbms_random.value(5,20) children from dual connect by level <= 20 ),
4 empatt as
5 ( select e.empid , x.start_date, x.start_date+dbms_random.value(0,0.75) end_date
6 from emps e,
7 lateral(
8 select
9 trunc(sysdate)+dbms_random.value(1,30) start_date
10 from dual
11 connect by level <= e.children
12 ) x
13 )
14 select empid, count(*)
15 from empatt
16 group by empid
17 order by 1;
Re: Syntax error on INSERT (3 merged) [message #681187 is a reply to message #681186] Wed, 24 June 2020 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said in your previous topic, LATERAL is a 12c feature and you are in 11.2.
This is (about) the same query than mine.

Please format your post.

Note: I merge both topics as it is in fact the same question.

Re: Syntax error on INSERT (3 merged) [message #681191 is a reply to message #681187] Wed, 24 June 2020 03:52 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
To format code do I wrap it in html tags like <code> </code>

It sounds like you're saying I need to play with row_number and partion after the tables are been created with the cartesian product. Is that correct? If so, something like this

<code>


,row_number() over (partition by e.employee_id order by dbms_random.value(5,20)) as rn,
count(*) over (partition by e.employee_id) as cnt

</code>

Since we are moving to version 19 this fall, for academic purposes, how would the lateral command help me with this problem?
Re: Syntax error on INSERT (3 merged) [message #681192 is a reply to message #681191] Wed, 24 June 2020 04:05 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
fyi: BB tags use square brackets [] not angle brackets <> Smile

Using row number function to retrieve values [message #681216 is a reply to message #681142] Fri, 26 June 2020 18:26 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
In the CTE code below I am using trunc (dbms_random.value (1, 20)) as location_id


This takes advantage of the fact that location_ids are consecutive integers. If they were arbitrary values, dbms_random would not work as it may give me a location_id that doesn't exist. Can the cte code below be modified to use the row_number function to randomly pick a value from the locations table to overcome this limitation



    ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

    -- create and populate an employee 
    -- table with 50 rows
   
    CREATE TABLE employees AS
    SELECT level AS employee_id,
    
    CASE round(dbms_random.value(1,20)) 
            WHEN 1 THEN 'Albert' 
            WHEN 2 THEN 'Tom' 
            WHEN 3 THEN 'Anna'
            WHEN 4 THEN 'Ty' 
            WHEN 5 THEN 'Andy' 
            WHEN 6 THEN 'Thomas' 
            WHEN 7 THEN 'Alan'
            WHEN 8 THEN 'Tara' 
            WHEN 9 THEN 'Cheryl' 
            WHEN 10 THEN 'Ed' 
            WHEN 11 THEN 'Steve'
            WHEN 12 THEN 'Mel' 
            WHEN 13 THEN 'Micheal' 
            WHEN 14 THEN 'Ron' 
            WHEN 15 THEN 'Donald'
            WHEN 16 THEN 'Donny' 
            WHEN 17 THEN 'Racheal' 
            WHEN 18 THEN 'Debbie' 
            WHEN 19 THEN 'Madison'
            WHEN  20 THEN 'Danny' 
         END AS first_name,

    CASE  round(dbms_random.value(1,20)) 
            WHEN 1 THEN 'Andrews' 
            WHEN 2 THEN 'Thorton' 
            WHEN 3 THEN 'Smith'
            WHEN 4 THEN 'Jones' 
            WHEN 5 THEN 'Ott' 
            WHEN 6 THEN 'Stevens' 
            WHEN 7 THEN 'Feldman'
            WHEN 8 THEN 'Stein' 
            WHEN 9 THEN 'Ross' 
            WHEN 10 THEN 'Eden' 
            WHEN 11 THEN 'Saltzman'
            WHEN 12 THEN 'Kramer'
            WHEN 13 THEN 'Monroe' 
            WHEN 14 THEN 'Hanks' 
            WHEN 15 THEN 'Dunn'
            WHEN 16 THEN 'Dunbar' 
            WHEN 17 THEN 'Rucker' 
            WHEN 18 THEN 'Silverberg' 
            WHEN 19 THEN 'Daniels'
            WHEN  20 THEN 'Kahn' 
         END AS last_name, 
        
    dbms_random.string('X',        dbms_random.value(5, 10))  AS card_num

    FROM   dual
    CONNECT BY level <= 50;


    ALTER TABLE employees
         ADD ( CONSTRAINT employee_id_pk
       PRIMARY KEY (employee_id));

    
    CREATE TABLE locations AS
    SELECT level AS location_id,
       'Door ' || level AS location_name,

    CASE round(dbms_random.value(1,3)) 
            WHEN 1 THEN 'A' 
            WHEN 2 THEN 'T' 
            WHEN 3 THEN 'G' 
         END AS location_type

    FROM   dual
    CONNECT BY level <= 25;


     ALTER TABLE locations 
         ADD ( CONSTRAINT location_id_pk
       PRIMARY KEY (location_id));



with     rws    as
(

    select  level   as rn
    from   dual
    connect by  level  <= 25

)
,    emps    as
(

    select  /*+ materialize */
            e.*
    ,  round ( dbms_random.value (1, 25) ) as n
    from   employees e
    where  employee_id  <= 50

)

select    e.employee_id
,   e.card_num
,         trunc (dbms_random.value (1, 20))              as location_id

,         trunc (sysdate) + dbms_random.value (0, 2.75)  as access_date  

from      rws   r
join      emps  e   on     r.rn  <= e.n
order by  employee_id;

Re: Using row number function to retrieve values [message #681218 is a reply to message #681216] Fri, 26 June 2020 23:44 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I already answered this question in your previous topics.
Please stop creating new topics for the same question.

Previous Topic: ORA-00904: invalid identifier
Next Topic: Want to trigger the URL using UTL_HTTP
Goto Forum:
  


Current Time: Thu Mar 28 16:14:43 CDT 2024