Solved

Usin connect to prior for the hirerarchy query and order by

Posted on 2007-03-20
6
751 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 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle RAC 12c 8 87
passing parameters to sql script oracle 4 61
form builder not starting 3 56
Help on model clause 5 37
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

830 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