Re: SQR not adding correctly - round function is used

[Jeff Hill <jhill@AGCONSULT.COM>]

Bruce -

I would change your logic to be as follows:

     let  #Work_Amt = round(&rescrce_amt,2)
     let  #Tot_Resrce_Amt = #Tot_Resrce_Amt + #Work_Amt

When you round working totals such as you are doing, in my experience, you
could have mixed results.  This way works a little better and is a little
cleaner.

Good Luck.

Jeff Hill
AG Consulting, an ADP Company
jhill@agconsult.com





Bruce Pecci <bdpecci@AEP.COM> on 06/06/99 06:47:17 PM

Please respond to SQR-USERS@list.iex.net

To:   Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
cc:    (bcc: Jeffrey Hill/Application Group)
Subject:  SQR not adding correctly - round function is used




Platform
OS:     HP-UX  11.00   E 9000/800
DB:     Oracle 8.0.4.2.1
SQR:  4.3.2

We are using SQR to extract data from a transaction table and write it to a
file.  Each file has a header record, detailed transaction records, and a
trailer record that includes the line count and total dollar amounts.

When we began running the SQR programs in production with large volumes, we
encountered a problem in which the total dollar amounts that were
accumulated and written in the trailer record were occasionally not equal
to the sum of the amounts on the detailed transactions.  The error was
discovered by the receiving system as they processed the extract file and
was verified using the total amount aggregated using SQL against the Oracle
table.  The differences in actual to output totals have ranged from a few
cents to over a thousand dollars.  Re-running the extract usually results
in correct trailer record totals.  The relevant code follows:


begin-report
.
.
let $format14    = '000000000000v99'
.
.!select rows from database meeting extract criteria
.
let #tot_resrce_amt =  round(#tot_resrce_amt + &resrce_amt,2)
.
.
do Decimal-Format-Sign-Sep(#tot_resrce_amt,$format14,$tot_resrce_amt)
.
.
write {FILE_NUMBER} from $trl_type:2
                            $tot_count:8
                            $fill244:244
                            $tot_foreign_amt:16
                            $tot_resrce_amt:15
                            $tot_resrce_qty:16
.
.
end-report


The code for the Decimal-Format-Sign-Sep function is as follows:

begin-procedure Decimal-Format-Sign-Sep(#amt,$format,:$num)
if #amt < 0
     let $sign = '-'
else
     let $sign = '+'
end-if
let $num = $sign||edit(abs(#amt),$format)
end-procedure Decimal-Format-Sign-Sep


To isolate the problem, we added display statements.  When the error
occurs, both the #tot_resrce_amt and formatted result contain the same
incorrect amount.  Therefore we believe the problem to be in the
accumulation statement itself.  I say "believe" because the displays of
both values were initially coded after the call to the formatting function.
It's my understanding that SQR shouldn't alter the value of #tot_resrce_amt
in the local function, but we're moving the display of the numeric quantity
before the call to the function just to be sure.

This error was occurring very infrequently (1/50 runs) until we upgraded
from SQR version 3 to 4.3.2.  Since the upgrade, the problem seems to be
occurring more frequently.  Is anyone else using SQR to accumulate amounts
in a similar manner and had similar problems?  We're very concerned about
the problem because if it with the accumulation step, errors could be
occurring throughout the system without anyone knowing.  The process of
extracting and passing data to another system is the only one in which one
program verifies the totals accumulated by another program.

Any thoughts would be appreciated.