none
query to find hierarchical or parent-child relational rows in oracle

    Pertanyaan

  • How to get no.of levels of all child records given a parent  in a self referencing table

    its a multi level hierarchy. I need to find out how many levels are there for the master parent.
    the data is as follows -i have  a table alpha-numbers, it has column alpha-n, that column has data as follows.

    ABC1-0001

    ABC1-0001-ab

    ABC1-0001-dc

    ABC1-0001-rt

    ABC1-0001-ko

    ABC1-0001-ab-01

    ABC1-0001-ab-02

    ABC1-0001-ko-03

    ABC1-0001-ko-04

    ABC1-0001-dc-00

    ABC1-0001-dc-45

    ABC1-0001-rt-15

    ABC1-0001-rt-44

    here ABC1-0001 is master parent.

    ABC1-0001-ab, ABC1-0001-dc,ABC1-0001-rt,ABC1-0001-ko are  children of ABC1-0001

    ABC1-0001-ab-01, ABC1-0001-ab-02 are childen of  ABC1-0001-ab  

    ABC1-0001-dc-00,ABC1-0001-dc-45 are children of ABC1-0001-dc

    ABC1-0001-ko-03,ABC1-0001-ko-04 are children of ABC1-0001-KO and the hierarchy goes on.

    how can i write a query to find the no. of levels for a master parent.



    sania

    10 Mei 2018 10:20

Semua Balasan

  • First of all this is not an Oracle forum

    This forum is for MS SQL Server, database product of Microsoft

    So if you're using Oracle, you may be better of posting this in some Oracle related forums

    T-SQL, the proprietary standard of SQLServer have many functions which are different from Oracle and their implementation will also vary so I doubt whether you would get a working solution for your scenario from here.

    In SQL server you can do like this

    declare @t table
    (
    alpha varchar(100)
    )
    insert @t
    values
    ('ABC1-0001'),
    
    ('ABC1-0001-ab'),
    
    ('ABC1-0001-dc'),
    
    ('ABC1-0001-rt'),
    
    ('ABC1-0001-ko'),
    
    ('ABC1-0001-ab-01'),
    
    ('ABC1-0001-ab-02'),
    
    ('ABC1-0001-ko-03'),
    
    ('ABC1-0001-ko-04'),
    
    ('ABC1-0001-dc-00'),
    
    ('ABC1-0001-dc-45'),
    
    ('ABC1-0001-rt-15'),
    
    ('ABC1-0001-rt-44')
    
    
    ;With CTE
    AS
    (
    select alpha,0 AS level,CAST(alpha AS varchar(max)) AS path
    from @t
    union all
    select t.alpha,c.level + 1,path + '/' + CAST( t.alpha AS varchar(max)) 
    from cte c
    join @t t
    on t.alpha like c.alpha + '%'
    and len(t.alpha) > len(c.alpha)
    )
    
    select alpha,maxlevel + 1 as maxlevel
    from cte c
    cross apply
    (
    select max(level) as maxlevel
    from cte
    where left(path,charindex('/',path+'/')-1) = c.alpha
    )c1
    where level = 0
    
    
    
    /*
    Output
    ----------------------------------
    alpha	        maxlevel
    -----------------------------------
    ABC1-0001	3
    ABC1-0001-ab	2
    ABC1-0001-dc	2
    ABC1-0001-rt	2
    ABC1-0001-ko	2
    ABC1-0001-ab-01	1
    ABC1-0001-ab-02	1
    ABC1-0001-ko-03	1
    ABC1-0001-ko-04	1
    ABC1-0001-dc-00	1
    ABC1-0001-dc-45	1
    ABC1-0001-rt-15	1
    ABC1-0001-rt-44	1
    
    
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    10 Mei 2018 11:13
  • Thankyou Visakh. Its working in sql server. But I wanted it in Oracle . can you please guide me.

    sania

    10 Mei 2018 12:48
  • Thankyou Visakh. Its working in sql server. But I wanted it in Oracle . can you please guide me.

    sania

    I dont know Oracle

    As suggested please post it in some Oracle forums like below

    https://community.oracle.com/community/database/developer-tools/sql_and_pl_sql


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    10 Mei 2018 13:09