-
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.
-
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
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_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
Last edited by Cookies; 07-29-2004 at 04:34 PM.
- Cookies
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|