dimanche 12 juillet 2020

Using a cte to retrieve a random record

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