Usin connect to prior for the hirerarchy query and order by

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

aaba646Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Helena Markováprogrammer-analystCommented:
Have you tried something like this ?
Select account_id,relationship_type from <your_table> connect by prior account_id=related_account_id;
0
sdstuberCommented:
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
sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.