Hi
I am converting an inherited system from an Access database to FB and am
faced with this problem, because Access allows "incorrect" use of SQL and
allows for 2 tables in the update statement. I would be grateful for any
help because the principles in this apply to a number of other projects
(and future ones I guess)

System is W2K, FB1, Delphi6 - no problem with system.
For simplicity these are the tables and fields:-
LEDGER has 2 fields -
ACNO CHAR(5)
BALANCE NUMERIC(9, 2)
POSTINGS has 3 fields
ACNO CHAR(5)
TOTAL NUMERIC(9, 2)
DR_CR CHAR(1) 'D' or 'C'

What I want is the following:-
This is an "english version" - Update the Postings Table - Set all
Postings.Total to zero where the Ledger table balance is zero, the
Postings. Acno is the same as the Ledger.Acno, and the net of Debit and
credit postings is nil. Then move on to the next set of records.

I know this will not work but am rephrasing the above sentence.
UPDATE POSTINGS
SET Total=0
WHERE LEDGER.Balance=0
AND POSTINGS.Acno=LEDGER.Acno
AND (Sum(Postings.Total) Where Postings.DR_CR='D') MINUS
(Sum(Postings.Total) Where Postings.DR_CR='C')=0

In Delphi this works ok - but it uses Tables not Sql and negates the
benefits of FB and stored procedures
while not LEDGER .eof do begin
DebitTotal:=0;
CreditTotal:=0;
if LEDGER.Balance=0 then
begin
while not POSTINGS .eof do begin
if POSTINGS .DR_CR='D' then
DebitTotal:=DebitTotal+POSTINGS ['Total']
else if POSTINGS .DR_CR='C'
CreditTotal:=CreditTotal+POSTINGS ['Total'];
if DebitTotal-CreditTotal=0 then
begin
POSTINGS .Edit;
POSTINGS . Total:=0;
POSTINGS .Post;
end;
POSTINGS .Next;
end;
LEDGER .Next;
end;

This is the latest version of my SP - which is when I decided to hit the
bottle - hard!! :-{
SET TERM ^ ;

/* Stored procedures */

CREATE PROCEDURE Ajd_AUTOALLOCATE
(
LEDGERTYPE CHAR(10)
)
AS
Declare Variable LedgerAcno CHAR(5);
Declare Variable DEBITTOTAL NUMERIC(9, 2);
Declare Variable CREDITTOTAL NUMERIC(9, 2);
begin
If (Ledgertype='Sales ') then
begin
For Select Acno
From Sledger
Where Balance=0
into :LedgerAcno
do
begin
For Select Sum(Total)
From Sposting
Where Acno=:LedgerAcno
And Dr_Cr='D'
into :DEBITTOTAL
do
suspend;
For Select Sum(Total)
From Sposting
Where Acno=:LedgerAcno
And Dr_Cr='C'
into :CREDITTOTAL
do
suspend;
end
if (:DEBITTOTAL-:CREDITTOTAL=0) then
begin
Update Sposting
Set Paid='Y',Total=0
Where Acno=:LedgerAcno;
end
end
end
^

SET TERM ; ^

Alan J Davies
email: alan@...