Home » Other » Training & Certification » Query Help regarding finding relationships.
icon1.gif  Query Help regarding finding relationships. [message #254828] Sat, 28 July 2007 22:06 Go to next message
sibgat
Messages: 45
Registered: November 2006
Location: Chennai
Member
Hi,

A Table contains two columns, column1 and column2 respectively, where these columns represent some relationship which shows a > c , b > d, a >d, c >d. basically column1 > column2
 column1 column2
 a       c
 d       b
 a       d
 c       d


I want the output to be greatest,next greatest, next next greatest like (n,n-1,n-2 etc) like 
a -> c -> d - > b


It would be of great help if you could help in doing this.
Thanks in advance
Regards,
Sibghat

Report message to a moderator

Re: Query Help regarding finding relationships. [message #254829 is a reply to message #254828] Sat, 28 July 2007 22:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
In case you did not notice, there is a forum specifically for homeworks near the bottom of the main frame.

If you really want to solve this problem on your own, search for "PIVOT QUERY".

Report message to a moderator

Re: Query Help regarding finding relationships. [message #254837 is a reply to message #254828] Sun, 29 July 2007 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is standard hierarchical query.

Regards
Michel

Report message to a moderator

Re: Query Help regarding finding relationships. [message #254876 is a reply to message #254837] Sun, 29 July 2007 22:38 Go to previous messageGo to next message
sibgat
Messages: 45
Registered: November 2006
Location: Chennai
Member
Michel,
i didn't find any sort of example in that link, which gives clue to my query... can you provide me an exact linke.
Thanks for your time
Regards,
Sibghat

Report message to a moderator

Re: Query Help regarding finding relationships. [message #254923 is a reply to message #254876] Mon, 30 July 2007 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you follow the links inside this page?
This is a starting point for your search.

Regards
Michel

Report message to a moderator

Re: Query Help regarding finding relationships. [message #254924 is a reply to message #254923] Mon, 30 July 2007 01:33 Go to previous messageGo to next message
sibgat
Messages: 45
Registered: November 2006
Location: Chennai
Member
Yes i did, unfortunately for me, i was not able to trace the exact one for my requirement
Regards
Sibghat

Report message to a moderator

Re: Query Help regarding finding relationships. [message #254928 is a reply to message #254876] Mon, 30 July 2007 01:47 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
In a standard hierarchical query, your value of 'b' won't show up. It is nowhere present as a value of column1. If we take column2 to generate the hierarchy, then value 'a' won't show up. Normally, a hierarchy will be something like this:
current_id parent_id
    a         NULL
    c          a
    d          c
    b          c


MHE

Report message to a moderator

Re: Query Help regarding finding relationships. [message #254948 is a reply to message #254924] Mon, 30 July 2007 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you tried and why it is not working.

Regards
Michel

Report message to a moderator

icon5.gif  Re: Query Help regarding finding relationships. [message #255174 is a reply to message #254948] Mon, 30 July 2007 23:32 Go to previous messageGo to next message
sibgat
Messages: 45
Registered: November 2006
Location: Chennai
Member
I have tried with sys_connect_by_path, it is not working, more over i want logic how to do.. it would be great if you could help me.
Thanks and regards
Sibghat

Report message to a moderator

Re: Query Help regarding finding relationships. [message #255223 is a reply to message #255174] Tue, 31 July 2007 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 30 July 2007 09:17
Post what you tried and why it is not working.

Regards
Michel

[Updated on: Tue, 31 July 2007 01:09]

Report message to a moderator

Re: Query Help regarding finding relationships. [message #255228 is a reply to message #255174] Tue, 31 July 2007 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also tried on AskTom: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:766825833740#380301900346009584

Tom's answer:
Quote:
Followup July 30, 2007 - 5pm US/Eastern:

simple connect by, lots of examples.

I'd have given you one with this data, but no create, no inserts, no look

Regards
Michel

Report message to a moderator

Re: Query Help regarding finding relationships. [message #255263 is a reply to message #255223] Tue, 31 July 2007 04:06 Go to previous messageGo to next message
sibgat
Messages: 45
Registered: November 2006
Location: Chennai
Member
Hi Michel
This is what i tried.


create table si (c1 varchar2(3),c2 varchar2(3))

insert into si values('a','c')
insert into si values('d','b')
insert into si values('a','d')
insert into si values('c','d')


SELECT SYS_CONNECT_BY_PATH(C1||C2,
'>') FROM SI
START WITH C1 = 'A'
CONNECT BY PRIOR C1 = C2

No Rows Returned

Thanks
Sib

Report message to a moderator

Re: Query Help regarding finding relationships. [message #255271 is a reply to message #255263] Tue, 31 July 2007 04:36 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
This looks familiar.
MHE

Report message to a moderator

Re: Query Help regarding finding relationships. [message #255286 is a reply to message #255263] Tue, 31 July 2007 05:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ There is no 'A' in the table data
2/ The prior relation in the opposite
3/ The expression is SYS_CONNECT_BY_PATH is not the correct one

Regards
Michel

Report message to a moderator

Re: Query Help regarding finding relationships. [message #255288 is a reply to message #254828] Tue, 31 July 2007 05:08 Go to previous messageGo to next message
sibgat
Messages: 45
Registered: November 2006
Location: Chennai
Member
SELECT MAX (SYS_CONNECT_BY_PATH (DECODE (LEVEL, 1, C1, C2), ' ')
)KEEP (DENSE_RANK LAST ORDER BY LEVEL) "Relationship"
FROM SI
CONNECT BY PRIOR C2 = C1

Report message to a moderator

Re: Query Help regarding finding relationships. [message #255292 is a reply to message #255288] Tue, 31 July 2007 05:17 Go to previous message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course this is not from you, so:
1/ Use QUOTE tags
2/ post the origin of what you quote (link)

Regards
Michel

Report message to a moderator

Previous Topic: 8 Questions req. clarification for funda II exam
Next Topic: queries
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Apr 24 16:03:35 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.