Solved

Usin connect to prior for the hirerarchy query and order by

Posted on 2007-03-20
6
748 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
  • 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 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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.
Via a live example, show how to take different types of Oracle backups using RMAN.

803 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