Re: hierarchical queries

DROP TABLE t;
CREATE TABLE t
( par VARCHAR2(10), ch VARCHAR2(10));

INSERT INTO t VALUES ('A1', 1);
INSERT INTO t VALUES ('A1', 2);
INSERT INTO t VALUES ('A1', 3);
INSERT INTO t VALUES ('A2', 4);
INSERT INTO t VALUES ('A3', 5);
INSERT INTO t VALUES ('A4', 'A1');
INSERT INTO t VALUES ('A4', 'A2');
INSERT INTO t VALUES ('A5', 'A2');
INSERT INTO t VALUES ('A5', 'A3');
INSERT INTO t VALUES ('A3', 'A1');

SELECT
r, ch
FROM (
SELECT ch, substr(s,1,instr(s,';')-1) r , a FROM (
SELECT ch, substr(SYS_CONNECT_BY_PATH(par,';'),2)||';' s, sys_connect_by_path(par,';') a
FROM t
CONNECT BY PRIOR ch = par
)
)
WHERE substr(r,1,1)<>substr(ch ,1,1)
ORDER BY r

This is solution for above problem, but my mistake is that i use general exemple, so you would be able to understand what i need better. Key is to eliminate parent who has child which is also parent...in my example WHERE substr(r,1,1)<>substr(ch ,1,1) is the key but in practice I have no such values for child, or parent... A1,A2, A3, 1,2,3...etc is the simplest example, in real there are BBV1, 37, 21, BV2, DJ1, DD3 ...values...and WHERE substr(r,1,1)<>substr(ch ,1,1) is not valid here...These values BBV1, 37, 21, BV2, DJ1....etc are actualy classification of accounts, so every account has simple and composite account..
I'm seeking for general solution not WHERE substr(r,1,1)<>substr(ch ,1,1)....

Homework...he he.. :) ...if i'm using oracle 10g solution will be trivial!!! use CONNECT_BY_ROOT and CONNECT_BY_ISLEAF

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1130339493.338966_at_yasure...
>T wrote:
>> I have problem with hierarchical query on Oracle database 9.2. I need to
>> list (with SQL-plus) all child at leaf level for each parent on root
>> level ....
>>
>> Example
>>
>> PARENT CHILD
>> A1 1
>> A1 2
>> A1 3
>> A2 4
>> A3 5
>> A4 A1
>> A4 A2
>> A5 A2
>> A5 A3
>>
>>
>>
>> output
>> PARENT CHILD
>> A1 1
>> A1 2
>> A1 3
>> A2 4
>> A3 5
>> A4 1
>> A4 2
>> A4 3
>> A4 4
>> A5 4
>> A5 5
>>
>> I used "select .....from ....start with ..connect by prior....." but with
>> no success..
>>
>> Every help is welcome!!!
>> :))
>
> Post your actual SQL. This looks like homework and, whether it is or
> not, no one should just hand you the answer ... rather point you in
> the right direction.
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan_at_x.washington.edu
> (replace x with u to respond)
Received on Thu Oct 27 2005 - 02:42:33 CDT