Need a Query which will use previous columns for current record calculation — oracle-tech

    Forum Stats

  • 3,702,038 Users
  • 2,239,552 Discussions
  • 7,835,729 Comments

Discussions

Need a Query which will use previous columns for current record calculation

User_02F0CUser_02F0C Posts: 11 Blue Ribbon

I have a table in which there is a hierarchy of product and its ingredients and F_LEVEL is showing us hierarchal level.

Now I need to calculate 2 new columns (FACTOR,QTY_REQURIED) which will be showing the ingredient quantity required for the production of finished items of sale order.

1) Formula for FACTOR column will be:

Ingredient_qty/Product_Qty

*FACTOR column will be 0 for maximum f_level.

**For the remaining hierarchal levels, Product_Qty will be used of that records whose product is current record ingredient.

2) Formula for QTY_REQURIED column will be:

Sale_Order_Qty * F_Level

QTY_REQURIED for f_level 1 will be SALE_ORDER_QTY

for other f_levels, Sale_Order_Qty and F_Level will be used of that line whose ingredient is same as current record product.

Excel File has been attached and formula's has been applied with above logic.

-------------------------------

**Create Table Command for Creating Table**

CREATE TABLE FORMULA_DETAIL

(

 PRODUCT_ITEM_ID    NUMBER,

 INGREDIENT_ITEM_ID   NUMBER,

 ORGANIZATION_ID    NUMBER,

 FORMULA_ID       NUMBER,

 FORMULALINE_ID     NUMBER,

 PRODUCT_QTY      NUMBER,

 INGREDIENT_QTY     NUMBER,

 FINISHED_GOOD_ITEM_ID NUMBER,

 F_LEVEL        NUMBER,

 SALE_ORDER_QTY     NUMBER

)

**Insert Commands for inserting test data**

INSERT ALL

INTO FORMULA_DETAIL (PRODUCT_ITEM_ID,INGREDIENT_ITEM_ID,ORGANIZATION_ID,FORMULA_ID,FORMULALINE_ID,PRODUCT_QTY,INGREDIENT_QTY,FINISHED_GOOD_ITEM_ID,F_LEVEL,SALE_ORDER_QTY)

VALUES (17484,2638968,118,1695,15460,1000,1000,17484,1,250)

INTO FORMULA_DETAIL (PRODUCT_ITEM_ID,INGREDIENT_ITEM_ID,ORGANIZATION_ID,FORMULA_ID,FORMULALINE_ID,PRODUCT_QTY,INGREDIENT_QTY,FINISHED_GOOD_ITEM_ID,F_LEVEL,SALE_ORDER_QTY)

VALUES (17484,2638967,118,1695,15459,1000,1000,17484,1,250)

INTO FORMULA_DETAIL (PRODUCT_ITEM_ID,INGREDIENT_ITEM_ID,ORGANIZATION_ID,FORMULA_ID,FORMULALINE_ID,PRODUCT_QTY,INGREDIENT_QTY,FINISHED_GOOD_ITEM_ID,F_LEVEL,SALE_ORDER_QTY)

VALUES (2638968,2638966,118,1991,20520,1000,1000,17484,2,0)

INTO FORMULA_DETAIL (PRODUCT_ITEM_ID,INGREDIENT_ITEM_ID,ORGANIZATION_ID,FORMULA_ID,FORMULALINE_ID,PRODUCT_QTY,INGREDIENT_QTY,FINISHED_GOOD_ITEM_ID,F_LEVEL,SALE_ORDER_QTY)

VALUES (2638967,2638966,118,1989,20487,1000,1000,17484,2,0)

INTO FORMULA_DETAIL (PRODUCT_ITEM_ID,INGREDIENT_ITEM_ID,ORGANIZATION_ID,FORMULA_ID,FORMULALINE_ID,PRODUCT_QTY,INGREDIENT_QTY,FINISHED_GOOD_ITEM_ID,F_LEVEL,SALE_ORDER_QTY)

VALUES (2638966,2638965,118,4027,50030,1000,6759.2,17484,3,0)

INTO FORMULA_DETAIL (PRODUCT_ITEM_ID,INGREDIENT_ITEM_ID,ORGANIZATION_ID,FORMULA_ID,FORMULALINE_ID,PRODUCT_QTY,INGREDIENT_QTY,FINISHED_GOOD_ITEM_ID,F_LEVEL,SALE_ORDER_QTY)

