Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Unanswered: Rolling balance on a SQL plus Report

    Does anyone know a way of creating a coloumn on a SQL plus report that is a rolling balance?

    I have a report which lists total inventory available and the amount ordered by order. I need to reduce the balance available by the amount ordered to create a column that is the calculation of the two fields.

  2. #2
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Which version of oracle are you using?

    You can use oracle analytiacl functions

    refer
    http://www.orafusion.com/oracf_sql.htm

    for some examples.

    If you give exact requirement, can try to build a SQL for what you require.
    Oracle can do wonders !

  3. #3
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    This is a very good information and usage on analytical functions:

    http://www.aw-bc.com/samplechapter/0201752948.pdf
    Oracle can do wonders !

  4. #4
    Join Date
    Jan 2004
    Posts
    9
    Thanks for the reply, just quickly looking at those examples gives me some hope. Here is the snippet of SQL plus script i'm using.

    BREAK ON Order_id SKIP 1 NODUPLICATES

    /* Column Headings and Formats */
    COLUMN Order_id HEADING 'Order' FORMAT A15
    COLUMN Order_Date HEADING 'Placed|Date' FORMAT A11
    COLUMN Line_id HEADING 'Line ID' FORMAT 9999
    COLUMN Sku_id HEADING 'Sku ID' Format A15
    COLUMN Qty_Ordered HEADING 'Qty Ordered' FORMAT 999999
    COLUMN TotalAvail HEADING 'Total|Qty|Available' FORMAT 999999
    COLUMN Rolling HEADING 'Total|Qty|Available2' FORMAT 999999
    COLUMN AllocOrd HEADING 'Allocate|Order?' FORMAT A15

    /* SQL Select statement */
    SELECT
    OH.Order_id,
    OH.Order_Date,
    OL.Line_id,
    OL.sku_ID,
    OL.Qty_Ordered,
    NVL((SUM(I.Qty_on_Hand)-SUM(I.Qty_Allocated)),0) AS TotalAvail,
    (SUM(I.Qty_on_Hand)-SUM(I.Qty_Allocated))-OL.Qty_Ordered AS Rolling,
    DECODE(SIGN(
    ( SUM(I.Qty_on_Hand)-SUM(I.Qty_Allocated) ) - OL.Qty_Ordered
    ), 1, 'Y ', 'N ') AS AllocOrd
    FROM Order_Header OH, Order_Line OL, Inventory I
    WHERE OH.Client_ID = '&1'
    AND OH.Status = 'Released'
    AND OH.Client_id = OL.Client_id
    AND OH.Order_id = OL.Order_id
    AND OL.Client_id = I.Client_id (+)
    AND OL.Sku_id = I.Sku_id (+)
    GROUP BY OH.Order_id, OH.Order_Date, OL.Line_id, OL.sku_ID, OL.Qty_Ordered
    ORDER BY OH.Order_Date, OH.Order_id, OL.Line_id
    ;

    As you can see i have already tried to get the balance calculation by taking away the qty ordered from the total available e.g

    ....(SUM(I.Qty_on_Hand)-SUM(I.Qty_Allocated))-OL.Qty_Ordered AS Rolling......

    But this only works once for each Order line product. The next calculation starts from the total avail again.


    What do you suggest?

  5. #5
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    I see what you want.

    You can customise this to your needs:

    Code:
    SQL> desc sales
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ORDER_ID                                           VARCHAR2(10)
     QTY                                                NUMBER
    
    SQL> select * from sales;
    
    ORDER_ID          QTY
    ---------- ----------
    O1                 10
    O2                 30
    O3                 20
    
    SQL> desc inventory
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     INV_QTY                                            NUMBER
    
    SQL> select * from inventory
      2  ;
    
       INV_QTY
    ----------
           400
    
    SQL> 
    SQL> select order_id, qty,
      2  sum(qty) over (order by order_id rows unbounded preceding) rolling_sales,
      3  inv_qty total_inv,
      4  inv_qty - sum(qty) over (order by order_id rows unbounded preceding)  balance_inv
      5  from sales, inventory;
    
    ORDER_ID          QTY ROLLING_SALES  TOTAL_INV BALANCE_INV
    ---------- ---------- ------------- ---------- -----------
    O1                 10            10        400         390
    O2                 30            40        400         360
    O3                 20            60        400         340
    Oracle can do wonders !

  6. #6
    Join Date
    Jan 2004
    Posts
    9
    Many thanks for the example. I have converted it to releate to my database but i'm getting this error:

    SQL> select ol.order_id, ol.qty_ordered
    2 sum(ol.qty_ordered) over (order by ol.order_id rows unbounded preceding) AS rollingord
    3 from order_line ol
    4 where ol.client_id = 'CLIENT'
    5 and ol.order_id = 'ORDERNO';

    sum(ol.qty_ordered) over (order by ol.order_id r
    *
    ERROR at line 2:
    ORA-00923: FROM keyword not found where expected

    ?

  7. #7
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Missing comma after ol.qty_ordered

    Code:
    SQL> select ol.order_id, ol.qty_ordered,
    2 sum(ol.qty_ordered) over (order by ol.order_id rows unbounded preceding) AS rollingord
    3 from order_line ol
    4 where ol.client_id = 'CLIENT'
    5 and ol.order_id = 'ORDERNO';
    Oracle can do wonders !

  8. #8
    Join Date
    Jan 2004
    Posts
    9
    lol!

    I have just noticed that, i'm sorry it works perfectly.

    Many thanks for your time on this cmasharma. I'm off to play with this new functionality you've shown me.

Posting Permissions

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