Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2012
    Posts
    1

    Unanswered: Calulation in hierarchy

    Hi,
    I need to calculate running quantity of an item using hierarchical approach. To calculate running quantity I need to multiply component quantity of current level with rest of the upper level's component quantity.

    I'm using following query to get all the level's component quantity but how can I multiply them within sql?

    SELECT LEVEL-1 "LEVEL_NUMBER",
    SYS_CONNECT_BY_PATH(TO_CHAR(COMPONENT_QUANTITY),' ') "PARENT_QUANTITY"
    FROM ods_inventory
    START WITH component_item = 'ABC'
    CONNECT BY NOCYCLE PRIOR component_item = assy_item

    For example, I have 4 level as shown below. I need to calculate running_quantity using SQL. Can someone help?

    Level Component_quantity RUNNING_QUANTITY
    ------ ----------------- ------------------
    1 1 1
    2 3 3 (1 * 3)
    3 6 18 (3 * 6)
    4 2 36 (18 * 2)

    Thanks,
    Kush

  2. #2
    Join Date
    Jan 2012
    Posts
    84
    Hi,
    if you are using oracle 11.2, you may do it using common table expression,
    here is an example:
    Code:
    create table ods_inventory(
      component_item varchar2(20 ),
      assy_item varchar2(20 ),
      Component_quantity NUMBER( 10, 0 )
    );
    
    insert into ods_inventory
    select 'ABC', null, 1 from dual union 
    select 'ABCD', 'ABC' , 3 from dual union
    select 'ABCDE', 'ABCD', 6 from dual union
    select 'XYZ', 'ABCDE', 2 from dual;
    
    commit;
    
    
    with qry( level_number, component_item, component_quantity, rn_qty ) as
    (
        SELECT 1, component_item, component_quantity, component_quantity
        FROM ods_inventory
        WHERE component_item = 'ABC'
        UNION ALL
        SELECT qry.level_number + 1, 
               ods.component_item, 
               ods.Component_quantity,
               ods.Component_quantity * qry.rn_qty
        FROM qry
        JOIN ods_inventory ods ON qry.component_item = ods.assy_item 
    )
    SELECT * FROM qry
    ;
    Code:
    LEVEL_NUMBER COMPONENT_ITEM       COMPONENT_QUANTITY RN_QTY
    ------------ -------------------- ------------------ ------
               1 ABC                                   1      1 
               2 ABCD                                  3      3 
               3 ABCDE                                 6     18 
               4 XYZ                                   2     36

Posting Permissions

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