Error in procedure which updates master table after deleting records in detail table

Question

In Interbase 2009 db I have master and detail tables(Tmaster, Tdetails).

Tmaster:

master_id(pk)	DocSumma	DocSummaDol

Tdetails:

det_id	master_id(fk)	price1,price2	qnt

After I delete/update records in child table(Tdetails) procedure must update summa in master table(Tmaster).

I have 2 problems:

1. If procedure contains this if-clause:

if (m.DocSumma=0) then begin delete from Tmaster m where m.master_id=:master_id; end

it returns this error:

Column does not belong to referenced table. Dynamic SQL Error. SQL error code = -206. Column unknown.

2. Without if-clause I have the second problem: procedure works very slow. It would be end after 13hrs :)

Sometimes in IBExpert I get this error:

Arithmetic overflow or division by zero has occurred. arithmetic exception, numeric overflow, or string truncation. SQLCODE: -802 GDSCODE: 335544321

what is wrong in my Psql code?

alter procedure sp_recalculate_summa
as
    declare variable master_id integer;
    declare variable det_id integer;
    declare variable sum1 decimal(8,4) ;
    declare variable sum2 decimal(8,4) ;
    begin
        for  select m.master_id
        from Tmaster m
        into :master_id
        do begin
            sum1=0;
            sum2=0;
            for select det_id,
                      sum(d.price1*d.qnt)as summa1,
                      sum(d.price2*d.qnt)as summa2
            from Tdetails d, Tmaster m
            where d.det_id=:master_id
            group by det_id
            into :det_id,:sum1,:sum2
            do
                if (m.DocSumma=0) then begin 
                    delete from Tmaster m where m.master_id=:master_id; 
                end 
                Else begin
                    update Tmaster set DocSumma=:sum1 where master_id=:master_id;
                    update Tmaster set DocSummaDol=:sum2 where master_id=:master_id;
                end
        end
    end

Answer 1

The problem is not with the delete statement, the problem is with if (m.DocSumma=0) then begin. You cannot refer to a table like that inside a PSQL block. You need to explicitly assign that column value to a local variable.

For example, something like:

alter procedure sp_recalculate_summa
as
    declare variable master_id integer;
    declare variable DocSumma decimal(8,4);
    declare variable det_id integer;
    declare variable sum1 decimal(8,4) ;
    declare variable sum2 decimal(8,4) ;
    begin
        for  select m.master_id, m.DocSumma
        from Tmaster m
        into master_id, DocSumma
        do begin
            sum1=0;
            sum2=0;
            for select det_id,
                      sum(d.price1*d.qnt)as summa1,
                      sum(d.price2*d.qnt)as summa2
            from Tdetails d, Tmaster m
            where d.det_id=:master_id
            group by det_id
            into det_id,sum1,sum2
            do
                if (DocSumma=0) then begin 
                -- etc..
                end
        end
    end

As additional remarks:

• I question the correctness of the condition if (m.DocSumma=0) then begin (if (DocSumma=0) then begin in my proposed change), shouldn't this be if (sum1 = 0) then begin? As in, it should use the updated sum, instead of the old sum.

• Also, why are you updating TMASTER twice? It would be more efficient to use a single update:

  update Tmaster set DocSumma=:sum1, DocSummaDol=:sum2 where master_id=:master_id;
  

• Some of these changes might better be done through triggers on TDETAILS and maybe TMASTER, instead of using a delayed explicit recalculation.