Update Question

[daphnny]

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.

[Cookies]

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]

edited to correct 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_ref, trx_number, line_number
        from inv_inter1
        order by inv_date, client_ref
        for update;

BEGIN

   open c1;
     fetch c1 into v_inv_date, v_client_ref, v_trx_number, n_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_date, v_client_ref, v_trx_number, n_line_number;
       end loop;
    close c1;
--   commit;
end inv_test_all;
/

Procedure created.

Elapsed: 00:00:00.00
topicadm@Topic_Dev> 
topicadm@Topic_Dev> exec inv_test_all;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
topicadm@Topic_Dev> select * 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 

[daphnny]

Thank you, Cookies, surely that solved my problem. And your code is better than mine.