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: 1281
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 Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts scheduling between OPC and TWS give u... nsbl IBM Tools 4 Sun Mar 12, 2017 5:35 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am


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