[sqr-users] RE: Illogical if statement

["White . Denise" <DWhite@drc.com>]

I guess everyone has their own solution to this pr... um, feature.  I
would use a round() function, myself, in this case to six digits.

Denise M. White
EBS Programmer/Analyst III
Dynamics Research Corporation
------------------------------

Message: 2
Date: Sat, 18 Nov 2006 23:02:27 +1100
From: "Steve Cavill" <steve.cavill@infoclarity.com.au>
Subject: RE: [sqr-users] Illogical if statement
To: "'This list is for discussion about the SQR database
        reportinglanguage       from Hyperion Solutions.'"
<sqr-users@sqrug.org>
Message-ID: <20061118120433.BKLF19269.omta05ps.mx.bigpond.com@lily>
Content-Type: text/plain;       charset="us-ascii"

The problem you are facing is that computers use binary but the numbers
you are comparing are decimal. Most decimal numbers cannot be
represented exactly in binary The only solution to rounding problems is
to use integers, as binary computers can't store decimal fractions
exactly.
E.g. 0.5 decimal = 0.1 binary, which is exact.
But 0.1 decimal= 0.001100110011.... Recurring (or something like that -
my binary arithmetic isn't brilliant!) So 0.1 + 0.1 + 0.1 -
0.3 will not equal zero exactly.

Binary arithmetic is hard to get your head around.
But take this simple decimal example
What is 1/3 + 1/3 + 1/3?
1 exactly right?
But not if you use decimal arithmetic
1/3 + 1/3 + 1/3
=.3333333333... +.333333333... + .333333333...
=.9999999999...
Which is not equal to one.

In the same way that 1/3 cannot be represented in decimal exactly 0.1
cannot be represented in binary exactly so you will always get rounding
errors.
The only solution to rounding problems is to use integers Steve 

-----Original Message-----
From: sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org
[mailto:sqr-users-bounces+steve.cavill=infoclarity.com.au@sqrug.org] On
Behalf Of Thompson, Betty P
Sent: Saturday, 18 November 2006 6:03 AM
To: This list is for discussion about the SQR database reportinglanguage
fromHyperion Solutions.
Subject: RE: [sqr-users] Illogical if statement


Hi, Cathy,

I was pulling my hair out years ago over this same problem.  I no longer
recall the full explanation of what was happening, but it had to do with
the precision not being consistent (not even from one run to the next, I
think).

Here's what I learned to add to the setup section which eliminated the
problem, and it's now my standard to use it in every SQR I
write:


  DECLARE-VARIABLE
     DEFAULT-NUMERIC=DECIMAL(18)
  END-DECLARE

End-SETUP
 
I'd like to know if you try this and still have the problem.

--Betty 

++++++++++++++++++++++++++++++++++++++++++
Betty Thompson
Applications & Systems Development (AIS) - Specialist Instructional,
Communication & Information Technology (iCIT) University of Wisconsin -
Whitewater 800 W. Main Street -- Room L-1015 Whitewater, WI  53190-1790
 
262-472-5532
thompsob@uww.edu
++++++++++++++++++++++++++++++++++++++++++



-----Original Message-----
From: sqr-users-bounces+thompsob=uww.edu@sqrug.org
[mailto:sqr-users-bounces+thompsob=uww.edu@sqrug.org] On Behalf Of
Stratman, Cathy
Sent: Friday, November 17, 2006 12:49 PM
To: 'the dragon'
Cc: sqr-users@sqrug.org
Subject: RE: [sqr-users] Illogical if statement

Clark - Well it works but how dumb is that code?! I thought programming
was supposed to use a little logic!

Thanks,
Cathy 


-----Original Message-----
From: the dragon [mailto:ceprn@hotmail.com]
Sent: Friday, November 17, 2006 12:35 PM
To: sqr-users@sqrug.org
Cc: CSTRATMAN@crown-chicago.com
Subject: RE: [sqr-users] Illogical if statement

Cathy - I know SQR *used to* have this problem where the numbers didn't
match up, and it was somhow related to a number like about
32 decimal places

out.  Given this *shouldn't* be happening, but it was.  If you want to
make certain the logic works if the numbers are really the same, convert
them into characters with the same edit mask in the compare.  This will
add unnecessary overhead, but it your case I don't think you'll be able
to get around it.

let us know if this helps any.

peace,
clark 'the dragon' willis



PSA: Salary <> Slavery. If you earn a salary, your employer is renting
your services for 40 hours a week, not purchasing your soul.
Your time is the only real finite asset that you have, and once used it
can never be recovered, so don't waste it by giving it away.

I work to live; I don't live to work.

"Time is the coin of your life. It is the only coin you have, and only
you can determine how it will be spent. Be careful lest you let other
people spend it for you."

Carl Sandburg
(1878 - 1967)

It is impossible to defeat an ignorant man in argument.

William G. McAdoo


----Original Message Follows----

Oh great guru's of sqr, I'm tired of beating my head against a wall and
invite you to please help.  I have a very simple, very dumb little
program that reads a csv file, does some validation, inserts valid data
into PS_FLAT_RATE_TBL and reports on invalid data.

The first thing that I check is to see if the sum of the employer rate
and the employee rate actually equal the total rate.  The if statement I
am using has a mind of it's own and I can't seem to figure out what I
may be doing wrong.

We are on PT 8.22.10, HRMS 8.3sp1, SQL server 2000.

The following code is the reading the input file and validating the
coverage rate.  I have included several display statements and what is
being displayed in the log.  The numbers look like they are equal to me
but the program doesn't seem to think so.  If I insert these three rows
via the panels, everything works fine.  Using the program, only the
first record is inserted properly, the other two error out.
Suggestions????

Begin-Procedure Read-Input-File

   let #Inserts = 0

   let #Tot-Recs = 0

   let #row = 5

   let #err_row = 50



   While 1 = 1

     Read 1 Into $Input:200



     Unstring $Input By ',' Into

       $Flat_rate_id

       $Effdt

       $Emplr_covrg_rate

       $Empl_covrg_rate

       $Total_covrg



     let $Prov_covrg_rate = $Total_covrg



     If #end-file = 1

        Break

     End-If



     let $ValidFlag1 = 'Y'

     let $ValidFlag = 'Y'



     Do Validate-Covrg-Rate



     If $ValidFlag1 = 'N'

       let $Error_msg = 'Sum of Employer Coverage Rate and Employee
Coverage Rate is not equal to Total Coverage Rate'

       Do Report-Error

     Else

       Do Validate-Date

       If $ValidFlag = 'N'

         let $Error_msg = 'Date is not in the Right Format'

         Do Report-Error

       End-If



       If $Validflag = 'Y'

         let  $flag = 'Y'

         let #row_found = 0

         Do get-fields

         if #row_found != 1

           let $Error_msg = 'There is no prior data for this
FLAT_RATE_ID'

           let $flag = 'N'

           Do Report-Error

         end-if

         If $flag = 'Y'

           Do Insert-Flat-Rate-Table

         End-if

       End-If

     End-If



   End-While

             .

             .

             .



end-procedure



Begin-Procedure Validate-Covrg-Rate

   let #num_Emplr_covrg_rate = 0

   let #num_Empl_covrg_rate = 0

   let #num_Total_covrg = 0

   let #total = 0



   let #num_Emplr_covrg_rate = to_number($Emplr_covrg_rate)

   let #num_Empl_covrg_rate = to_number($Empl_covrg_rate)

   let #num_Total_covrg = to_number($Total_covrg)

   let #total = #num_Emplr_covrg_rate + #num_Empl_covrg_rate



   DISPLAY '$ValidFlag1 COMING IN TO PROCEDURE:' NOLINE

   DISPLAY $ValidFlag1

   display '#total:' noline

   display #total noline

   display 'X'

   display '#num_Total_covrg:' noline

   display #num_Total_covrg noline

   display 'X'



   if #total = #num_Total_covrg

     !do nothing

   else

     let $ValidFlag1 = 'N'

   End-If





   DISPLAY '$ValidFlag1 COMING OUT OF PROCEDURE: ' NOLINE

   DISPLAY $ValidFlag1

End-Procedure



Log file:



$ValidFlag1 COMING IN TO PROCEDURE:Y

#total:1574.020000X

#num_Total_covrg:1574.020000X

$ValidFlag1 COMING OUT OF PROCEDURE: Y

$ValidFlag1 COMING IN TO PROCEDURE:Y

#total:2422.570000X

#num_Total_covrg:2422.570000X

$ValidFlag1 COMING OUT OF PROCEDURE: N

$ValidFlag1 COMING IN TO PROCEDURE:Y

#total:304.160000X

#num_Total_covrg:304.160000X

$ValidFlag1 COMING OUT OF PROCEDURE: N

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users