Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Rounding Issue (SQL Server vs WebFOCUS)
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Rounding Issue (SQL Server vs WebFOCUS)
 Login/Join
 
Silver Member
posted
I have two tables with the same data one in WebFOCUS and the other in SQL Server. In WebFOCUS the numeric field is defined as D19.2 and in SQl Server it is defined as money(to maintain the precision) and redefined as D19.2 in the master file.

Here's the issue,
As we know even though the field is defined as a D19.2 the values are stored at a higher precision in the database. WebFOCUS is rounding the values differently depending on the where the data is coming from

an example,

Value in SQl Server: 39.425
Value in WebFOCUS Datamart: 39.425

Displayed using a print statement
from SQL Server : 39.43
from WebFOCUS : 39.42

this is an issue only when there is a 5 at the end.

is there any setting I can change to get consistent results?

Thanks in Advance

This message has been edited. Last edited by: <Kathryn Henning>,


Release: Webfocus 7.6.11
OS/Platform: Windows
Output: HTML, EXL2K
 
Posts: 32 | Registered: October 29, 2010Reply With QuoteReport This Post
<Emily Duensing>
posted
Change the field format in the WebFOCUS database to P19.2.
 
Reply With QuoteReport This Post
Virtuoso
posted Hide Post
For MONEY, I don't think that using D (Floating Point) field is recommended. Using a P (Packed) field would be the normal approach for both the SQL data source of MONEY, and the WebFOCUS datamart itself.

That should stop any rounding issues.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Reply With QuoteReport This Post
Silver Member
posted Hide Post
Using the P(Packed) format didn't help either.. WebFOCUS is rounding differently depending on the underlying datasource.. did any one experience similar issue while comparing data from different sources.


Release: Webfocus 7.6.11
OS/Platform: Windows
Output: HTML, EXL2K
 
Posts: 32 | Registered: October 29, 2010Reply With QuoteReport This Post
Master
posted Hide Post
post the MFD's of the columns and the suffix from the MFD's please




Scott

 
Posts: 862 | Registered: May 24, 2004Reply With QuoteReport This Post
Silver Member
posted Hide Post
Here is the column definition in both instances

Case 1:
WebFOCUS Datamart and the SUFFIX is FOC

quote:
FIELDNAME=UNITS, USAGE=D19.2, ACTUAL=D19.2, MISSING=ON, $


Case 2:
SQlServer Table and the SUFFIX is SQLMSS

quote:
FIELDNAME=UNITS, USAGE=D19.2, ACTUAL=D8,
MISSING=ON, $


to give a little background we are trying to move the data to SQL sever and maintin the same results. Which means leave the precision for calculations(use 39.425) but rounded off(Use 39.42) when displaying the output.

Thanks,


Release: Webfocus 7.6.11
OS/Platform: Windows
Output: HTML, EXL2K
 
Posts: 32 | Registered: October 29, 2010Reply With QuoteReport This Post
<Kathryn Henning>
posted
Hi pvparuc,

I see that a case has been opened for this issue. Please keep us updated on the outcome.

Thanks and regards,

Kathryn
 
Reply With QuoteReport This Post
Expert
posted Hide Post
Do you see the difference in your master file descriptions?
one is ACTUAL D19.2 and the other is ACTUAL D8

make them the same and see what happens




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Master
posted Hide Post
I'll go one better, make the ACTUAL=D8. We use Oracle and all of our money fields are USAGE=D10.2 and ACTUAL=D8. We've never had an issue with rounding.

The ACTUAL is the internal format, i.e. double word binary for D8. There is no interal precision for D or F format fields. If I have a field defined as USAGE=D10.2 and ACTUAL=D8, if I compute 1/3, the field will contain .3333... but will display .33. Likewise, 10/3 will contain 3.3333... and display 3.33. D and F fields do no rounding, they truncate results. On the other hand P fields maintain internal precision and round. Try it out.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 934 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Starting with the basics,a WebFOCUS data file, SUFFIX = FOC, does not require an ACTUAL and USAGE. The ACTUAL is determined by the USAGE, or FORMAT. So a D19.2 will always have an ACTUAL of D8.

There is an SQL Server Table with this data:
SQL TABLE          As reported in WebFOCUS
================   =========================
key     money      money - ACTUAL=D8, USAGE=D19.2,
1	39.3333    39.33
2	39.6667    39.67
3	39.4250    39.43
4	39.4550    39.46


With a WebFOCUS data file, containing the exact same data:
WebFOCUS TABLE                 
================                
key     money - FORMAT=D19.4    money - FORMAT=D19.2,
1	39.3333                 39.33
2	39.6667                 39.67
3	39.4250                 39.43
4	39.4550                 39.46

which is exactly as I would expect for standard rounding.

So why is the value seen on this post rounding to .42 instead of .43? If a calculation is done on a D field, the results may not always be exactly as expected. So within the WebFOCUS data file, the field money is multiplied by 9.43968467 and stored. Then divided by 9.43968467 and stored.
WebFOCUS TABLE                  
================                
key     money - FORMAT=D19.4    money - FORMAT=D19.2,
1	39.3333                 39.33
2	39.6667                 39.67
3	39.4250                 39.42
4	39.4550                 39.45

So why suddenly have .4250 & .4550 rounded down. The clue can be found by changing the FORMAT to D19.12
WebFOCUS TABLE                  
================                
key     money - FORMAT=D19.12 
1       39.333299986935 
2       39.666699986581 
3       39.424999986837 
4       39.454999986806 

So the maths has changed the actual held value subtly. The D & F fields will behave in this way, it is floating point arithmetic and more decimal places are actually held than are required by the FORMAT. A D or F format can be changed in the MFD to more decimal places or less without needing to reload the data.

However, if the WebFOCUS table is built with a FORMAT of P19.2, the actual decimal places are as declared, so the same calculation done on a P19.2 results in.
WebFOCUS TABLE                 
================                
key     money - FORMAT=P19.2,
1	39.33
2	39.67
3	39.43
4	39.46

So this is now different, and it is the way that the data is held. Also a P field cannot have the number of decimal places changed without reloading the data. But if the data is reloaded with P19.4 and reported as P19.2 the results are the same and round as expected.
WebFOCUS TABLE                 
================                
key     money - FORMAT=P19.4,
1	39.3333
2	39.6667
3	39.4250
4	39.4550


So the P field is rounding as expected so is a better choice, IMHO, for this type of data.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Reply With QuoteReport This Post
Silver Member
posted Hide Post
quote:
We use Oracle and all of our money fields are USAGE=D10.2 and ACTUAL=D8. We've never had an issue with rounding.


As I mentioned The SQl Server data is rounding correctly but the WebFOCUS rounding was inconsistent. We were trying to figure out why and replicate the error if possible(the checkout program was creating a lot of noise because of this).

Alan B's post clearly explains the difference.. The differences are because of the way we load the data..
Thank you.. Truly a Virtuoso.


Release: Webfocus 7.6.11
OS/Platform: Windows
Output: HTML, EXL2K
 
Posts: 32 | Registered: October 29, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Rounding Issue (SQL Server vs WebFOCUS)

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.