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 update hierarchical query

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

  1. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    how to update hierarchical query only when leaf=1

    UPDATE CUSTOMER SET CLASS='SHIP-TO' (when the leaf=1)

    CREATE OR REPLACE PROCEDURE STAGING.SP_CUST_HIER_DNORM_2
    IS
    L_ERR_MSG VARCHAR2 (1000);
    l_cust_id NUMBER (10);
    v_cnt NUMBER (1);
    i NUMBER := 0;
    j NUMBER;
    BEGIN
    SP_LOG_ENTRY (CURRENT_TIMESTAMP,'SP_CUST_HIER_DNORM_2','START',NULL,NULL,NULL);

    DELETE CUST_HIER_DNORM_2;

    FOR v_cnt IN 2 .. 8
    LOOP

    FOR j IN (SELECT cust_id AS child_id
    FROM customer
    WHERE connect_by_isleaf=1 and
    LEVEL = v_cnt
    START WITH cust_id = 12638
    CONNECT BY PRIOR cust_id = cust_cust_id)
    LOOP
    BEGIN
    i := i + 1;

    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;


    SP_LOG_ENTRY (CURRENT_TIMESTAMP,'SP_CUST_HIER_DNORM_2','COMPLETE',NULL,
    i || ' records inserted ',NULL);
    COMMIT;



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

    j := SQL%ROWCOUNT;
    SP_LOG_ENTRY (CURRENT_TIMESTAMP,'SP_CUST_HIER_DNORM_2','COMPLETE',NULL,
    j || ' records updated ',NULL);
    COMMIT;


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