I am using Oracle 9i

I need to extract records from a table where certain criteria is met
and then compare 4 fields from those records to 4 fields in another
table. Where the records don't match I need them extracted into
another table.

For example.

I want to select *

I then want to compare fields test1, test2, test3 and test4 in table
INVOICE with fields test1, test2, test3 and test4 in table PAYMENT to
see if I get a matching record (This tells me if an invoice has been
paid). If these records do not match then I want the record copied
from table INVOICE to table NOTPAID.

I understand I need 2 cursors, one to populate variables based on the
Invoice table and the other based on the Payment table and then do a
comparison on each. Then to Insert the non matching record from
Invoice into Notpaid and keep looping until we reach the end of the
records in the Invoice cursor.

Any guidance and example code of something similar would be greatly
appreciated.

Cheers

Monty

swoop wrote:
You probably don't even need ONE cursor, just a SQL statement something
like:

insert into NOTPAID (col1, col2, ...)
select ... from INVOICE i
where aa = 1, bb = No, cc=2001
and not exists
( select null from PAYMENT p
where p.test1 = i.test1
and p.test2 = i.test2
and p.test3 = i.test3
and p.test4 = i.test4
);

SQL is all about dealing with SETS of data, rather than row-by-row
processing.

On 24 May 2005 05:50:27 -0700, andrewst (AT) onetel (DOT) com wrote:

If you work in 9i or 10g database meybe will be helpful to look at MERGE
sql statment

I have tried the above with my code but get an error on the last line
telling me t.sysref is an invalid column name.

INSERT INTO utgbtran
VALUES (CMPY, SUPP, ENTRY_DATE, SYSREF);
SELECT CMPY, SUPP, ENTRY_DATE, SYSREF
FROM tgbtran t
WHERE tran_type = 1
AND ordr_rltd_flag = 'Y'
AND yy = 2005;
( select null from TGBPAYM p
where p.cmpy = t.cmpy
and p.supp = t.supp
and p.trans_entry_date = t.entry_date
and p.trans_sysref = t.sysref);

Here is my attempt with 2 cursors but I can't get it to work. I know
you suggested I didn't need to use cursors but for my own benefit I
would like to get it to work with cursors and your method so I can see
it work from different angles as I am new to all this.

Any help will be appreciated.

Cheers.

DECLARE

CURSOR tgb_cur IS

SELECT cmpy, supp, entry_date, sysref
FROM tgbtran
WHERE tran_type = 1
AND ordr_rltd_flag = 'Y'
AND yy = 2005;

v_cmpy tgbtran.cmpy%type;
v_supp tgbtran.supp%type;
v_entry_date tgbtran.entry_date%type;
v_sysref tgbtran.sysref%type;




CURSOR tgbp_cur IS

SELECT cmpy, supp, trans_entry_date, trans_sysref
FROM tgbpaym;

v_py_cmpy tgbpaym.cmpy%type;
v_py_supp tgbpaym.supp%type;
v_py_trans_entry_date tgbpaym.trans_entry_date%type;
v_py_trans_sysref tgbpaym.trans_sysref%type;

BEGIN

OPEN tgb_cur;
LOOP
FETCH tgb_cur INTO v_cmpy,
v_supp,
v_entry_date,
v_sysref;
EXIT WHEN tgb_cur%notfound;


OPEN tgbp_cur;
FETCH tgbp_cur INTO v_py_cmpy,
v_py_supp,
v_py_trans_entry_date,
v_py_trans_sysref;

INSERT INTO utgbtran
VALUES (v_cmpy, v_supp, v_entry_date, v_sysref);
WHERE v_cmpy <> v_py_cmpy
AND v_supp <> v_py_supp
AND v_entry_date <> v_py_trans_entry_date
AND v_sysref <> v_py_trans_sysref;

CLOSE tgbp_cur;
END LOOP
CLOSE tgb_cur;
END;
/

swoop wrote:
Swoop, you are so full of good intentions as your code of mistakes.

Would it not be better if learned a bit about Oracle, SQL and PL/SQL before
trying something like this?

