Solved

Usin connect to prior for the hirerarchy query and order by

Posted on 2007-03-20
6
755 Views
Last Modified: 2013-12-19
Hi

I have a table where there have relationships

account_id   related_account_id  relationship_type
---------------------------------------------------
100  110   GP-P
100  120   GP-P
110  130   P-C
120  140   P-C
120  150   P-C
200  210   GP-P
210  220   P-C

I want the data to be returned as first record should be the account_id of grandparent,
next record should be the corresponding parent and next records should be the corresponding child.
Now next record should be the corresponding parent and of the first grand parent and thereafter the corresponding childs of that parent.
Now after these set of records of the first hierarchies next should display the details of second grandparent
ex:
Now the query should return

account_id    relationship_type
-------------------------------
100  Grandparent
110  Parent
130  child
120  Parent
140  child
150  child
200  Grandparent
210  Parent
220  child

I know that i should use CONNECT BY PRIOR can someone help me out.
Iam using oracle 9i.

Thanks in advance

0
Comment
Question by:aaba646
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
6 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 18762247
Have you tried something like this ?
Select account_id,relationship_type from <your_table> connect by prior account_id=related_account_id;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20553435
How are you translating GP-P and P-C to Grandparent, Parent and child?

Is that a typo?  Or do you really need your info to translate like that?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 22205009
here's one last shot at it.

assuming the data is correct, no typos and a simple search and replace with no lookups is fine.

Then this returns the correct results
  SELECT *
    FROM (SELECT CASE WHEN n = 1 THEN account_id ELSE related_account_id END
                     account_id,
                 DECODE(CASE
                            WHEN n = 1
                            THEN
                                SUBSTR(relationship_type,
                                       1,
                                       INSTR(relationship_type, '-') - 1)
                            ELSE
                                SUBSTR(relationship_type,
                                       INSTR(relationship_type, '-') + 1)
                        END,
                        'GP',
                        'Grandparent',
                        'P',
                        'Parent',
                        'C',
                        'child')
                     relationship_type,
                 rn,
                 ROW_NUMBER() OVER (
                              PARTITION BY CASE
                                               WHEN n = 1 THEN account_id
                                               ELSE related_account_id
                                           END
                                  ORDER BY rn)
                     r
            FROM (    SELECT account_id,
                             related_account_id,
                             relationship_type,
                             ROWNUM rn
                        FROM yourtable
                  START WITH account_id NOT IN
                                     (SELECT related_account_id
                                        FROM yourtable)
                  CONNECT BY account_id = PRIOR related_account_id),
                 (SELECT 1 n FROM DUAL
                  UNION ALL
                  SELECT 2 FROM DUAL))
   WHERE r = 1
ORDER BY rn

Open in new window

0

Featured Post

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question