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>,

Change the field format in the WebFOCUS database to P19.2.

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.

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.

post the MFD's of the columns and the suffix from the MFD's please

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,

Hi pvparuc,

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

Thanks and regards,

Kathryn

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

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.

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.

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.