1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to remove columns not ending with leaf nodes in hierarchical queries

Discussion in 'SQL PL/SQL' started by ashwanth, Apr 15, 2014.

  1. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    how to remove columns not ending with leaf nodes in hierarchical queries .can someone share an example

    SELECT cust_id AS child_id
    FROM customer
    WHERE LEVEL = v_cnt
    START WITH cust_id = 12660
    CONNECT BY PRIOR cust_id = cust_cust_id


    i have written a stored procedure for this.

    CREATE OR REPLACE PROCEDURE FRONTROOM.SP_CUST_HIER_DENORM_2

    IS
    L_ERR_MSG VARCHAR2(1000);
    l_cust_id number(10);
    v_cnt number(1);
    BEGIN
    SP_LOG_ENTRY(CURRENT_TIMESTAMP,'SP_CUST_HIER_DENORM_2','START',NULL,NULL,NULL);

    -- select cust_id into l_cust_id from customer where true_gcdb_source_key ='9900000001';


    delete CUST_HIER_DNORM_2;

    for v_cnt in 2..6
    loop


    -- v_cnt := 6;
    For j in ( SELECT cust_id AS child_id
    FROM customer
    WHERE LEVEL = v_cnt
    START WITH cust_id = 12660
    CONNECT BY PRIOR cust_id = cust_cust_id)

    LOOP
    begin

    INSERT INTO CUST_HIER_DNORM_2
    (LVL_1,LVL_2,LVL_3,LVL_4,LVL_5,LVL_6,LVL_7,LVL_8 )
    SELECT SUM(DECODE(LEVEL,v_cnt,cust_id)) LVL_1 ,
    SUM(DECODE(LEVEL,v_cnt-1,cust_id)) LVL_2 ,
    SUM(DECODE(LEVEL,v_cnt-2,cust_id)) LVL_3 ,
    SUM(DECODE(LEVEL,v_cnt-3,cust_id)) LVL_4 ,
    SUM(DECODE(LEVEL,v_cnt-4,cust_id)) LVL_5 ,
    SUM(DECODE(LEVEL,v_cnt-5,cust_id)) LVL_6 ,
    SUM(DECODE(LEVEL,v_cnt-6,cust_id)) LVL_7 ,
    SUM(DECODE(LEVEL,v_cnt-7,cust_id)) LVL_8
    FROM CUSTOMER
    WHERE LEVEL <=v_cnt
    START WITH cust_id = j.child_id
    CONNECT BY cust_id = PRIOR cust_cust_id ;
    EXCEPTION
    WHEN OTHERS
    THEN
    SP_LOG_ENTRY (CURRENT_TIMESTAMP,
    'SP_CUST_HIER_DENORM_2',
    'ERROR WHILE INSERTING ',
    SQLERRM,
    '* ' || j.CHILD_ID || ' *',NULL

    );
    END;
    END LOOP ;
    end loop;

    COMMIT;



    UPDATE cust_hier_dnorm_2 cd
    SET LVL_1_DESC =
    (SELECT c.true_gcdb_source_key
    FROM customer c
    WHERE cd.lvl_1 = C.CUST_ID ),
    LVL_2_DESC =
    (SELECT c.true_gcdb_source_key
    FROM customer c
    WHERE cd.lvl_2 = C.CUST_ID ),
    LVL_3_DESC =
    (SELECT c.true_gcdb_source_key
    FROM customer c
    WHERE cd.lvl_3 = C.CUST_ID ),
    LVL_4_DESC =
    (SELECT c.true_gcdb_source_key
    FROM customer c
    WHERE cd.lvl_4 = C.CUST_ID ),
    LVL_5_DESC =
    (SELECT c.true_gcdb_source_key
    FROM customer c
    WHERE cd.lvl_5 = C.CUST_ID ),
    LVL_6_DESC =
    (SELECT c.true_gcdb_source_key
    FROM customer c
    WHERE cd.lvl_6 = C.CUST_ID ),
    LVL_7_DESC =
    (SELECT c.true_gcdb_source_key
    FROM customer c
    WHERE cd.lvl_7 = C.CUST_ID ),
    LVL_8_DESC =
    (SELECT c.true_gcdb_source_key
    FROM customer c
    WHERE cd.lvl_8 = C.CUST_ID );

    commit;


    SP_LOG_ENTRY(CURRENT_TIMESTAMP,'SP_CUST_HIER_DENORM_2','START',NULL,NULL,NULL);

    EXCEPTION
    WHEN OTHERS THEN
    L_ERR_MSG :=SQLERRM;
    SP_LOG_ENTRY(CURRENT_TIMESTAMP,'SP_LOAD_CUST_HIER_DENORM_2',NULL,L_ERR_MSG,NULL,NULL);
    END;
    /


    currently my output is in this format

    lvl 1 lvl2 lvl3 lvl4 lvl5 lvl6
    12660 12389 12517 12476 12557 3953(leaf node)
    12660 12389 12517 12476 12557 3978(leaf node)
    12660 12389 12517 12476 12557 3992(leaf node)
    12660 12389 12517 12476 12557
    12660 12389 12517 12476
    12660 12389 12517


    desired output
    lvl 1 lvl2 lvl3 lvl4 lvl5 lvl6
    12660 12389 12517 12476 12557 3953(leaf node)
    12660 12389 12517 12476 12557 3978(leaf node)
    12660 12389 12517 12476 12557 3992(leaf node)
    which means removing the columns not ending with leaf nodes.

    Its a denorm table.all the records are inserted to denormalised table
     
  2. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    select * from cust_hier_dnorm_2 where lvl_6=3953
    union
    select * from cust_hier_dnorm_2 where lvl_5=12557
    union
    select * from cust_hier_dnorm_2 where lvl_4=12476

    data

    LVL_1 LVL_2 LVL_3 LVL_4 LVL_5 LVL_6 LVL_7 lvl_8
    12389 12517 12476 12557 3953
    12389 12517 12476 12557 3978
    12389 12517 12476 12557 3992
    12389 12517 12476 12557
    12389 12517 12476


    desired result

    12389 12517 12476 12557 3953
    12389 12517 12476 12557 3978
    12389 12517 12476 12557 3992

    which means removing the columns not ending with leaf nodes.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    output of only child members:

    Code (SQL):

    SELECT cust_id AS child_id
    FROM hr.customer
    WHERE   connect_by_isleaf = 1
    START WITH cust_id = 12660
    CONNECT BY PRIOR cust_id = cust_cust_id
     

    Try this:
    Code (SQL):



    SELECT * FROM (
    SELECT SUM(DECODE(LEVEL,v_cnt,cust_id)) OVER () LVL_1 ,
    SUM(DECODE(LEVEL,v_cnt-1,cust_id)) OVER () LVL_2 ,
    SUM(DECODE(LEVEL,v_cnt-2,cust_id)) OVER () LVL_3 ,
    SUM(DECODE(LEVEL,v_cnt-3,cust_id)) OVER () LVL_4 ,
    SUM(DECODE(LEVEL,v_cnt-4,cust_id)) OVER () LVL_5 ,
    SUM(DECODE(LEVEL,v_cnt-5,cust_id)) OVER () LVL_6 ,
    SUM(DECODE(LEVEL,v_cnt-6,cust_id)) OVER () LVL_7 ,
    SUM(DECODE(LEVEL,v_cnt-7,cust_id)) OVER () LVL_8,
    connect_by_isleaf is_leaf
    FROM CUSTOMER
    WHERE LEVEL <=v_cnt
    START WITH cust_id = j.child_id
    CONNECT BY cust_id = PRIOR cust_cust_id )
    WHERE is_leaf = 1;