VALUES (2638966,2638965,118,4027,50030,1000,6759.2,17484,3,0)

INTO FORMULA_DETAIL (PRODUCT_ITEM_ID,INGREDIENT_ITEM_ID,ORGANIZATION_ID,FORMULA_ID,FORMULALINE_ID,PRODUCT_QTY,INGREDIENT_QTY,FINISHED_GOOD_ITEM_ID,F_LEVEL,SALE_ORDER_QTY)

VALUES (2638965,2634101,118,4376,53910,1000,149.28,17484,4,0)

INTO FORMULA_DETAIL (PRODUCT_ITEM_ID,INGREDIENT_ITEM_ID,ORGANIZATION_ID,FORMULA_ID,FORMULALINE_ID,PRODUCT_QTY,INGREDIENT_QTY,FINISHED_GOOD_ITEM_ID,F_LEVEL,SALE_ORDER_QTY)

VALUES (2638965,2634101,118,4376,53910,1000,149.28,17484,4,0)

INTO FORMULA_DETAIL (PRODUCT_ITEM_ID,INGREDIENT_ITEM_ID,ORGANIZATION_ID,FORMULA_ID,FORMULALINE_ID,PRODUCT_QTY,INGREDIENT_QTY,FINISHED_GOOD_ITEM_ID,F_LEVEL,SALE_ORDER_QTY)

VALUES (2634101,6459,118,4,9,1000,33.79,17484,5,0)

INTO FORMULA_DETAIL (PRODUCT_ITEM_ID,INGREDIENT_ITEM_ID,ORGANIZATION_ID,FORMULA_ID,FORMULALINE_ID,PRODUCT_QTY,INGREDIENT_QTY,FINISHED_GOOD_ITEM_ID,F_LEVEL,SALE_ORDER_QTY)

VALUES (2634101,6459,118,4,9,1000,33.79,17484,5,0)

SELECT 1 FROM DUAL

