Members   Search   Help    Register    Login   Home
Home » SQL & PL/SQL » SQL & PL/SQL » inserting predecessor and succerror values (oracle 10g)
inserting predecessor and succerror values [message #429972] Fri, 06 November 2009 10:37 Go to next message
nastyjillu
Messages: 156
Registered: February 2009
Senior Member
<< this is edited >>
i have a table called Act. another table called Act_rel

values in table Act are

Aid Aname

a1 T1
a2 T2
a3 T3

now Act_rel will have predecessor and successor values.

for a1, there is no predecessor but there are 2 successors called T2 and T3

for a2, there is one predecessor T1 and one successor T3

for a3, there is no successor but there are 2 predecessors T1 and T2.

given Act table values are just for an example. the table may grow to any size. depending on that we
should insert values into act_rel.

i tried cursors,inserts, updates. but could findout

values of Act_rel should be like this in this particular case:

Aid Predec Succsr

a1 T2

a1 T3

a2 T1 T2

a3 T1

a3 T2



thanks
jillu

[Updated on: Fri, 06 November 2009 10:48]

Report message to a moderator
Re: inserting predecessor and succerror values [message #429973 is a reply to message #429972] Fri, 06 November 2009 10:44 Go to previous messageGo to next message
JRowbottom
Messages: 5362
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You will need a column in your table to order the rows by.
Once you've got that, you can use LEAD and LAG to get previous and next values.
Report message to a moderator
Re: inserting predecessor and succerror values [message #430009 is a reply to message #429972] Fri, 06 November 2009 15:38 Go to previous messageGo to next message
Kevin Meade
Messages: 1050
Registered: December 1999
Senior Member
what is your question?
how do you order your rows?

Your data is insufficient. Consider this:

a1 T1
a2 T2
a3 T3
a4 T4
a5 T5

What you say is the desired result does not extend to conver the new data. What does a4 T4,a5 T5 do to the result?

Aid Predec Succsr

a1 T2
a1 T3
a2 T1 T2
a3 T1
a3 T2

Kevin
Report message to a moderator
Re: inserting predecessor and succerror values [message #431136 is a reply to message #430009] Sun, 15 November 2009 21:57 Go to previous messageGo to next message
nastyjillu
Messages: 156
Registered: February 2009
Senior Member
Hi Kevin,

act_Rel table should be like this


Aid  Predecessor  Successor

a1                 T2

a1                 T3

a2       T1        T2

a3       T1

a3       T2


hope its clear now
Report message to a moderator
Re: inserting predecessor and succerror values [message #431154 is a reply to message #429972] Sun, 15 November 2009 23:23 Go to previous message
ayush_anand
Messages: 270
Registered: November 2008
Senior Member
Quote:
for a2, there is one predecessor T1 and one successor T3
...
...

a2 T1 T2


why is it not like
a2 T1 T3

[Updated on: Sun, 15 November 2009 23:26]

Report message to a moderator
Previous Topic:Oracle DATE Problem
Next Topic:Hot to Stop SQL Injections
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Nov 30 23:16:25 CST 2009

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