Jump to content
Henry Olive

FB3 Update

Recommended Posts

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

Share this post


Link to post

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

 

 

Share this post


Link to post

Thank you So Much Serge

What you mean by  ACCACCOUNT  table description ?

ACCAccount table is like below

ACCCODE,  DEBIT,  CREDIT

 

Share this post


Link to post

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

Share this post


Link to post
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 by Serge_G

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×