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!

Fetching multiple rows using CONNECT BY

Anil KuppaNov 27 2015 — edited Nov 30 2015

Hi,

This is the table structure:

CREATE TABLE XX_SHIFT_EXCEPTIONS
(
PLANT_SHIFT_EXC_ID     NUMBER(18) NOT NULL,
START_DATE  DATE NOT NULL ,
END_DATE              DATE NOT NULL ,
EXC_START_TIME        NUMBER(18) NOT NULL ,
EXC_DURATION                   NUMBER(18),      
EXC_END_TIME           NUMBER(18) NOT NULL);

INSERT INTO XX_SHIFT_EXCEPTIONS
VALUES
(1,'27-NOV-2015','30-NOV-2015',1200,480,2000);

INSERT INTO XX_SHIFT_EXCEPTIONS
VALUES
(2,'28-NOV-2015','28-NOV-2015',1200,480,2000);

INSERT INTO XX_SHIFT_EXCEPTIONS
VALUES
(3,'27-NOV-2015','28-NOV-2015',1200,480,2000);

I want my result to look like this:

PLANT_SHIFT_EXC_ID START_DATE END_DATE EXC_START_TIME EXC_END_TIME

1 27-NOV-2015 27-NOV-2015 1200 2000

1 28-NOV-2015 28-NOV-2015 1200 2000

1 29-NOV-2015 29-NOV-2015 1200 2000

1 30-NOV-2015 30-NOV-2015 1200 2000

2 28-NOV-2015 28-NOV-2015 1200 2000

3 27-NOV-2015 27-NOV-2015 1200 2000

3 28-NOV-2015 28-NOV-2015 1200 2000

I tried the below:

SELECT  PLANT_SHIFT_EXC_ID,START_DATE+LEVEL-1 START_DATE,START_DATE+LEVEL-1 END_DATE,EXC_START_TIME,EXC_END_TIME
from
(SELECT * FROM XX_SHIFT_EXCEPTIONS
WHERE TRUNC(END_DATE)!=TRUNC(START_DATE)) SHIFT1
CONNECT BY LEVEL<=(END_DATE-START_DATE+1)
UNION ALL
SELECT PLANT_SHIFT_EXC_ID,START_DATE,END_DATE,EXC_START_TIME,EXC_END_TIME
FROM XX_SHIFT_EXCEPTIONS WHERE END_DATE=START_DATE
order by 1,2;

But the result set is not the expected as for plant shift 1 , I get 11 rows instead of 4 rows

Please help. Thanks.

This post has been answered by RogerT on Nov 27 2015

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Dec 28 2015

Added on Nov 27 2015

#sql

4 comments

450 views