These are two statements, the second one does not have a from table with the t
alias.

In the PL/SQL, the second cursor does not have a where clause, the fetch into
will raise too_many_rows exception.

The insert statement does have a where clause, which is new to me (and perhaps
to Oracle).

Those are the points from the belgian jury.

swoop wrote:
Why would you use a cursor loop to do this?

A simple SQL statement would suffice and even if you did require
a processing loop a cursor would be the worst possible solution:
Look up bulk collection and FORALL.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

swoop wrote:
Well almost...

INSERT INTO utgbtran
VALUES (CMPY, SUPP, ENTRY_DATE, SYSREF)
SELECT CMPY, SUPP, ENTRY_DATE, SYSREF
FROM tgbtran t
WHERE tran_type = 1
AND ordr_rltd_flag = 'Y'
AND yy = 2005
AND NOT EXISTS
( select null from TGBPAYM p
where p.cmpy = t.cmpy
and p.supp = t.supp
and p.trans_entry_date = t.entry_date
and p.trans_sysref = t.sysref);

It is all one SQL statement - only one semi-colon, right at the end.

Hi,

I have managed to correct my error on the SQL statement to get it
working, so thanks very much for your help. It has updated my TGBPAYM
table wit the required records.

However, I have been asked to do it using cursors as I will need to add
more to it later as this is just the start. If someone could
point/guide me as to where I have gone wrong with it and would really
appreciate it.

I have made some changes to it but not sure if it's the right
direction.

Cheers.

DECLARE

CURSOR tgb_cur IS

SELECT cmpy, supp, entry_date, sysref
FROM tgbtran
WHERE tran_type = 1
AND ordr_rltd_flag = 'Y'
AND yy = 2005;

v_cmpy tgbtran.cmpy%type;
v_supp tgbtran.supp%type;
v_entry_date tgbtran.entry_date%type;
v_sysref tgbtran.sysref%type;

v_py_cmpy tgbpaym.cmpy%type;
v_py_supp tgbpaym.supp%type;
v_py_tran_entry_date tgbpaym.tran_entry_date%type;
v_py_tran_sysref tgbpaym.tran_sysref%type;

CURSOR tgbp_cur IS

SELECT cmpy, supp, tran_entry_date, tran_sysref
FROM tgbpaym
WHERE v_cmpy <> v_py_cmpy
AND v_supp <> v_py_supp
AND v_entry_date <> v_py_tran_entry_date
AND v_sysref <> v_py_tran_sysref;

BEGIN

OPEN tgb_cur;
LOOP
FETCH tgb_cur INTO v_cmpy,
v_supp,
v_entry_date,
v_sysref;
EXIT WHEN tgb_cur%notfound;


OPEN tgbp_cur;
FETCH tgbp_cur INTO v_py_cmpy,
v_py_supp,
v_py_tran_entry_date,
v_py_tran_sysref;

INSERT INTO utgbtran
VALUES (v_cmpy, v_supp, v_entry_date, v_sysref);

CLOSE tgbp_cur;
END LOOP;
CLOSE tgb_cur;
END;
/

swoop wrote:
You would still only need ONE cursor, which would look something like
this:

DECLARE
CURSOR c IS
SELECT CMPY, SUPP, ENTRY_DATE, SYSREF
FROM tgbtran t
WHERE tran_type = 1
AND ordr_rltd_flag = 'Y'
AND yy = 2005
AND NOT EXISTS
( select null from TGBPAYM p
where p.cmpy = t.cmpy
and p.supp = t.supp
and p.trans_entry_date = t.entry_date
and p.trans_sysref = t.sysref);
BEGIN
FOR r IN c LOOP
INSERT INTO utgbtran (...)
VALUES (r.CMPY, r.SUPP, r.ENTRY_DATE, r.SYSREF);
END LOOP;
END;

swoop wrote:
This 2nd cursor of yours doesn't check that there is NO match in
tgbpaym for those 4 values, it fins ALL the rows in tgbpaym that DO NOT
match. So if tgbpaym has 10,000 rows and one of those rows matches,
then the cursor will return the other 9,999 rows!