I have the following INSERT statement, which works fine.
I also found some code on the internet that creates a random count of employee records, which works fine.
Can someone provide a solution that merges the random count code into the INSERT statement. Thanks to all who respond.
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,
trunc(sysdate) + dbms_random.value (0, 2) + dbms_random.value (0, .75)
from all_combos
order by dbms_random.value
);
with
emps as
( select level empid, dbms_random.value(5,20) children from dual connect by level <= 20 ),
empatt as
( select e.empid , x.start_date, x.start_date+dbms_random.value(0,0.75) end_date
from emps e,
lateral(
select
trunc(sysdate)+dbms_random.value(1,30) start_date
from dual
connect by level <= e.children
) x
)
select empid, count(*) from empatt group by empid order by 1;
Aucun commentaire:
Enregistrer un commentaire