Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Usin connect to prior for the hirerarchy query and order by

Posted on 2007-03-20
6
Medium Priority
?
756 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

688 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