Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2012
    Posts
    5

    Unanswered: Performing calculations on sums unioned together

    I have a query that works perfectly, I get the exact values I need, except I need to add another column.


    The top union provides the current month and future 12 months, where the bottom union provides the past 12 months.


    Here is the select statement, leaving out the tables and joins:

    SELECT sales_date as Sales_Date,
    SUM (proj_on_hand * xx_conso_cost) as Projected_on_hand,
    SUM((xx_sales_fcst + xx_trend_fcst)*xx_consol_cost) as Cost_of_FCST
    Group by sales_date

    union

    SELECT sales_date as Sales_Date,
    (SUM(xx_prod_inv))*1000 as Projected_on_hand,
    (SUM(xx_prod_cost))*1000 as Cost_of_FCST
    group by sales_date


    The select statement returns results like the following:
    Code:
    Month    -     Projected_on_hand             -          Cost_of_FCST
    Oct 12   -            10                     -             3
    Nov 12   -             8                      -            5
    Dec 12   -             9                     -             4
    Jan 12   -             11                    -             6
    etc

    I want to keep displaying the month, projected_on_hand, and cost_of_fcst, but I also want to display an additional column.

    I want to the sum the previous 3 months of cost_of_fcst and divide them by the sum of the previous 3 months of projected on_hand.

    So the new column for Jan 12 should be .44 because (3+5+4)/(10+8+9)
    The new column for Feb 12 should be .53 because (5+4+6)/(8+9+11)


    I am in an oracle database environment, can I do this using a cursor? Any idea on how I would go about that?

    Or do I have to go about creating a global temporary table?

  2. #2
    Join Date
    Dec 2012
    Posts
    5
    I figured it out, now all I need to do is write a procedure to tie the steps together.



    create global temporary table temp_subd_net_need
    on commit preserve rows
    as (select statement)

    ;

    SELECT T1.Sales_Date,
    T1.Proj_on_Hand,
    T1.Cost_Of_Forecast,
    round(((12 * (T2.Cost_of_forecast + T3.Cost_of_forecast + T4.Cost_of_forecast ))/(T2.Proj_on_hand + T3.Proj_on_hand + T4.Proj_on_hand)),2) as Turn_Rate

    FROM temp_subd_net_need T1,
    temp_subd_net_need T2,
    temp_subd_net_need T3,
    temp_subd_net_need T4
    WHERE T1.Sales_Date = add_months (T2.Sales_date , -1)
    AND T1.Sales_Date = add_months (T3.Sales_date , -2)
    AND T1.Sales_Date = add_months (T4.Sales_date , -3)
    Last edited by MTSkibum; 12-19-12 at 19:24.

  3. #3
    Join Date
    Dec 2012
    Posts
    5
    And just incase anyone is curious about the whole sql



    CREATE global TEMPORARY TABLE temp_subd_net_need ON COMMIT preserve rows
    AS
    (SELECT net.sales_date Sales_Date,
    DECODE(SIGN(SUM(ROUND((NVL(net.xx_net_need, 0) * NVL(net.xx_conso_cost, 0)),0))), -1,0, SUM(ROUND((NVL(net.xx_net_need, 0) * NVL(net.xx_conso_cost, 0)),2))) Net_Need,
    SUM(ROUND((NVL(net.proj_on_hand, 0) * NVL(net.xx_conso_cost, 0)),0)) Proj_on_hand,
    SUM(ROUND((NVL(net.safety_stk, 0) * NVL(net.xx_conso_cost, 0)),0)) Safety_stock,
    SUM(ROUND(((NVL(sda.xx_sales_fcst, 0) + (NVL(sda.xx_trend_fcst, 0)) )* NVL(sda.xx_consol_cost, 0)),0)) Cost_of_Forecast,
    SUM(ROUND(((NVL(sda.xx_sales_fcst, 0) + (NVL(sda.xx_trend_fcst, 0))) * NVL(sda.xx_asp, 0)),0)) Revenue_of_Forecast,
    (SUM(ROUND(((NVL(sda.xx_sales_fcst, 0) + (NVL(sda.xx_trend_fcst, 0))) * NVL(sda.xx_asp, 0)),0)) - SUM(ROUND(((NVL(sda.xx_sales_fcst, 0)+ (NVL(sda.xx_trend_fcst, 0)) )* NVL(sda.xx_consol_cost, 0)),0)) ) Forecasted_Margin
    FROM dm01.supply_plan_data net,
    dm01.location loc,
    dm01.T_EP_MIS_SUBDIS sub,
    DM01.items itm,
    dm01.sales_data sda
    WHERE net.location_id =loc.location_id
    AND loc.T_EP_MIS_SUBDIS_EP_ID = sub.T_EP_MIS_SUBDIS_EP_ID
    AND net.item_id =itm.item_id
    AND loc.location_id = sda.location_id
    AND net.sales_date = sda.sales_date
    AND itm.item_id = sda.item_id
    AND sub.MIS_SUBDIS_DESC = 'Germany Sub-Dist'
    GROUP BY net.sales_date
    UNION
    SELECT sda.sales_date Sales_Date,
    0 AS Net_Need,
    (SUM(ROUND(NVL(sda.xx_prod_inv, 0) ,2)))*1000 Proj_on_hand,
    0 AS Safety_stock,
    (SUM(ROUND(NVL(sda.xx_prod_cost, 0) ,2)))*1000 Cost_of_Forecast,
    (SUM(ROUND(NVL(sda.xx_prod_rev, 0) ,2))) *1000 Revenue_of_Forecast,
    ((SUM(ROUND(NVL(sda.xx_prod_rev, 0) ,2)))-(SUM(ROUND(NVL(sda.xx_prod_cost, 0) ,2))))*1000 Forecasted_Margin
    FROM dm01.sales_data sda,
    dm01.location loc,
    dm01.T_EP_MIS_SUBDIS sub,
    DM01.items itm,
    DM01.T_EP_ITEM epi
    WHERE sda.item_id =itm.item_id
    AND itm.T_EP_ITEM_EP_ID =epi.T_EP_ITEM_EP_ID
    AND sda.location_id = loc.location_id
    AND loc.T_EP_MIS_SUBDIS_EP_ID = sub.T_EP_MIS_SUBDIS_EP_ID
    AND sda.sales_date >= add_months (sysdate , -5)
    AND sda.Sales_date <= add_months (sysdate , -1)
    AND sub.MIS_SUBDIS_DESC = 'Germany Sub-Dist'
    GROUP BY sda.sales_date
    )
    ;
    SELECT T1.Sales_Date,
    T1.Net_Need,
    T1.Proj_on_Hand,
    T1.Safety_Stock,
    T1.Cost_Of_Forecast,
    T1.Revenue_Of_Forecast,
    T1.Forecasted_Margin,
    ROUND(((12 * (T2.Cost_of_forecast + T3.Cost_of_forecast + T4.Cost_of_forecast ))/(T2.Proj_on_hand + T3.Proj_on_hand + T4.Proj_on_hand)),2) Turn_Rate
    FROM temp_subd_net_need T1,
    temp_subd_net_need T2,
    temp_subd_net_need T3,
    temp_subd_net_need T4
    WHERE T1.Sales_Date = add_months (T2.Sales_date , -1)
    AND T1.Sales_Date = add_months (T3.Sales_date , -2)
    AND T1.Sales_Date = add_months (T4.Sales_date , -3)

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't know whether code you posted does what you wanted (but, as you said it does, fine). However: I'd suggest you NOT to use a global temporary table - it is unnecessary (is your background SQL Server, perhaps, and now you switched to Oracle?).

    The same could have been done with an inline view or - maybe even better - a WITH factoring clause. Here are both examples:
    Code:
    SQL> -- Inline view
    SQL> select sum(s.salary) sum_sal
      2  from (select e.sal salary
      3        from emp e
      4       ) s;
    
       SUM_SAL
    ----------
         40225
    
    SQL> -- With factoring clause
    SQL> with salaries as
      2    (select e.sal salary
      3     from emp e
      4    )
      5  select sum(s.salary) sum_sal
      6  from salaries s;
    
       SUM_SAL
    ----------
         40225
    
    SQL>
    It means that you'd put the GTT SELECT statement into one of these forms (either an inline view, or a WITH factoring clause) and avoid an unnecessary intermediate table.

  5. #5
    Join Date
    Dec 2012
    Posts
    5
    Quote Originally Posted by Littlefoot View Post
    I don't know whether code you posted does what you wanted (but, as you said it does, fine). However: I'd suggest you NOT to use a global temporary table - it is unnecessary (is your background SQL Server, perhaps, and now you switched to Oracle?).

    I do not have any background in sql, I am a chemical engineer, I work at a fortune 500 company and my job is to make sure the oracle implementation answers all of the needs of operations.

    I have been working with sql less than 2 months.


    Quote Originally Posted by Littlefoot View Post

    The same could have been done with an inline view or - maybe even better - a WITH factoring clause. Here are both examples:
    Code:
    SQL> -- Inline view
    SQL> select sum(s.salary) sum_sal
      2  from (select e.sal salary
      3        from emp e
      4       ) s;
    
       SUM_SAL
    ----------
         40225
    
    SQL> -- With factoring clause
    SQL> with salaries as
      2    (select e.sal salary
      3     from emp e
      4    )
      5  select sum(s.salary) sum_sal
      6  from salaries s;
    
       SUM_SAL
    ----------
         40225
    
    SQL>
    It means that you'd put the GTT SELECT statement into one of these forms (either an inline view, or a WITH factoring clause) and avoid an unnecessary intermediate table.

    Before I posted here I tried with, and it is to slow in this case. I will try and give the inline view a try, I just googled it and it looked like it might work.

    Thanks for the help.

  6. #6
    Join Date
    Dec 2012
    Posts
    5
    The problem with using an inline view in this case is that I have to alias the inline view 4 times to join the inline view to itself.

    SELECT T1.Sales_Date,
    T1.Proj_on_Hand,
    T1.Cost_Of_Forecast,
    ROUND(((12 * (T2.Cost_of_forecast + T3.Cost_of_forecast + T4.Cost_of_forecast ))/(T2.Proj_on_hand + T3.Proj_on_hand + T4.Proj_on_hand)),2) Turn_Rate

    FROM temp_subd_net_need T1,
    temp_subd_net_need T2,
    temp_subd_net_need T3,
    temp_subd_net_need T4

    WHERE T1.Sales_Date = add_months (T2.Sales_date , -1)
    AND T1.Sales_Date = add_months (T3.Sales_date , -2)
    AND T1.Sales_Date = add_months (T4.Sales_date , -3)







    My question is, can i create 4 alias's off of the same inline view, or do I have to run the inline view 4 different times?

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    WITH factoring clause successfully substitutes an inline view. It makes it possible to use it in as many places in your query as you need it (which eliminates 4 inline views in your case). I can't tell whether it will (or not) slow the query down; there's that "performance tuning" stuff which helps making things faster.

    If you wish, have a look at OraFAQ Forum's Performance Tuning - the very first topic discusses How to tune SQL or Identify Performance Problem and Bottleneck; maybe you'll find it interesting.

Posting Permissions

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