Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    42

    Unanswered: Hierarchy Rollup and totals Question

    Hello,
    I have a table with 18K+ rows in a hierarchical structure.
    I am using Oracle 10g XE, Windows XP Pro
    CREATE TABLE BDN_HOLD_C6_NEW
    (
    CODE CHAR(200 BYTE),
    CODEDESC CHAR(250 BYTE),
    BDN_LEVEL NUMBER,
    TAG CHAR(250 BYTE),
    CA CHAR(150 BYTE),
    WP CHAR(250 BYTE),
    PARENT CHAR(150 BYTE),
    BAC NUMBER,
    EAC NUMBER,
    VAC NUMBER,
    P_CMP NUMBER,
    P_SPN NUMBER,
    BCWS_*** NUMBER,
    BCWP_*** NUMBER,
    ACWP_*** NUMBER,
    CV_*** NUMBER,
    SV_*** NUMBER,
    BCWS_CUR NUMBER,
    BCWP_CUR NUMBER,
    ACWP_CUR NUMBER,
    CV_CUR NUMBER,
    SV_CUR NUMBER,
    WEIGHT CHAR(1 BYTE),
    FLAG CHAR(1 BYTE),
    SEQNO NUMBER
    )

    INSERT INTO COBRA.BDN_HOLD_C6_NEW (CODE, CODEDESC, TAG, CA, WP, BAC, EAC, VAC, P_CMP, P_SPN, BCWS_***, BCWP_***, ACWP_***, CV_***, SV_***, BCWS_CUR, BCWP_CUR, ACWP_CUR, CV_CUR, SV_CUR)
    VALUES (CODE, CODEDESC, BDN_LEVEL, A, 111111111, 111111111,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL)

    INSERT INTO COBRA.BDN_HOLD_C6_NEW (CODE, CODEDESC, TAG, CA, WP, BAC, EAC, VAC, P_CMP, P_SPN, BCWS_***, BCWP_***, ACWP_***, CV_***, SV_***, BCWS_CUR, BCWP_CUR, ACWP_CUR, CV_CUR, SV_CUR)
    VALUES (CODE, CODEDESC, BDN_LEVEL, AA, Cost Account 1, 111111111,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL)

    INSERT INTO COBRA.BDN_HOLD_C6_NEW (CODE, CODEDESC, TAG, CA, WP, BAC, EAC, VAC, P_CMP, P_SPN, BCWS_***, BCWP_***, ACWP_***, CV_***, SV_***, BCWS_CUR, BCWP_CUR, ACWP_CUR, CV_CUR, SV_CUR)
    VALUES (CODE, CODEDESC, BDN_LEVEL, AAA, Cost Account 1, Work Package 1,2.34,3,4,5,6,7,8,8,8,8,6,7,8,9)

    INSERT INTO COBRA.BDN_HOLD_C6_NEW (CODE, CODEDESC, TAG, CA, WP, BAC, EAC, VAC, P_CMP, P_SPN, BCWS_***, BCWP_***, ACWP_***, CV_***, SV_***, BCWS_CUR, BCWP_CUR, ACWP_CUR, CV_CUR, SV_CUR)
    VALUES (CODE, CODEDESC, BDN_LEVEL, AAA, Cost Account 1, Work Package 2,2.34,3,4,5,6,7,8,8,8,8,6,7,8,9)

    INSERT INTO COBRA.BDN_HOLD_C6_NEW (CODE, CODEDESC, TAG, CA, WP, BAC, EAC, VAC, P_CMP, P_SPN, BCWS_***, BCWP_***, ACWP_***, CV_***, SV_***, BCWS_CUR, BCWP_CUR, ACWP_CUR, CV_CUR, SV_CUR)
    VALUES (CODE, CODEDESC, BDN_LEVEL, AAA, Cost Account 1, Work Package 3,2.34,3,4,5,6,7,8,8,8,8,6,7,8,9)

    INSERT INTO COBRA.BDN_HOLD_C6_NEW (CODE, CODEDESC, TAG, CA, WP, BAC, EAC, VAC, P_CMP, P_SPN, BCWS_***, BCWP_***, ACWP_***, CV_***, SV_***, BCWS_CUR, BCWP_CUR, ACWP_CUR, CV_CUR, SV_CUR)
    VALUES (CODE, CODEDESC, BDN_LEVEL, AAA, Cost Account 1, Work Package 4,2.34,3,4,5,6,7,8,8,8,8,6,7,8,9)

    What I am trying to to is rollup the totals all the way up. The table size is around 18K records.


    Here is what I have tried so far:

    Code:
    <<final_updates_loop>>
    for y in (SELECT SEQNO,TAG FROM bdn_hold_c6_new ORDER BY tag)
         loop
              select    sum(bac),sum(eac),sum(vac),sum(bcws_***),sum(bcwp_***),sum(acwp_***),sum(cv_***),sum(sv_***),sum(bcws_cur),sum(bcwp_cur),sum(acwp_cur),sum(cv_cur),sum(sv_cur)
              into      sumBAC,sumEAC,sumVAC,sumBCWSc,sumBCWPc,sumACWPc,sumCVc,sumSVc,sumBCWS,sumBCWP,sumACWP,sumCV,sumSV
              from bdn_hold_c6_new where trim(tag) like (trim(y.tag) || '%');
    
               update bdn_hold_c6_new set   BAC=sumBAC,EAC=sumEAC,VAC=sumVAC,BCWS_***=sumBCWSc,BCWP_***=sumBCWPc,ACWP_***=sumACWPc,CV_***=sumCVc,SV_***=sumSVc,BCWS_CUR=sumBCWS,BCWP_CUR=sumBCWP,ACWP_CUR=sumACWP,CV_CUR=sumCV,SV_CUR=sumSV   where seqno = y.seqno;
    
    end loop final_updates_loop;
    This MAY be working, but after 20 minutes I canceled the execution. Any pointers on how I can do this rollup? I wanted to update the existing table with the rollup data for later presentation.


    The columns that needed totaling/rollup are (BAC,EAC,VAC,P_CMP,P_SPN,BCWS_***,BCWP_***,ACWP_** *,CV_***,SV_***, BCWS_CUR, BCWP_CUR,ACWP_CUR,CV_CUR,SV_CUR)
    This is the way the table needs to look after the rollup:
    CODE, CODEDESC, BDN_LEVEL, A, 111111111, 111111111,9.36,12,16,20,24,28,32,32,32,32,24,28,32 ,36
    CODE, CODEDESC, BDN_LEVEL, AA, Cost Account 1, 111111111,9.36,12,16,20,24,28,32,32,32,32,24,28,32 ,36
    CODE, CODEDESC, BDN_LEVEL, AAA, Cost Account 1, Work Package 1,2.34,3,4,5,6,7,8,8,8,8,6,7,8,9
    CODE, CODEDESC, BDN_LEVEL, AAA, Cost Account 1, Work Package 2,2.34,3,4,5,6,7,8,8,8,8,6,7,8,9
    CODE, CODEDESC, BDN_LEVEL, AAA, Cost Account 1, Work Package 3,2.34,3,4,5,6,7,8,8,8,8,6,7,8,9
    CODE, CODEDESC, BDN_LEVEL, AAA, Cost Account 1, Work Package 4,2.34,3,4,5,6,7,8,8,8,8,6,7,8,9

    Thanks...

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Your example is very hard to follow, and impossible to recreate since the SQL is invalid (string values are not quoted). Your PL/SQL seems to be rolling up based on values in the TAG column, yet your example rolls up on the CA column ('A','AA','AAA').

    Maybe something like this:
    Code:
    begin
      update BDN_HOLD_C6_NEW parent
      set (BAC, EAC, VAC, ...) = 
      (select sum(ABC), sum(EAC), sum(VAC), ...
       from   BDN_HOLD_C6_NEW child
       where child.ca like parent.ca||'_');
    end;

  3. #3
    Join Date
    Nov 2002
    Posts
    42
    Quote Originally Posted by andrewst
    Your example is very hard to follow, and impossible to recreate since the SQL is invalid (string values are not quoted). Your PL/SQL seems to be rolling up based on values in the TAG column, yet your example rolls up on the CA column ('A','AA','AAA').

    Maybe something like this:
    Code:
    begin
      update BDN_HOLD_C6_NEW parent
      set (BAC, EAC, VAC, ...) = 
      (select sum(ABC), sum(EAC), sum(VAC), ...
       from   BDN_HOLD_C6_NEW child
       where child.ca like parent.ca||'_');
    end;
    You're right, my question is unclear. I apologize. Let me try to clear it up.

    I have a table with 18K+ rows, these rows are organized in a hierarchical structure according to a column called "tag" at the lowest level of the hierarchy numerical totals exist. I want to roll those totals up the hierarchy.

    Here is an example of that data structure:

    Code:
    Code-1 AAA Need Totals Rollup
      Code-2 AAAA Need Totals Rollup
        Code-3 AAAAA Need Totals Rollup
          Cost Account-1 AAAAAA Need Totals Rollup
            WorkPackage-1 AAAAAAA Totals Exist Here
            WorkPackage-2 AAAAAAB Totals Exist Here
            WorkPackage-3 AAAAAAC Totals Exist Here
          Cost Account-2 AAAAAB Need Totals Rollup
            WorkPackage-1 AAAAABA Totals Exist Here
            WorkPackage-2 AAAAABB Totals Exist Here
            WorkPackage-3 AAAAABC Totals Exist Here
    What I need is for the totals at the work package level to rollup all the way up the hierarchy according to their tag. The second column in the example is the hierarchy column "tag".

    The structure of the data is immutable.

    I hope this is more clear.

    Thanks!

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    How do you know whether a row is a Work Package or not? I'll assume it is because column WP is not null for a Work Package, null for a rollup.

    Then does this work?
    Code:
    begin
      update BDN_HOLD_C6_NEW rollup
      set (BAC, EAC, VAC, ...) = 
      (select sum(ABC), sum(EAC), sum(VAC), ...
       from   BDN_HOLD_C6_NEW wp
       where wp.wp is not null
       and    wp.tag like rollup.tag|'%')
      where rollup.wp is null;
    end;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •