1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to Calculate a new fieild based on another column running difference .,

Discussion in 'SQL PL/SQL' started by asita, Sep 9, 2014.

  1. asita

    asita Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hello Good Afternoon,



    Could you please help me out to calculate a column value and insert into a table via oracle procedure



    First How to Find ta new column value (Bal Fee)



    Create table MyTable

    (Ord_NO numeric(10),
    CL_FEE numeric,
    CL_Date Date,
    SeqNum int
    )


    insert into MyTable values (1001,4500,'1/1/2014',45)
    insert into MyTable values (1001,300,'1/14/2014',95)
    insert into MyTable values (1001,0,'2/12/2014',22)

    insert into MyTable values (1002,700,'1/1/2014',145)
    insert into MyTable values (1002,250,'1/14/2014',395)
    insert into MyTable values (1002,100,'2/12/2014',252)

    insert into MyTable values (1003,0,'4/1/2013',65)
    insert into MyTable values (1003,100,'5/14/2014',95)
    insert into MyTable values (1003,20,'6/12/2014',131)

    insert into MyTable values (1004,0,'6/1/2014',15)
    insert into MyTable values (1004,25,'8/14/2014',17)

    insert into MyTable values (1005,500,'10/10/2014',15)

    select * from MyTable order by Ord_NO


    select ord_no, Seqnum, cl_fee , Cl_date
    from MyTable
    order by Ord_NO , CL_Date

    need to calculate a new column called BalFee which is group by ordno sort by ordno, CL_date then
    first column value for cl_fee will be zero
    then second column follow will be first Cl_fee - second Cl_fee
    then third column follow will be second Cl_fee - third Cl_fee so onnn,

    if ord_num has only one row then the balfee will be just 0

    if ord_num has two rows then first column value for clien_fee will be zero
    then second column follow will be first Cl_fee - second Cl_fee


    expected outcome will be

    Create table MyTableOutCome

    (Ord_NO numeric(10),
    CL_FEE numeric,
    CL_Date Date,
    SeqNum int,
    BalFee int
    )


    insert into MyTableOutCome values (1001,4500,'1/1/2014',45,0) -- First value so default to 0
    insert into MyTableOutCome values (1001,300,'1/14/2014',95,4200) -- Second value first - second 4500 - 300
    insert into MyTableOutCome values (1001,0,'2/12/2014',22,300) -- Third Value second - third so 300 - 0

    insert into MyTableOutCome values (1002,700,'1/1/2014',145,0)
    insert into MyTableOutCome values (1002,250,'1/14/2014',395,450)
    insert into MyTableOutCome values (1002,100,'2/12/2014',252,150)

    insert into MyTableOutCome values (1003,0,'4/1/2013',65,0)
    insert into MyTableOutCome values (1003,100,'5/14/2014',95,-100)
    insert into MyTableOutCome values (1003,20,'6/12/2014',131,80)

    insert into MyTableOutCome values (1004,0,'6/1/2014',15,0)
    insert into MyTableOutCome values (1004,25,'8/14/2014',17,-25)

    insert into MyTableOutCome values (1005,500,'10/10/2014',15,0)

    select * from MyTableOutCome order by Ord_NO , CL_Date



    also one more question how to do it in a proc by using "where Current of Cursor_Name" please



    Thank you very much in Advance

    Asita
     
  2. asita

    asita Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hello Could somebody please help me with query please.,

    Thanks a ton in advance
     
  3. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Asita ,

    Try with the below query.

    Code (SQL):
      SELECT  ord_no
               , Seqnum
               , cl_fee
               , cl_date
               , NVL(LAG(cl_fee,1) OVER( PARTITION BY ord_no ORDER BY Ord_NO , CL_Date ) - cl_fee,0) BalFee
    FROM    MyTable
    ORDER BY Ord_NO , CL_Date
     

    Regards
    Sambasiva Reddy.K
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Asita,

    Try this SQL:

    Code (SQL):
    SELECT ord_no,
      cl_fee,
      cl_date ,
      seqnum,
      CASE
        WHEN ord_no != prev_ord_no THEN 0
        ELSE prev_cl_fee - cl_fee  
      END bal_fee  
    FROM
      (SELECT ord_no,
        cl_fee,
        cl_date ,
        seqnum,
        LAG(cl_fee, 1, 0) OVER (ORDER BY ord_no, cl_date) AS prev_cl_fee,
        LAG(ord_no, 1, 0) OVER (ORDER BY ord_no, cl_date) AS prev_ord_no
      FROM mytable
      );
     
  5. asita

    asita Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hello Sambasiva and rajan,

    Thank you very much my lords., it is working like a charm, but can you please let me know how can I update this new bal value to the table column new_bam using cursor(with proc)

    please please see below.,



    CREATE OR REPLACE PROCEDURE Proc_Fees_Diff
    as
    WS_Order_num number (10) :=0 ;
    WS_Seq_num NUMBER(10) :=0;
    WS_FEE NUMBER(14,4) :=0;
    WS_LAST_UPD_DT date;
    WS_DIFF NUMBER(14,4) :=0;
    CURSOR CLIENTFEE IS
    select Order_num, Seq_num, CLIENT_FEE , CL_LAST_UPD_DT
    from feetable
    order by Order_num, CL_LAST_UPD_DT ;

    rd1 CLIENTFEE%ROWTYPE;
    F_Order_num number (10) ;
    F_Seq_num NUMBER(10);
    F_FEE NUMBER(14,4);
    F_LAST_UPD_DT date;

    BEGIN
    OPEN CLIENTFEE;
    <<calc_perf_loop>>
    LOOP
    FETCH CLIENTFEE INTO rd1;
    EXIT WHEN CLIENTFEE%NOTFOUND;
    BEGIN
    If F_Order_num = WS_Order_num
    then WS_DIFF= WS_FEE -F_FEE;
    UPDATE FEE_QUOTE_TABLE
    SET set_clause
    WHERE CURRENT OF cursor_name;
    ......


    or please let me know with any other ways.,


    Thank you in advance
    asita
     
  6. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi ,

    In the Cursor you are fetching the data from ' feetable' table. But in the loop you are updating different table ' FEE_QUOTE_TABLE'. So here you can't use 'WHERE CURRENT'. If you are updating the same table i.e 'feetable' that time only you can use 'WHERE CURRENT' .


    Regards
    Sambasiva Reddy.K
     
  7. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    There are several issues or questions linked to your procedure:

    1) Your source table is "feetable" and destination table is "fee_quote_table" ?
    2) You are doing "If F_Order_num = WS_Order_num": how are theres variables populated ?
    3) As Sambasiva raised, you can't update using "WHERE CURRENT OF cursor_name;" if your cursor is not based on the table being updated.

    IMHO, the procedure can simply be written without any cursor, i.e., (if I take the table names provided in your 1st post):

    Code (SQL):
    CREATE OR REPLACE PROCEDURE Proc_Fees_Diff
    AS
    BEGIN
    INSERT INTO MyTableOutCome (
    Ord_NO ,
    CL_FEE ,
    CL_Date ,
    SeqNum ,
    BalFee
    )
    SELECT .......
    FROM MyTable;

    EXCEPTION
    -- Exception handling part
    WHEN ....
    END;
    /

    Where the SELECT statement is the one we posted above.
     
  8. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi asita,

    If you still want the cursor version (without LAG), then you can try:

    Code (SQL):
    CREATE OR REPLACE PROCEDURE Proc_Fees_Diff
    AS
      l_bal_fee NUMBER := 0;
      l_ord_no  NUMBER := -1;
    BEGIN
      FOR C IN
      (SELECT ord_no, cl_fee, cl_date, SeqNum
        FROM MyTable ORDER BY ord_no, cl_date
      )
      LOOP
        IF C.ord_no != l_ord_no THEN
          l_bal_fee := 0;
        ELSE
          l_bal_fee := l_bal_fee - C.cl_fee;
        END IF;
        INSERT INTO MyTableOutCome
          (Ord_NO, CL_FEE, CL_Date, SeqNum, BalFee)
        VALUES
          (C.ord_no, C.cl_fee, C.cl_date, C.SeqNum, l_bal_fee);
        l_bal_fee := C.cl_fee;
        l_ord_no  := C.ord_no;
      END LOOP;
    EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20010, 'Error in Proc_Fees_Diff');
    END;
    /
    However, the previous version I posted remains the simplest (and fastest) one !
     
  9. asita

    asita Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hello Rajan Greatful to you,


    Thank you very much

    I will use the LAG one

    Thank you very much