Hmm, on looking at this, I see I forgot to finish it off with a WHERE
criterion that would identify the rows(s) to receive the update. As it
stands, all rows in the table would receive the update each time an
existing structure was found. Humph.
At 10:40 AM 05-06-02 -0400, you wrote:

> Why does this need two instances of Mempay2?

It's not two instances, it's two cursors - one to form the "eligibility
set" (which involves Mempay2) and one to perform the positioned
update. The only problem is, there is no positioning criterion...we really
need the equivalent of WHERE CURRENT OF the M3 cursor.

> What does SELECT 1 mean at <--- below?

When doing an EXISTS() test we don't form an output set so we don't care
what we select - it's just dummy output. A non-data dummy output is often
faster.

> > You could try this:
> >
> > UPDATE MEMPAY2 M3
> > SET M3.REMIND_LET = :InvoiceDate
> > WHERE (EXISTS (SELECT 1 FROM MEMPAY2 M2 <---
> > JOIN MEMBERS M ON M2.ID = M.ID
> > AND M2.RENEW_DATE = M.RENEW_DTE
> > WHERE M.ID = M3.ID
> > AND (M.STATUS_MEM = 'ACTIVE' OR M.STATUS_MEM = 'ASSOCIATE')
> > AND M2.DATE_PAID IS NULL AND
> > AND M2.REMIND_LET IS NULL
> > AND M2.RENEW_DATE IS NOT NULL
> > AND (M2.RENEW_DATE >= '05/04/2002' AND M2.RENEW_DATE <= '07/18/2002')))

we need to add a positioning criterion here like
AND M3.ID = {{{SOMETHING}}}

which seems impossible, leading on to...

> > ...otherwise I'd want to go the safe route and do this in a stored
> procedure.

In a SP, you can work a cursor on the joined structure...
...
AS
CREATE VARIABLE MEMID INTEGER;
BEGIN
FOR SELECT M2.ID FROM MEMPAY2 M2
JOIN MEMBERS M ON M2.ID = M.ID
AND M2.RENEW_DATE = M.RENEW_DTE
WHERE M.ID = M3.ID
AND (M.STATUS_MEM = 'ACTIVE' OR M.STATUS_MEM = 'ASSOCIATE')
AND M2.DATE_PAID IS NULL AND
AND M2.REMIND_LET IS NULL
AND M2.RENEW_DATE IS NOT NULL
AND (M2.RENEW_DATE >= '05/04/2002' AND M2.RENEW_DATE <= '07/18/2002')
INTO :MEMID DO
BEGIN
UPDATE MEMPAY M3
SET REMIND_LET = :InvoiceDate
WHERE M3.ID = :MEMID;
END;
END;

>And lastly, I am not using an SP as I use the same query component
>over and
>over for many different things. Why is an SP, "the safe route"?

In the first place, a query component isn't your best choice for DML, since
it's a dataset...but, anyway, what is wrong with
IBReportQuery->SQL->Clear;
IBReportQuery->SQL->Add("EXECUTE PROCEDURE MYPROC(:InvoiceDate)");

???
heLen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________