I found the following code, which works fine. Could someone suggest a way how I can integrate the locations table into the cte so that I get a random location_id with every row. I suspect I need another union all but I'm unsure how to get the random location_id
In this example the locations table is consecutively ordered for ease of posting but that will not be the case all the time.
CREATE TABLE schedule_hdr AS
SELECT level AS schedule_id,
'Schedule ' || level AS schedule_name
FROM dual
CONNECT BY level <= 2;
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 random_times ( schedule_id, datetime, lvl ) AS (
SELECT schedule_id,
TRUNC(sysdate)
+
NUMTODSINTERVAL( FLOOR(DBMS_RANDOM.VALUE(0,23*60)), 'MINUTE' ),
1
FROM schedule_hdr
UNION ALL
SELECT schedule_id,
datetime + NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE(6,11)), 'MINUTE'),
lvl + 1
FROM random_times
WHERE lvl < 5
)
SELECT schedule_id,
datetime
FROM random_times
ORDER BY schedule_id, datetime;
Aucun commentaire:
Enregistrer un commentaire