Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Recursive CTE pulling values from another table

BeefStuAug 19 2021

I have a recursive CTE, which generates start and END dates for multiple dates, which is working as expected.

It will stop creating rows before the date crosses midnight or the count(*) of the locations table is exceeded.

With each row the CTE generates I want to associated a location_id. Note there should not be duplicate location_id before one of the stop gaps are reached.

There is no commonality between the start/end dates and the locations so a JOIN didn't seem possible to me. 

In the sample code below I tried a cross apply, which gave me the same location_id for each group generated by the recursive code so that isn't working either.

Can someone please suggest a way I can get a unique location_id from the locations table for each row.

Thanks in advance for your expertise and all who answer. My test CASE is below.

Current Result 

LOCATION_ID    BASE_DATE    START_DATE    END_DATE
1    08212021 00:00:00    08212021 23:16:00    08212021 23:21:00
1    08212021 00:00:00    08212021 23:26:00    08212021 23:31:00
1    08212021 00:00:00    08212021 23:36:00    08212021 23:41:00
…
…

Expected Result

LOCATION_ID    BASE_DATE    START_DATE    END_DATE
1    08212021 00:00:00    08212021 23:16:00    08212021 23:21:00
2    08212021 00:00:00    08212021 23:26:00    08212021 23:31:00 
3    08212021 00:00:00    08212021 23:36:00    08212021 23:41:00
…
...
ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

CREATE OR REPLACE TYPE nt_date IS TABLE OF DATE;
/

    CREATE OR REPLACE FUNCTION generate_dates_pipelined(
      p_from IN DATE,
      p_to   IN DATE
    )
    RETURN nt_date  PIPELINED   DETERMINISTIC
    IS
      v_start DATE := TRUNC(LEAST(p_from, p_to));
      v_end   DATE := TRUNC(GREATEST(p_from, p_to));
    BEGIN
     LOOP
        PIPE ROW (v_start);
        EXIT WHEN v_start >= v_end;
        v_start := v_start + INTERVAL '1' DAY;
      END LOOP;
      RETURN;
    END       generate_dates_pipelined;
/

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 'T' 
         END AS location_type

    FROM   dual
    CONNECT BY level <= 20;

WITH input  (base_date,start_time) AS (
SELECT 
COLUMN_VALUE,
COLUMN_VALUE+
NUMTODSINTERVAL(83760, 'SECOND')
  FROM   TABLE(generate_dates_pipelined(DATE '2021-08-21',DATE '2021-08-30')) 
)
SELECT
     location_id,
     base_date, start_time + (LEVEL-1) * INTERVAL '10' MINUTE
         AS start_date,
       start_time + (LEVEL-1) * INTERVAL '10' MINUTE + INTERVAL '5' MINUTE
         AS end_date 
FROM   input i
CROSS APPLY  (SELECT location_id from locations)
CONNECT BY (LEVEL-1) * INTERVAL '10' MINUTE < INTERVAL '1' DAY
AND    LEVEL <= (SELECT COUNT(*) FROM locations)
AND    start_time + (LEVEL-1) * INTERVAL '10' MINUTE < TRUNC(start_time) + INTERVAL '1' DAY;
This post has been answered by Frank Kulash on Aug 19 2021
Jump to Answer

Comments

User_KN1IX

I re-installed mysql.
I think the Stack template assigns a password but does not display it afterwards.

dvohra21

No need to provide a host name. Just use

mysql  -u root
User_KN1IX

Mysql was installed with a root password. So that does not work.

dvohra21

If password was used:

mysql  -u root -p 

Provide password when prompted. Is password not known?

1 - 4

Post Details

Added on Aug 19 2021
10 comments
625 views