Update Question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Update Question

  1. #1
    Join Date
    Dec 2000
    Posts
    37

    Cool Update Question

    I want to update a table with transaction number and line number. For rows with the same invoice_date and client_ref,update them using the same transaction number and line_number increase by 1. If invoice_date and client_ref are not the same, then get a new sequence number as trx number.


    --CREATE TABLE:
    CREATE TABLE INV_INTER
    (
    INV_DATE DATE,
    CLIENT_REF VARCHAR2(15),
    TRX_NUMBER VARCHAR2(15),
    LINE_NUMBER NUMBER
    );

    --INSERT RECORDS:
    INSERT INTO INV_INTER1 VALUES
    ('15-JUL-04','2222',NULL,NULL);
    INSERT INTO INV_INTER VALUES
    ('15-JUL-04',2222,NULL,NULL);
    INSERT INTO INV_INTER1 VALUES
    ('14-JUL-04','1500',NULL,NULL);
    INSERT INTO INV_INTER1 VALUES
    ('01-JUL-04','1000',NULL,NULL);
    INSERT INTO INV_INTER1 VALUES
    ('01-JUL-04','1000',NULL,NULL);

    So Here is what the table looks like before update:
    select inv_date, client_ref,trx_number, line_number from inv_inter;

    INV_DATE CLIENT_REF TRX_NUMBER LINE_NUMBER
    --------- --------------- --------------- -----------
    15-JUL-04 2222
    14-JUL-04 1500
    01-JUL-04 1111
    01-JUL-04 1111

    Here is the expected result after running the procedure:

    INV_DATE CLIENT_REF TRX_NUMBER LINE_NUMBER
    --------- --------------- --------------- -----------
    15-JUL-04 2222 116670 1
    14-JUL-04 1500 116669 1
    01-JUL-04 1111 116668 1
    01-JUL-04 1111 116668 2

    The problem with my procedure is that it does not update the last record sort by inv_date:

    INV_DATE CLIENT_REF TRX_NUMBER LINE_NUMBER
    --------- --------------- --------------- -----------
    15-JUL-04 2222 1
    14-JUL-04 1500 116671 1
    01-JUL-04 1111 116670 1
    01-JUL-04 1111 116670 2


    Here is my code:

    CREATE OR REPLACE PROCEDURE INV_TEST_ALL IS
    V_TRX_NUMBER VARCHAR2(15):=NULL;
    V_INV_DATE DATE:=NULL;
    V_CLIENT_REF VARCHAR2(15):=NULL;
    N_LINE_NUMBER NUMBER:=0;

    BEGIN
    FOR INV_REC IN (SELECT * FROM INV_INTER ORDER BY INV_DATE, CLIENT_REF)
    LOOP

    IF V_INV_DATE<>INV_REC.INV_DATE OR V_CLIENT_REF<>INV_REC.CLIENT_REF
    THEN
    SELECT mysequence.nextval INTO V_TRX_NUMBER FROM DUAL;
    ELSE
    V_TRX_NUMBER:=INV_REC.TRX_NUMBER;
    END IF;


    FOR LINE_REC IN (SELECT ROWID FROM INV_INTER WHERE INV_DATE=INV_REC.INV_DATE
    AND CLIENT_REF=INV_REC.CLIENT_REF)
    LOOP
    N_LINE_NUMBER:=N_LINE_NUMBER+1;

    UPDATE INV_INTER SET TRX_NUMBER=V_TRX_NUMBER WHERE INV_DATE=V_INV_DATE AND CLIENT_REF=V_CLIENT_REF;
    V_INV_DATE:=INV_REC.INV_DATE;
    V_CLIENT_REF:=INV_REC.CLIENT_REF;
    UPDATE INV_INTER SET LINE_NUMBER=N_LINE_NUMBER WHERE ROWID=LINE_REC.ROWID;


    END LOOP;

    N_LINE_NUMBER:=0;
    END LOOP;
    COMMIT;
    END;
    /

    I appreciate your help.

  2. #2
    Join Date
    Oct 2002
    Posts
    182
    you cannot compare NULL values.
    this is where you are going wrong.

    at best you need a null check
    (ie: IF INV_REC.INV_DATE is NULL
    OR INV_REC.CLIENT_REF is NULL
    THEN )
    - Cookies

  3. #3
    Join Date
    Oct 2002
    Posts
    182
    edited to correct code
    PHP Code:
    CREATE OR REPLACE PROCEDURE inv_test_all 
    IS
       v_inv_date      DATE          
    := NULL;
       
    v_client_ref    VARCHAR2 (15) := NULL;
       
    v_inv_date2     DATE          := '01-JAN-10';
       
    v_client_ref2   VARCHAR2 (15) := '0';
       
    v_trx_number    VARCHAR2 (15) := NULL;
       
    v_trx_number2   VARCHAR2 (15) := NULL;
       
    n_line_number   NUMBER        := 1;
       
    n_line_number2   NUMBER        := 1;
       
       
    cursor c1 is
           select 
               inv_date
    client_reftrx_numberline_number
            from inv_inter1
            order by inv_date
    client_ref
            
    for update;

    BEGIN

       open c1
    ;
         
    fetch c1 into v_inv_datev_client_refv_trx_numbern_line_number;
                       
         while 
    c1%found 
          loop
          
              
    if (v_inv_date v_inv_date2) and (v_client_ref v_client_ref2)
              
    then
                    v_trx_number 
    := v_trx_number2;
                    
    n_line_number := n_line_number2 1;
              else

                    
    select mysequence.nextval
                      into v_trx_number
                        from dual
    ;

                    
    n_line_number := 1;

              
    end if;
                

                
    update inv_inter1
                    set trx_number 
    v_trx_number
                         
    line_number n_line_number
                where current of c1
    ;

                
    v_inv_date2   := v_inv_date;
                
    v_client_ref2 := v_client_ref;
                
    v_trx_number2 := v_trx_number;
                
    n_line_number2:= n_line_number;
                
           
    fetch c1 into v_inv_datev_client_refv_trx_numbern_line_number;
           
    end loop;
        
    close c1;
    --   
    commit;
    end inv_test_all;
    /

    Procedure created.

    Elapsed00:00:00.00
    topicadm
    @Topic_Dev
    topicadm@Topic_Devexec inv_test_all;

    PL/SQL procedure successfully completed.

    Elapsed00:00:00.00
    topicadm
    @Topic_Devselect from inv_inter1;

    INV_DATE  CLIENT_REF      TRX_NUMBER      LINE_NUMBER
    --------- --------------- --------------- -----------
    15-JUL-04 2222            29                        1
    15
    -JUL-04 2222            29                        2
    14
    -JUL-04 1500            28                        1
    01
    -JUL-04 1000            27                        1
    01
    -JUL-04 1000            27                        2
    01
    -JUL-04 1000            27                        3 
    Last edited by Cookies; 07-29-2004 at 04:34 PM.
    - Cookies

  4. #4
    Join Date
    Dec 2000
    Posts
    37
    Thank you, Cookies, surely that solved my problem. And your code is better than mine.

Posting Permissions

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



Click Here to Expand Forum to Full Width