Answers

  • Frank KulashFrank Kulash Moderator Posts: 39,603 Red Diamond

    Hi,

    Thanks for posting the sample data. Don't forget to post the exact results you want from the given data, and explain, step by step, how you get the values in the factor and qty_required columns for a couple of the rows.

    Always say which version of Oracle you're using (e.g., 12.2.0.2.0).

  • User_02F0CUser_02F0C Posts: 11 Blue Ribbon

    Hi,

    I am using Oracle Database Server 12.1.0.2.0 and Oracle Applications : 12.2.9

    Following are the results and formula required from above scenario.


  • Frank KulashFrank Kulash Moderator Posts: 39,603 Red Diamond
    edited October 7

    Hi,

    Sorry, I still don't understand. You started by saying you wanted two derived columns, but the desired results you posted seem to have four derived columns, and some of them include things like 'K9*L9' that don't seem to correspond to anything in your table or description.

    About the factor column, you said "For the remaining hierarchal levels, Product_Qty will be used of that records whose product is current record ingredient.", but you didn't say what "product" or "current record ingredient" mean. Ignoring that requirement, it looks like you can derive factor this way:

    SELECT  ingredient_qty
    ,     product_qty
    ,     CASE
           WHEN f_level = MAX (f_level) OVER ()
           THEN 0
           ELSE ingredient_qty / product_qty
         END AS factor
    FROM   formula_detail
    ORDER BY f_level
    ;
    

    This gets the results you posted (factor column only) for the sample data you posted.

    If this table represents a hierarchy (or a bill of materials) it might be helpful if you explained how that works, especially how you tell if row A is a child of row B. Can a child have two (or more) parents?


    Do all the columns in the sample data play some role in this problem? Explain.

  • User_02F0CUser_02F0C Posts: 11 Blue Ribbon

    Hi,


    I want to have two derived formula, I have inserted their excel formula to show calculation. you can ignore that.

    Relation between child and their parents is Product_Item_Id and its Ingredient_Item_Id.

    Level 1 has ingredient, which is product of level 2. Ingredient of level 2 is a product of level 3 and so on.

    To derive factor, Product_qty value should be use of its child.

    For e.g.: You are calculating factor for level 2. Then,

    Ingredient_qty is of level 2. Product_qty value should be of level 3 where level 2 ingredient is equal to level 3 product.

    ----

    For another derived column QTY_REQURIED.

    FACTOR*QTY_REQUIRED.

    Both columns will be use of previous level.

    For e.g.: You are calculating QTY_REQURIED for level 2. Then,

    qty_required and factor value should be of level 1 where level 2 product is equal to level 1 ingredient.

  • Frank KulashFrank Kulash Moderator Posts: 39,603 Red Diamond

    Sorry, that doesn't answer any of my questions.

    It doesn't help to say "Product_qty value should be use of its child" if you don't explain what a child is.

    What does "level 2 ingredient is equal to level 3 product" mean? There can be multiple rows with the same level. Is the "level 2 ingredient" one of them? If so, which one? Is the "level 2 ingredient" some kind of aggregate, like the AVG or MAX? Please explain.

  • User_02F0CUser_02F0C Posts: 11 Blue Ribbon

    I am explaining parent child relation again.

    Parent's ingredient is a product of child.

    If you can see data.

    Ingredient_item_id of level 1 is a Product_item_id of level 2

    Ingredient_item_id of level 2 is a Product_item_id of level 3

    Ingredient_item_id of level 3 is a Product_item_id of level 4 and so on.

    One product_item_id can have two ingredient_item_id, so that will be in same level. (for e.g level 1)

    and two product_item_id can have same ingredient (for e.g. level 2)

    You can use MAX for 2 ingredients at same level.

    I hope I have answer your question.

  • Jan GorkowJan Gorkow Posts: 7 Blue Ribbon

    Hi User_02F0C,

    how about this:

    WITH
      factor
      AS
       (SELECT DISTINCT formula_detail.product_item_id,
                formula_detail.ingredient_item_id,
                formula_detail.organization_id,
                formula_detail.formula_id,
                formula_detail.formulaline_id,
                formula_detail.product_qty,
                formula_detail.ingredient_qty,
                formula_detail.finished_good_item_id,
                formula_detail.f_level,
                formula_detail.sale_order_qty,
                NVL (
                  formula_detail.ingredient_qty
                 / (SELECT DISTINCT tmp.product_qty
                    FROM formula_detail tmp
                   WHERE   tmp.finished_good_item_id =
                        formula_detail.finished_good_item_id
                      AND tmp.product_item_id =
                        formula_detail.ingredient_item_id
                      AND tmp.f_level =
                        formula_detail.f_level + 1),
                 0)  AS factor
         FROM formula_detail),
      qty_required (product_item_id,
             ingredient_item_id,
             organization_id,
             formula_id,
             formulaline_id,
             product_qty,
             ingredient_qty,
             finished_good_item_id,
             f_level,
             sale_order_qty,
             factor,
             qty_required)
      AS
       (SELECT factor.product_item_id,
           factor.ingredient_item_id,
           factor.organization_id,
           factor.formula_id,
           factor.formulaline_id,
           factor.product_qty,
           factor.ingredient_qty,
           factor.finished_good_item_id,
           factor.f_level,
           factor.sale_order_qty,
           factor.factor,
           factor.sale_order_qty  AS qty_required
         FROM factor
        WHERE factor.f_level = 1
        UNION ALL
        SELECT factor.product_item_id,
           factor.ingredient_item_id,
           factor.organization_id,
           factor.formula_id,
           factor.formulaline_id,
           factor.product_qty,
           factor.ingredient_qty,
           factor.finished_good_item_id,
           factor.f_level,
           factor.sale_order_qty,
           factor.factor,
           qty_required.factor * qty_required.qty_required  AS qty_required
         FROM factor, qty_required
        WHERE   factor.finished_good_item_id =
             qty_required.finished_good_item_id
           AND factor.product_item_id = qty_required.ingredient_item_id)
    SELECT qty_required.product_item_id,
        qty_required.ingredient_item_id,
        qty_required.organization_id,
        qty_required.formula_id,
        qty_required.formulaline_id,
        qty_required.product_qty,
        qty_required.ingredient_qty,
        qty_required.finished_good_item_id,
        qty_required.f_level,
        qty_required.sale_order_qty,
        qty_required.factor,
        qty_required.qty_required
     FROM qty_required;
    

    I think this meets your needs.

    Best regards,

    Jan

Sign In or Register to comment.