Re: SQR not adding correctly - round function is used

[Peter Alan Burton <peterb@SQRIBE.COM>]

Bruce,

If you want EXACT (vs APPROXIMATE) mathematical computations then I would
SQR DECIMAL variables.

In the BEGIN-SETUP section (or within a procedure that has arguments or
defined as LOCAL):

 Declare-Variable
  Decimal #Variable[(precision)]   (precision = 1 .. 38; default = 16)
 End-Declare

And then get rid of the ROUND function.

Peter


On 6 Jun 99, at 21:47, Bruce Pecci wrote:

Date sent:              Sun, 6 Jun 1999 21:47:17 -0400
Send reply to:          SQR-USERS@list.iex.net
From:                   Bruce Pecci <bdpecci@AEP.COM>
Subject:                SQR not adding correctly - round function is used
To:                     Multiple recipients of list SQR-USERS 
<SQR-USERS@list.iex.net>

> 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.
>