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 · Accepted Answer · 2016-05-31 00:35:43Z

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

Do you know how you would use it inside of a recursive sql server like query? With the anchor and recursive parts of the cte?

vercelli · Accepted Answer · 2016-05-31 08:38:59Z

1

You are getting an error on the query since on the lower side of the union all you are using pN.task_id instead of d.task_id and that's an infinite loop. This is what it should look like:

    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(d.Days,0) = 0 THEN pN.Project
             ELSE pN.Project + 1
        END AS "Project"
      , d.Task_Id
      , d.Start_Date
      , d.End_Date
      , d.Days
  FROM projectsNumbered pN 
    JOIN daysBetweenTasks d on d.task_Id = pN.task_Id + 1
)
SELECT * 
FROM projectsNumbered;

OUPUT

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

answered May 31, 2016 at 8:38

vercelli

4,7772 gold badges17 silver badges15 bronze badges

1 Comment

uh_big_mike_boi Over a year ago

Thanks man, I was doing that actually, but I had the columns ordered incorrectly. It was giving me only one row in return. The craziest error man such a noob

Collectives™ on Stack Overflow

Recursive Query in Oracle

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related