Strange results from DB2 in Quiz

[Pickering, John (NORBORD)]

Byron

I can't comment on the "different layers" theory in your first paragraph.
But if both DecisionStream and Quiz are using the same ODBC driver then how
can they affect DB2 on the remote server? Are they making different sorts of
requests of the ODBC driver?

I would heartily concur with your comments about the inappropriateness of
floating point numbers in typical business calculations. But what Bob is
experiencing (as per his example, not <snipped> below) seems much greater
than what can be explained by simple floating point precision problems --
e.g.
>DecisionStream using the above ODBC driver shows 3.670
>Quiz using the above ODBC driver shows 3.610.
Were it just float problems I would expect 3.70 versus 3.6699999999999999999
or some such value. And this could be cured by judicious rounding when
converting to something sensible like integer datatypes.

Seems like something else is going on here.

Regards,
JWP

> -----Original Message-----
> From:	Byron Welch [SMTP:byron.welch@creatcomp.com]
> Sent:	Thursday, May 03, 2001 3:46 PM
> To:	'Edis, Bob'; 'powerh-l@list.swau.edu'
> Subject:	RE: Strange results from DB2 in Quiz
> 
> Bob,
>   Two comments but unfortunately I don't have a solution:(
> 
>   I think you are getting different results between PowerHouse and
> DecisionStream because they are using different database layers.  My
> understanding is that they will be merged in the next major release but
> for
> now you get slightly different database layers between PowerHouse and the
> newer products.
> 
>   I think you are running into a precession error.  In a couple of cases
> when I was using floating point in the past I came across similar results.
> If I remember correctly the PH manuals used to have a caveat about
> floating
> point numbers.  It was something like for fractions or very large numbers
> a
> loss of precision can occur.  i.e. .2 might actually be stored as .1999.
> This used to prevent me from comparing a Floating point to an integer in a
> select statement.  I usually solved this by multiplying by 100 and then
> dividing by 100 in a define.  
> 
> Byron Welch
> Manager Consulting Services
> Creative Computing, Inc.
> 100 Middle Street
> Lincoln, RI 02865
> (401) 727-0183
> 
> 
> -----Original Message-----
> From: Edis, Bob [mailto:bob.edis@fleetpride.com]
> Sent: Thursday, May 03, 2001 3:01 PM
> To: 'powerh-l@list.swau.edu'
> Subject: Strange results from DB2 in Quiz
> 
> 
> G'day all
> 
> Environment: PH 8.21D4, WinNT 4.0, HitODBC 400, DB/400 
> 
> We are using HitODBC to pull data from a DB2 database on an AS/400 machine
> and are getting erroneous results in PowerHouse Quiz.  E.g.
> 
> Case record:
> 
> DB2 shows the cost amount column as 3.670
> DecisionStream using the above ODBC driver shows 3.670
> Quiz using the above ODBC driver shows 3.610.
> 
> In another case Quiz reports 4.950 whereas DB2 and DS show 5.00.
> 
> The values reported by DB2 and DecisionStream are the correct ones.  The
> column is stored to 3 decimal places (float) in the DB2 table.
> 
> The discrepancy between Quiz and DecisionStream is NOT consistent.  I.e.
> some records in the table match exactly.
> 
> Any ideas why Quiz is reporting incorrect values?  
> 
> Regards,
> 
> Robert Edis
> Manager - Business Intelligence
> FleetPride 
> 520 Lake Cook Rd., Suite 100
> Deerfield IL 60015
> Tel: 847.572.8039
> Fax: 847.444.1096
> www.fleetpride.com
> 
>