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.