Hi:

Update query below was run on 6/3/2002 and inserted invoice date
for a member whose status was cancelled but whose record had a
valid renew_date entry.

It did not seem to exclude the M2 record even though the M record
had neither ACTIVE or ASSOCIATE as the status.

What is really strange though (and here is the rub) it nulled
the ID field in M2 where M Status was CANCELLED.

Now this has not posed a problem since the lack of an ID prevents
the invoice from being printed but leaves an orphaned record
in the database (several). Unfortunately this record contains other
information which is not necessarily to be discarded since a cancelled
member can be reinstated.

Right now I am going back and manually putting the
correct ID in the orphaned records. Not a good op
for my "normal" users.

The screw up is obviously at the <----- below where it
sets the ID in M2 = null since it can't find an M.ID in a record
meeting ACTIVE or ASSOCIATE.

I am trying to remember how I got to the SQL the way it is at
<----.

I will crunch on but any great insight appreciated.

Thanks.

Best regards

UPDATE MEMPAY2 M2
SET M2.REMIND_LET = :InvoiceDate, M2.ID = <-------------
(SELECT M.ID FROM MEMBERS M
WHERE (M.ID = M2.ID AND M.RENEW_DTE = M2.RENEW_DATE AND
(M.STATUS_MEM = 'ACTIVE' OR M.STATUS_MEM = 'ASSOCIATE')))
WHERE (M2.RENEW_DATE IS NOT NULL AND M2.DATE_PAID IS NULL AND
M2.REMIND_LET IS NULL AND
M2.RENEW_DATE >= '05/04/2002' AND M2.RENEW_DATE <= '07/18/2002')