Home » SQL & PL/SQL » SQL & PL/SQL » Getting all Linked rows (Oracle 10 g)
Getting all Linked rows [message #404200] Wed, 20 May 2009 15:49 Go to next message
pkirangi
Messages: 74
Registered: August 2005
Member
I have the following
Table structure

Order                     LPN org
12345                     A   112
12345                     B   113

12346                     K   111
12346                     B   113
12346                     C   112

12347                     C   112
12347                     L   111

12348                     M   111
12348                     A   112  
12348                     D   113

12349                     N   111
12349                     D   113

12350                     D   113
12351                     O   111

12351                     A   112 
12351                     E   111
12351                     F   111

12352                     G   111

12353                     H   111
12353                     I   111
12353                     J   111

12354                     E   111


I know the first order will be 12345. So going fom there I need to bring all related orders. The orders would be linked based of the LPN Number/Org. So I should get the
following record set

12345                     A   112
12345                     B   113
12348                     M   111
12348                     A   112
12348                     D   113
12351                     A   112
12351                     E   111
12351                     F   111
12346                     K   111
12346                     B   113
12346                     C   112
12347                     C   112
12347                     L   111
12349                     N   111
12349                     D   113
12350                     D   113
12351                     O   111
12354                     E   111


Can this be achived in a single query? I know I can write a PLSQL code, to get the details. But is there a way to use some existing functionality of SQL, to get the linked list?

Thanks
PHK
Re: Getting all Linked rows [message #404216 is a reply to message #404200] Wed, 20 May 2009 22:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try a hierarchical query. Search for CONNECT BY in the Oracle SQL Reference manual.

Ross Leishman
Re: Getting all Linked rows [message #404241 is a reply to message #404200] Thu, 21 May 2009 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand your output.
Why there is:
12345                     A   112
then
12345                     B   113
then
12348                     M   111
then
12348                     A   112
then
12348                     D   113
then
12351                     A   112
then
12351                     E   111
and so on.

Post a working Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Getting all Linked rows [message #404331 is a reply to message #404241] Thu, 21 May 2009 06:54 Go to previous messageGo to next message
pkirangi
Messages: 74
Registered: August 2005
Member
Hi Michel,
Thanks for your response.

The Oracle version we are on is 10g - 10.2.0.3

Here are scripts you requested.

create table order_lpn_tab
(order     NUMBER,
 lpn       VARCHAR2(1),
 org       NUMBER
)


INSERT INTO order_lpn_tab values(12345 ,'A' ,  112);
INSERT INTO order_lpn_tab values(12345 ,'B' ,  113);
INSERT INTO order_lpn_tab values(12346 ,'K' ,  111);
INSERT INTO order_lpn_tab values(12346 ,'B' ,  113);
INSERT INTO order_lpn_tab values(12346 ,'C' ,  112);
INSERT INTO order_lpn_tab values(12347 ,'C' ,  112);
INSERT INTO order_lpn_tab values(12347 ,'L' ,  111);
INSERT INTO order_lpn_tab values(12348 ,'M' ,  111);
INSERT INTO order_lpn_tab values(12348 ,'A' ,  112);
INSERT INTO order_lpn_tab values(12348 ,'D' ,  113);
INSERT INTO order_lpn_tab values(12349 ,'N' ,  111);
INSERT INTO order_lpn_tab values(12349 ,'D' ,  113);
INSERT INTO order_lpn_tab values(12350 ,'D' ,  113);
INSERT INTO order_lpn_tab values(12350 ,'O' ,  111);
INSERT INTO order_lpn_tab values(12351 ,'A' ,  112);
INSERT INTO order_lpn_tab values(12351 ,'E' ,  111);
INSERT INTO order_lpn_tab values(12351 ,'F' ,  111);
INSERT INTO order_lpn_tab values(12352 ,'G' ,  111);
INSERT INTO order_lpn_tab values(12353 ,'H' ,  111);
INSERT INTO order_lpn_tab values(12353 ,'I' ,  111);
INSERT INTO order_lpn_tab values(12353 ,'J' ,  111);
INSERT INTO order_lpn_tab values(12354 ,'E' ,  111);


This is the desired output

Order                     Lpn org
------                    --- ----
12345                     A   112
12345                     B   113
12348                     M   111
12348                     A   112
12348                     D   113
12351                     A   112
12351                     E   111
12351                     F   111
12346                     K   111
12346                     B   113
12346                     C   112
12347                     C   112
12347                     L   111
12349                     N   111
12349                     D   113
12350                     D   113
12350                     O   111
12354                     E   111



12345                     A   112
12345                     B   113

The above rows are there because , it was identified that order 12345 was the oldest order. This is based of a seperate logic. This is the starting point for the result set.

12348                     M   111
12348                     A   112
12348                     D   113

Order 12348 is there because it shares an LPN/org - 'A' with order 12345

12351                     A   112
12351                     E   111
12351                     F   111

Order 12351 is there because it shares an LPN/org - 'A' with order 12345

12346                     K   111
12346                     B   113
12346                     C   112

Order 12346 is there because it shares an LPN/org - 'B' with order 12345

12347                     C   112
12347                     L   111

Order 12347 is there because it shares an LPN/org - 'C' with order 12346, which again is there because it shared an LPN/org - 'B' with the starting order 12345


12349                     N   111
12349                     D   113

Order 12349 is there because it shares an LPN/org - 'D' with order 12348, which again is there because it shared an LPN/org - 'A' with the starting order 12345

12350                     D   113
12350                     O   111

Order 12350 is there is there because it shares an LPN/org - 'D' with order 12348, which again is there because it shared an LPN/org - 'A' with the starting order 12345


12354                     E   111

Order 12354 is there is there because it shares an LPN/org - 'E' with order 12351, which again is there because it shared an LPN/org - 'A' with the starting order 12345



Thanks
PHK

[Updated on: Thu, 21 May 2009 07:00]

Report message to a moderator

icon5.gif  Re: Getting all Linked rows [message #404387 is a reply to message #404331] Thu, 21 May 2009 10:45 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
can you give a reason of order
12345->12348->12351->12346

cannot be like
12345->12346->12351->12348
or
12345->12351->12348->12346
Re: Getting all Linked rows [message #404763 is a reply to message #404200] Sat, 23 May 2009 07:48 Go to previous messageGo to next message
pkirangi
Messages: 74
Registered: August 2005
Member
Hi Ayush,
The order does'nt matter.

Thanks
PHK
Re: Getting all Linked rows [message #405187 is a reply to message #404216] Tue, 26 May 2009 16:36 Go to previous message
pkirangi
Messages: 74
Registered: August 2005
Member
I tried using the CONNECT BY syntax.
But this works to two different columns. In my case, I am linking based of values in a single column - which is the LPN.

Thanks
PHK
Previous Topic: Oracle Regular Expression
Next Topic: begin end under exception
Goto Forum:
  


Current Time: Wed Dec 07 10:44:10 CST 2016

Total time taken to generate the page: 0.10754 seconds