FB3 Update

[Henry Olive 5]

Good Day,

 

I want to Update  a target table according to a Master Detail table records.

I wrote 2 Stored Procs. 1 for Update a Single record in target , the other for Update All records in target

Here below my 2 s.procs,  both works, but i'd like to be sure and need an expert confirmation

Could someone please help.?

 

/* For single 1 record update s.proc */

ALTER PROCEDURE UPDATEACC_SINGLE (
    ACCCODE VARCHAR(20),
    ACTIVEYEAR VARCHAR(4) )
AS
BEGIN
  IF (ACCCODE IS NULL) THEN EXIT;
  
  UPDATE ACCACCOUNT SET 
     DEBIT= (SELECT SUM(AD.DEBIT) FROM ACCRECDETAIL AD 
             JOIN ACCRECEIPT AR ON AD.RNO=AR.RNO
             WHERE AD.ACCCODE=:ACCCODE and EXTRACT(YEAR FROM AR.TDATE)=:ACTIVEYEAR),
             
     CREDIT= (SELECT SUM(AD.CREDIT) FROM ACCRECDETAIL AD 
             JOIN ACCRECEIPT AR ON AD.RNO=AR.RNO
             WHERE AD.ACCCODE=:ACCCODE and EXTRACT(YEAR FROM AR.TDATE)=:ACTIVEYEAR);                
END

 

/* For all records update s.proc */

ALTER PROCEDURE UPDATEACC_ALL (
    ACTIVEYEAR VARCHAR(4) )
AS
begin

 UPDATE ACCACCOUNT AC SET
        AC.DEBIT=(SELECT SUM(AD.DEBIT) FROM ACCRECDETAIL AD
                  JOIN ACCRECEIPT AR ON AR.RNO=AD.RNO
                  WHERE AR.RNO=AD.RNO and AD.ACCCODE=AC.ACCCODE and
                        EXTRACT(YEAR FROM AR.TDATE)=:ACTIVEYEAR), 
                                                                                                                                   
        AC.CREDIT=(SELECT SUM(AD.CREDIT) FROM ACCRECDETAIL AD
                   JOIN ACCRECEIPT AR ON AR.RNO=AD.RNO
                   WHERE AR.RNO=AD.RNO and AD.ACCCODE=AC.ACCCODE and
                         EXTRACT(YEAR FROM AR.TDATE)=:ACTIVEYEAR);                 
end
 

Thank You

[Serge_G 87]

Hi, for me the 2 updates lack of a WHERE clause, but it depend on ACCACCOUNT  table description

 

 

[Henry Olive 5]

Thank you So Much Serge

What you mean by  ACCACCOUNT  table description ?

ACCAccount table is like below

ACCCODE,  DEBIT,  CREDIT

 

[tgbs 14]

When join is only for checking(not selecting column) better to use EXISTS.

Can you sum debit and credit in one select ?

Input parameter ACTIVEYEAR better be integer

[Serge_G 87]

16 hours ago, Henry Olive said:

ACCAccount table is like below

ACCCODE,  DEBIT,  CREDIT

Why do I ask for table description because I was thinking about a ACCOUNT,YEAR key or worst

  Why did you use 2 "embedded" SELECT when only one is needed ?

 

 

So I suggest you to write  and test this  for first one

ALTER PROCEDURE UPDATEACC_SINGLE (
    ACCCODE VARCHAR(20),
    ACTIVEYEAR VARCHAR(4) )
AS
DECLARE VARIABLE ACCREDIT NUMERIC(15,2);
DECLARE VARIABLE ACDEBIT NUMERIC(15,2);

BEGIN
  IF (ACCCODE IS NULL) THEN EXIT;
  SELECT SUM(AD.DEBIT),SUM(AD.CREDIT) FROM ACCRECDETAIL AD 
             JOIN ACCRECEIPT AR ON AD.RNO=AR.RNO
             WHERE AD.ACCCODE=:ACCCODE and EXTRACT(YEAR FROM AR.TDATE)=:ACTIVEYEAR)
  INTO :ACDEBIT,:ACCREDIT;           
  UPDATE ACCACCOUNT SET DEBIT=:ACDEBIT,CREDIT=:ACCREDIT 
         WHERE ACCCODE=:ACCCODE               
END 

And second one

/* For all records update s.proc */
 

ALTER PROCEDURE UPDATEACC_ALL (
    ACTIVEYEAR VARCHAR(4) )
AS
DECLARE VARIABLE ACCOUNT VARCHAR(20);
DECLARE VARIABLE ACCREDIT NUMERIC (15,2);
DECLARE VARIABLE ACDEBIT NUMERIC (15,2);
begin

FOR SELECT ACCCODE, SUM(AD.DEBIT),SUM(AD.CREDIT)  FROM ACCRECDETAIL AD
                  JOIN ACCRECEIPT AR ON AR.RNO=AD.RNO
       WHERE EXTRACT(YEAR FROM AR.TDATE)=:ACTIVEYEAR
       GROUP BY ACCCODE
       INTO :ACCOUNT,:ACDEBIT,:ACCREDIT 

DO  UPDATE ACCACCOUNT  SET DEBIT=:DEBIT, CREDIT=:AMOUNT
      WHERE ACCCODE=:ACCOUNT;             
end

Note : you have to adjust VARIABLE declaration to your need of course

Edited July 26, 2022 by Serge_G

[Henry Olive 5]

Thank you SO SO SO much Serge

Your code is shorter & faster

[Henry Olive 5]

Thank you so much tgbs