Re: hierarchical queries

From: T <t_at_yahoo.com>
Date: Thu, 27 Oct 2005 10:19:12 +0200
Message-ID: <djq2ib$6no$1@ss405.t-com.hr>

here is final solution made by mj from Croatia newsgroup!!!!!

SELECT r, ch
FROM (
SELECT
CH, R, LVL, FIRST_VALUE(LVL) OVER (PARTITION BY R ORDER BY LVL DESC) l FROM (
SELECT
CH
, SUBSTR(S, 1, INSTR(S, ';') - 1) R
, LVL
FROM (
SELECT CH, LEVEL LVL
,SUBSTR(SYS_CONNECT_BY_PATH(PAR, ';'), 2) || ';' S FROM T
CONNECT BY PRIOR CH = PAR

)
)
)

WHERE lvl = l
ORDER BY R, ch

"T" <t_at_yahoo.com> wrote in message news:djq0do$20i$1_at_ss405.t-com.hr...
> 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 - 03:19:12 CDT