Recursive Query in Oracle

Question

Hi I have the following query...

I want to have a the following data set, which just has 4 columns, be queried to get the project. The projects are separated by the previous row not having the same end_date as the current row start_date. So the first three rows would be in the same project. The second project would be the next two rows, and project 3 and 4 would be the second to last and last rows.

DAYS,TASK_ID,START_DATE,END_DATE
NULL,1,10/1/2015,10/2/2015
0,2,10/2/2015,10/3/2015
0,3,10/3/2015,10/4/2015
9,4,10/13/2015,10/14/2015
0,5,10/14/2015,10/15/2015
13,6,10/28/2015,10/29/2015
1,7,10/30/2015,10/31/2015

So the output would look like

PROJECT,DAYS,TASK_ID,START_DATE,END_DATE
1,NULL,1,10/1/2015,10/2/2015
1,0,2,10/2/2015,10/3/2015
1,0,3,10/3/2015,10/4/2015
2,9,4,10/13/2015,10/14/2015
2,0,5,10/14/2015,10/15/2015
3,13,6,10/28/2015,10/29/2015
4,1,7,10/30/2015,10/31/2015

I am a sql server architect and I already did the sql server query, but I'm getting errors on the oracle query. Here is what I have so far in Oracle.

WITH projectsNumbered (Project, Task_Id, Start_Date, End_Date, Days) AS
(SELECT 1 As "Project"
      , Task_Id
      , Start_Date
      , End_Date
      , Days
  FROM daysBetweenTasks
  WHERE Task_Id = 1
  UNION ALL
  SELECT 
        CASE WHEN COALESCE(pN.Days,0) = 0 THEN pN.Project
             ELSE pN.Project + 1
        END AS "Project"
      , pN.Task_Id
      , pN.Start_Date
      , pN.End_Date
      , pN.Days
  FROM projectsNumbered pN 
    JOIN daysBetweenTasks d on p.task_Id = pN.task_Id + 1
)
--SEARCH DEPTH FIRST BY Task_Id SET order1
CYCLE Task_Id SET cycle TO '1' DEFAULT 0
SELECT * 
FROM projectsNumbered

But I only get two rows returning. I added the Cycle Clause after I was getting an error. I think an oracle dba could probably handle this one? I'm using 11g Express by the way.

MT0 MT0 · Accepted Answer · 2016-05-31T00:35:43

Oracle Setup:

CREATE TABLE daysBetweenTasks ( DAYS,TASK_ID,START_DATE,END_DATE ) AS
SELECT NULL, 1, DATE '2015-10-01', DATE '2015-10-02' FROM DUAL UNION ALL
SELECT 0,    2, DATE '2015-10-02', DATE '2015-10-03' FROM DUAL UNION ALL
SELECT 0,    3, DATE '2015-10-03', DATE '2015-10-04' FROM DUAL UNION ALL
SELECT 9,    4, DATE '2015-10-13', DATE '2015-10-14' FROM DUAL UNION ALL
SELECT 0,    5, DATE '2015-10-14', DATE '2015-10-15' FROM DUAL UNION ALL
SELECT 13,   6, DATE '2015-10-28', DATE '2015-10-29' FROM DUAL UNION ALL
SELECT 1,    7, DATE '2015-10-30', DATE '2015-10-31' FROM DUAL;

Query:

SELECT DENSE_RANK() OVER ( ORDER BY CONNECT_BY_ROOT( task_id ) ) AS project,
       days,
       task_id,
       start_date,
       end_date
FROM   (
  SELECT d.*,
         LAG( end_date ) OVER ( ORDER BY task_id ) AS prev_end_date
  FROM   daysBetweenTasks d
) d
START WITH prev_end_date IS NULL
OR         prev_end_date <> start_date
CONNECT BY PRIOR end_date = start_date;

Output:

   PROJECT       DAYS    TASK_ID START_DATE          END_DATE          
---------- ---------- ---------- ------------------- -------------------
         1                     1 2015-10-01 00:00:00 2015-10-02 00:00:00 
         1          0          2 2015-10-02 00:00:00 2015-10-03 00:00:00 
         1          0          3 2015-10-03 00:00:00 2015-10-04 00:00:00 
         2          9          4 2015-10-13 00:00:00 2015-10-14 00:00:00 
         2          0          5 2015-10-14 00:00:00 2015-10-15 00:00:00 
         3         13          6 2015-10-28 00:00:00 2015-10-29 00:00:00 
         4          1          7 2015-10-30 00:00:00 2015-10-31 00:00:00

Recursive Query in Oracle

2 Answers