Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Fractions defference in DB2 query result.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
chandraBE

New User


Joined: 02 Feb 2010
Posts: 40
Location: Bangalore

PostPosted: Mon Aug 23, 2010 3:38 pm    Post subject: Fractions defference in DB2 query result.
Reply with quote

I am running a query to get the totals for the values of few columns. But I am getting slight discrepancy in the results in the below queries.

The Query I have used

SELECT COUNT(*),
SUM(FLOAT(AM_PRINCIPLE_CASH))
FROM TRANSACT A, TRAN_REF B
WHERE (A.IN_HIST_PEND = 'H')
AND A.ID_TRAN = B.ID_TRAN
AND B.CD_BUS_SYS = 'ABC'
AND A.DT_ACC_TRAN = '2010-07-22';

The result I have got is
59348 +0.9419550453190383E+09

where AM_PRINCIPLE_CASH defined as DECIMAL(18, 6).

Then I have tried to optimize the query to by negating the index.

SELECT COUNT(*),
SUM(FLOAT(AM_PRINCIPLE_CASH))
FROM TRANSACT A, TRAN_REF B
WHERE (A.IN_HIST_PEND = 'H' OR 0=1)
AND A.ID_TRAN = B.ID_TRAN
AND B.CD_BUS_SYS = 'ABC'
AND A.DT_ACC_TRAN = '2010-07-22';

Now the result I have got is
59348 +0.9419550453192099E+09

How ever when I removed the FLOAT in the query then I have got the consistent result in the both queries. But value was for the column was 0000941955045.320000. (This should be the correct value).

But I am not able to understand that even though I have not changed anything much in the query its giving me slight difference when I used FLOAT. Can some one help me to understand why the negating the index can cause this problem?
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Aug 23, 2010 5:42 pm    Post subject:
Reply with quote

the access path changes so the order in which all the rows come is different.
With intermediate rounding/accuracy loss the order of terms in a sum is important.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Aug 23, 2010 11:31 pm    Post subject:
Reply with quote

Hello,

When FLOAT is used, accuracy is reduced. . . This works well for a moon-shot, but not so well for business accounting.

Why would there be a reason to use FLOAT when dealing with money? Or units shipped. . . Or any other amount or quantity?

Suggest non-float be used for these. . .

Possibly, there is something i misunderstand. . .
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Mon Aug 23, 2010 11:49 pm    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

When FLOAT is used, accuracy is reduced. . . This works well for a moon-shot, but not so well for business accounting.


And even with moonshots there are inflight course corrections.
Back to top
View user's profile Send private message
chandraBE

New User


Joined: 02 Feb 2010
Posts: 40
Location: Bangalore

PostPosted: Wed Aug 25, 2010 3:00 pm    Post subject:
Reply with quote

Thanks GuyC and Dick Scherrer for your quick replies. That was pretty much useful information.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us