Home » SQL & PL/SQL » SQL & PL/SQL » traverse a table (Oracle 10g)
traverse a table [message #488114] Wed, 05 January 2011 00:44 Go to next message
holkudla
Messages: 2
Registered: January 2011
Location: San Jose
Junior Member
I have a requirement where a view needs to be created to traverse a table that is connected by dates. For example:

table1 has the columns id, status_string, start_date, and end_date.

The row entries would look like this

1, 'continuing', 08/16/2010, 09/15/2010
1, 'started', 06/15/2010, 07/15/2010
5, 'started', 01/01/2010, 02/01/2010
1, 'continuing', 07/16/2010, 08/15/2010
5, 'terminated', 02/02/2010, 02/15/2010
3, 'started', 05/05/2010, 08/08/2011
1, 'terminted', 09/16/2010, 09/17/2010
1, 'started', 11/10/2010, 12/15/2010


I need to create a view that connects the end_date from one row with the start_date of the next row. So my select should result in this exact list returning as represented below. As you can see, the pattern is, current_row.end_date = next_row.start_date - 1 day. Also, the status_string is 'continuing' on the current row. If the status_string is not 'continuing', even if the dates match up, that's just a coincidence.

1, 'started', 06/15/2010, 07/15/2010
1, 'continuing', 07/16/2010, 08/15/2010
1, 'continuing', 08/16/2010, 09/15/2010
1, 'terminted', 09/16/2010, 09/17/2010
1, 'started', 11/10/2010, 12/15/2010
3, 'started', 05/05/2010, 08/08/2011
5, 'started', 01/01/2010, 02/01/2010
5, 'terminated', 02/02/2010, 02/15/2010

thank you, any help would be appreciated.

btw, there are over million rows in this table and it is a legacy system that's been around for 10+ years. So the option to redesigning the table is not a option. Migration would be a monster job just to satisfy this one view we want to write. Also, there are approximately 50+ columns in this table - I'm only representing the relevant columns to the query.

Report message to a moderator

Re: traverse a table [message #488116 is a reply to message #488114] Wed, 05 January 2011 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 54126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You posted this in "Suggestions & Feedback" which purpose is "Post your feedback and suggestions for this site (OraFAQ) here. All suggestions, feedback, comments, ideas, etc are more than welcome." Do you think your topic fits this definition? I think it should be posted in "SQL & PL/SQL" forum. Please take care where you create your topic.

Also, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
And always post your Oracle version, with 4 decimals (10g is NOT an Oracle version, it is a marketing name, 10.2.0.4 is an Oracle version).

Post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data. And take care of the data you post, I don't think that 'terminted' is a correct value (you should add a check constraint on your column).

Regards
Michel

Report message to a moderator

Re: traverse a table [message #488123 is a reply to message #488114] Wed, 05 January 2011 02:59 Go to previous messageGo to next message
lakshmis
Messages: 100
Registered: November 2008
Location: India
Senior Member
Hi,
DDL and DMLs:
CREATE TABLE tabs(id NUMBER(1), status VARCHAR2(15), start_date DATE, end_date DATE)

INSERT INTO tabs VALUES(1, 'continuing', '08/16/2010', '09/15/2010');
INSERT INTO tabs VALUES(1, 'started', '06/15/2010', '07/15/2010');
INSERT INTO tabs VALUES(5, 'started', '01/01/2010', '02/01/2010');
INSERT INTO tabs VALUES(1, 'continuing', '07/16/2010', '08/15/2010');
INSERT INTO tabs VALUES(5, 'terminated', '02/02/2010', '02/15/2010');
INSERT INTO tabs VALUES(3, 'started', '05/05/2010', '08/08/2011');
INSERT INTO tabs VALUES(1, 'terminted', '09/16/2010', '09/17/2010');
INSERT INTO tabs VALUES(1, 'started', '11/10/2010', '12/15/2010');

I tried using START WITH and CONNECT BY:
SELECT id,status,start_date,end_date FROM tabs START WITH start_date='06/15/2010' 
OR status='started'
CONNECT BY start_date=PRIOR end_date+1

But the order of the result I got vary from your requirement. And I could not order them.
Output:
1 started 06/15/2010 07/15/2010 
1 continuing 07/16/2010 08/15/2010 
1 continuing 08/16/2010 09/15/2010 
1 terminted 09/16/2010 09/17/2010 
5 started 01/01/2010 02/01/2010 
5 terminated 02/02/2010 02/15/2010 
3 started 05/05/2010 08/08/2011 
1 started 11/10/2010 12/15/2010


Regards,
Lakshmi.

Report message to a moderator

Re: traverse a table [message #488127 is a reply to message #488123] Wed, 05 January 2011 03:37 Go to previous messageGo to next message
cookiemonster
Messages: 9135
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do not create tables called tabs - there's a data dictionary view of that name.
Always use to_Date with the correct format mask for dates. Just because it works for you doesn't mean it'll work for everybody else:
SQL> CREATE TABLE tabs1 (id NUMBER(1), status VARCHAR2(15), start_date DATE, end_date DATE);

Table created.

SQL> INSERT INTO tabs1 VALUES(1, 'continuing', '08/16/2010', '09/15/2010');
INSERT INTO tabs1 VALUES(1, 'continuing', '08/16/2010', '09/15/2010')
                                          *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Report message to a moderator

Re: traverse a table [message #488130 is a reply to message #488127] Wed, 05 January 2011 03:51 Go to previous messageGo to next message
lakshmis
Messages: 100
Registered: November 2008
Location: India
Senior Member
Hi,
Modified DDL and DMLs:
CREATE TABLE test_tab(id NUMBER(1), status VARCHAR2(15), start_date DATE, end_date DATE)

INSERT INTO test_tab VALUES(1, 'continuing', TO_DATE('08/16/2010','MM/DD/YYYY'),TO_DATE('09/15/2010','MM/DD/YYYY'));
INSERT INTO test_tab VALUES(1, 'started', TO_DATE('06/15/2010','MM/DD/YYYY'),TO_DATE('07/15/2010','MM/DD/YYYY'));
INSERT INTO test_tab VALUES(5, 'started', TO_DATE('01/01/2010','MM/DD/YYYY'),TO_DATE('02/01/2010','MM/DD/YYYY'));
INSERT INTO test_tab VALUES(1, 'continuing',TO_DATE('07/16/2010','MM/DD/YYYY'),TO_DATE('08/15/2010','MM/DD/YYYY'));
INSERT INTO test_tab VALUES(5, 'terminated',TO_DATE('02/02/2010','MM/DD/YYYY'),TO_DATE('02/15/2010','MM/DD/YYYY'));
INSERT INTO test_tab VALUES(3, 'started', TO_DATE('05/05/2010','MM/DD/YYYY'),TO_DATE('08/08/2011','MM/DD/YYYY'));
INSERT INTO test_tab VALUES(1, 'terminted',TO_DATE('09/16/2010','MM/DD/YYYY'),TO_DATE('09/17/2010','MM/DD/YYYY'));
INSERT INTO test_tab VALUES(1, 'started', TO_DATE('11/10/2010','MM/DD/YYYY'),TO_DATE('12/15/2010','MM/DD/YYYY'));

I just want to know is there any other method apart from START WITH..CONNECT BY to arrive at these results in SQL.

Regards,
Lakshmi.

Report message to a moderator

Re: traverse a table [message #488133 is a reply to message #488114] Wed, 05 January 2011 03:56 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
 
SQL> select * from tabs order by 1,3;

        ID STATUS          START_DATE END_DATE
---------- --------------- ---------- ----------
         1 started         06/15/2010 07/15/2010
         1 continuing      07/16/2010 08/15/2010
         1 continuing      08/16/2010 09/15/2010
         1 terminted       09/16/2010 09/17/2010
         1 started         11/10/2010 12/15/2010
         3 started         05/05/2010 08/08/2011
         5 started         01/01/2010 02/01/2010
         5 terminated      02/02/2010 02/15/2010

8 rows selected.


looks to me simple order by

Report message to a moderator

Re: traverse a table [message #488135 is a reply to message #488130] Wed, 05 January 2011 04:01 Go to previous messageGo to next message
Michel Cadot
Messages: 54126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I just want to know is there any other method apart from START WITH..CONNECT BY to arrive at these results in SQL

No.

Regards
Michel

Report message to a moderator

Re: traverse a table [message #488219 is a reply to message #488135] Wed, 05 January 2011 11:47 Go to previous messageGo to next message
holkudla
Messages: 2
Registered: January 2011
Location: San Jose
Junior Member
Thanks for everyone's input. This was my first post ever so I tried pseudo coding the problem. I can't post the actual table (even the column names or the table names) for security reasons. So it's going to be a problem. But I'll give "connect by a shot" and see what happens. If it works, I'll do another post. If not, I'll do another post with a working schema creation scripts - although, it can't look anything remotely close to my actual schema.

Report message to a moderator

Re: traverse a table [message #488224 is a reply to message #488219] Wed, 05 January 2011 11:51 Go to previous messageGo to next message
Michel Cadot
Messages: 54126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it works:
SQL> select id, status, start_date, end_date
  2  from test_tab
  3  connect by prior end_date = start_date-1 and prior id = id
  4  start with status = 'started'
  5  /
        ID STATUS          START_DATE END_DATE
---------- --------------- ---------- ----------
         5 started         01/01/2010 01/02/2010
         5 terminated      02/02/2010 15/02/2010
         3 started         05/05/2010 08/08/2011
         1 started         15/06/2010 15/07/2010
         1 continuing      16/07/2010 15/08/2010
         1 continuing      16/08/2010 15/09/2010
         1 terminted       16/09/2010 17/09/2010
         1 started         10/11/2010 15/12/2010

Regards
Michel

[Updated on: Wed, 05 January 2011 11:52]

Report message to a moderator

Re: traverse a table [message #488226 is a reply to message #488219] Wed, 05 January 2011 11:55 Go to previous messageGo to next message
cookiemonster
Messages: 9135
Registered: September 2008
Location: Rainy Manchester
Senior Member
You could just change the table name and column names to something else. So long as we've got something that creates tables and data that accurately reflects your problem we couldn't care less what it's called.
Just keep it simple - include only the relevant columns.

Report message to a moderator

Re: traverse a table [message #488233 is a reply to message #488224] Wed, 05 January 2011 12:04 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
10+ years of millions of records..The connect by will take very long time to complete even with indexes..

Report message to a moderator

Re: traverse a table [message #488236 is a reply to message #488233] Wed, 05 January 2011 12:11 Go to previous messageGo to next message
BlackSwan
Messages: 20085
Registered: January 2009
Senior Member
>10+ years of millions of records..
& who is responsible for keeping all this data?
Yes, many, many records take time to process.

You can not push a string.
Accept reality & get on with life.

Report message to a moderator

Re: traverse a table [message #488240 is a reply to message #488233] Wed, 05 January 2011 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 54126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ayush_anand wrote on Wed, 05 January 2011 19:04
10+ years of millions of records..The connect by will take very long time to complete even with indexes..

And then? If you have to handle the whole data, you have to handle the whole data.
What do you suggest?

Regards
Michel

Report message to a moderator

Re: traverse a table [message #570469 is a reply to message #488114] Sat, 10 November 2012 06:42 Go to previous messageGo to next message
CooLDB
Messages: 2
Registered: November 2012
Location: India
Junior Member
This will also Do:

SELECT A.ID,B.Status,C.Start_Date,B.End_Date FROM
(SELECT ID,MIN(Start_Date) Start_Date FROM Tabs GROUP BY ID ) A JOIN
(SELECT * FROM Tabs) B
ON A.ID=B.ID
JOIN
(SELECT * FROM Tabs) C
ON B.ID=C.ID AND B.Status=C.Status AND B.End_Date=C.End_Date
ORDER BY C.Start_Date,B.End_Date

ID Status Start_Date End_Date
5 started 2010-01-01 2010-02-01
5 terminated 2010-02-02 2010-02-15
3 started 2010-05-05 2011-08-08
1 started 2010-06-15 2010-07-15
1 continuing 2010-07-16 2010-08-15
1 continuing 2010-08-16 2010-09-15
1 terminted 2010-09-16 2010-09-17
1 started 2010-11-10 2010-12-15

Report message to a moderator

Re: traverse a table [message #570471 is a reply to message #570469] Sat, 10 November 2012 07:11 Go to previous messageGo to next message
John Watson
Messages: 3102
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Good of you to try to help - a lot of new members never contribute, only take. But be careful: I guess you didn't notice that this topic dates back to January Smile

Report message to a moderator

Re: traverse a table [message #570472 is a reply to message #488224] Sat, 10 November 2012 07:20 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1397
Registered: January 2010
Senior Member
Michel Cadot wrote on Wed, 05 January 2011 12:51
Yes it works:


But it needs additional condition. Otherwise it will not work right if same id started again next day after:

SQL> update test_tab
  2     set start_date = date '2010-09-18' where start_date = date '2010-11-10'
  3  /

1 row updated.

SQL> select * from test_tab order by id,start_date
  2  /

        ID STATUS          START_DAT END_DATE
---------- --------------- --------- ---------
         1 started         15-JUN-10 15-JUL-10
         1 continuing      16-JUL-10 15-AUG-10
         1 continuing      16-AUG-10 15-SEP-10
         1 terminted       16-SEP-10 17-SEP-10
         1 started         18-SEP-10 15-DEC-10
         3 started         05-MAY-10 08-AUG-11
         5 started         01-JAN-10 01-FEB-10
         5 terminated      02-FEB-10 15-FEB-10

8 rows selected.

SQL> select id, status, start_date, end_date
  2    from test_tab
  3    connect by prior end_date = start_date-1 and prior id = id
  4    start with status = 'started'
  5  /

        ID STATUS          START_DAT END_DATE
---------- --------------- --------- ---------
         5 started         01-JAN-10 01-FEB-10
         5 terminated      02-FEB-10 15-FEB-10
         3 started         05-MAY-10 08-AUG-11
         1 started         15-JUN-10 15-JUL-10
         1 continuing      16-JUL-10 15-AUG-10
         1 continuing      16-AUG-10 15-SEP-10
         1 terminted       16-SEP-10 17-SEP-10
         1 started         18-SEP-10 15-DEC-10
         1 started         18-SEP-10 15-DEC-10

9 rows selected.

SQL> select id, status, start_date, end_date
  2    from test_tab
  3    start with status = 'started'
  4    connect by prior end_date = start_date - 1
  5        and prior id = id
  6        and status != 'started'
  7  /

        ID STATUS          START_DAT END_DATE
---------- --------------- --------- ---------
         5 started         01-JAN-10 01-FEB-10
         5 terminated      02-FEB-10 15-FEB-10
         3 started         05-MAY-10 08-AUG-11
         1 started         15-JUN-10 15-JUL-10
         1 continuing      16-JUL-10 15-AUG-10
         1 continuing      16-AUG-10 15-SEP-10
         1 terminted       16-SEP-10 17-SEP-10
         1 started         18-SEP-10 15-DEC-10

8 rows selected.

SQL>


SY.

Report message to a moderator

Re: traverse a table [message #570779 is a reply to message #570471] Thu, 15 November 2012 12:39 Go to previous messageGo to next message
CooLDB
Messages: 2
Registered: November 2012
Location: India
Junior Member
Yeahh i saw that.. But could not refrain myself from posting that sql Razz

Report message to a moderator

Re: traverse a table [message #570780 is a reply to message #570779] Thu, 15 November 2012 12:53 Go to previous messageGo to next message
Michel Cadot
Messages: 54126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why? You don't want to share?

Regards
Michel

Report message to a moderator

Re: traverse a table [message #570790 is a reply to message #488114] Thu, 15 November 2012 19:57 Go to previous messageGo to next message
masterborj
Messages: 21
Registered: October 2007
Location: Philippines
Junior Member
You might want to try this

SELECT ID,
STATUS,
START_DATE,
END_DATE,
LEAD(START_DATE) OVER (PARTITION BY ID ORDER BY START_DATE) NEXT_START_DATE
FROM TABS;

Report message to a moderator

Re: traverse a table [message #570803 is a reply to message #570790] Thu, 15 November 2012 23:57 Go to previous message
Michel Cadot
Messages: 54126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No it does not work.
Could you try the query before posting it?

Regards
Michel

Report message to a moderator

Previous Topic: Help in this pivot query
Next Topic: Need Sql Query
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat May 18 15:06:01 CDT 2013

Total time taken to generate the page: 1.04241 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.