Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.

Dismiss

[Info-Ingres] Weird money problem. EA/MSSQL conversion to Ingres

62 views

Skip to first unread message

Paul White

unread,

Jan 3, 2013, 12:39:05 PM1/3/13

to Ingres and related product discussion forum

Happy new year all.

I have two money fields which appear to be equal but are not. Has anyone encountered this before? There are 3 records below which should not display. An OpenROAD screen shows the difference is -0 which makes me think there are some bits floating around somewhere. The faulty data originates from data conversion via EA/MSSQL. Data is fixed now so I have just documented this for future reference.

select doc_no, status, amount_due, orig_amount,

amount_due - orig_amount from open_items

where cust_code = 'C00524'

and orig_amount <> amount_due

and status <> 'F'

Executing . . .

+--------------------+------+--------------------+--------------------+--------------------+

+--------------------+------+--------------------+--------------------+--------------------+

|149286 | | $204.81| $1853.66| $-1648.85|

|153212 | | $299.90| $2079.69| $-1779.79|

|155694 | | $0.01| $14566.63| $-14566.62|

|156443 | | $32.26| $2293.90| $-2261.64|

|157139 | | $64.85| $1138.73| $-1073.88|

|157401 | | $19.98| $2467.52| $-2447.54|

|157414 | | $280.33| $20468.20| $-20187.87|

|157900 | | $756.48| $7412.97| $-6656.49|

|159064 | | $2.62| $5723.35| $-5720.73|

|159616 | | $56.36| $4028.37| $-3972.01|

|159731 | | $33.99| $7858.43| $-7824.44|

|161071 | | $364.91| $12913.16| $-12548.25|

|162080 |P | $503.01| $10655.86| $-10152.85|

|162258 |P | $277.25| $3600.98| $-3323.73|

|162827 | | $5015.57| $5015.57| $0.00|

|162950 | | $2605.16| $2605.16| $0.00|

|163130 | | $1265.38| $1265.38| $0.00|

|CR-015297 | | $-3.45| $-38.00| $34.55|

+--------------------+------+--------------------+--------------------+--------------------+

(18 rows)

I checked these variations also

float8(amount_due) - float8(orig_amount)

float8(amount_due - orig_amount)

To fix the problem I rewrote the data.

-- This didn’t have any effect.

update open_items

set amount_due = money(amount_due), orig_amount = money(orig_amount)

where orig_amount <> amount_due

and status <> 'F'

\p\g

-- And neither did this. Hmmm my manual says round() works for any number.

update open_items

set amount_due = round(amount_due,2), orig_amount = round(orig_amount,2)

where orig_amount <> amount_due

and status <> 'F'

Executing . . .

E_US0B5C line 1, Function 'round' is not defined for arguments of type

'money' and 'integer'. Explicitly convert its arguments to the desired

type(s).

(Thu Jan 03 14:57:50 2013)

-- This worked

update open_items

set amount_due = round(float8(amount_due),2),

orig_amount = round(float8(orig_amount),2)

where cust_code = 'C00524'

and orig_amount <> amount_due

and status <> 'F'

Executing . . .

(18 rows)

continue

* * * * * * * * *

select doc_no,status,

amount_due, orig_amount,

amount_due - orig_amount from open_items

where cust_code = 'C00524'

and orig_amount <> amount_due

and status <> 'F'

Executing . . .

+--------------------+------+--------------------+--------------------+--------------------+

+--------------------+------+--------------------+--------------------+--------------------+

|149286 | | $204.81| $1853.66| $-1648.85|

|153212 | | $299.90| $2079.69| $-1779.79|

|155694 | | $0.01| $14566.63| $-14566.62|

|156443 | | $32.26| $2293.90| $-2261.64|

|157139 | | $64.85| $1138.73| $-1073.88|

|157401 | | $19.98| $2467.52| $-2447.54|

|157414 | | $280.33| $20468.20| $-20187.87|

|157900 | | $756.48| $7412.97| $-6656.49|

|159064 | | $2.62| $5723.35| $-5720.73|

|159616 | | $56.36| $4028.37| $-3972.01|

|159731 | | $33.99| $7858.43| $-7824.44|

|161071 | | $364.91| $12913.16| $-12548.25|

|162080 |P | $503.01| $10655.86| $-10152.85|

|162258 |P | $277.25| $3600.98| $-3323.73|

|CR-015297 | | $-3.45| $-38.00| $34.55|

+--------------------+------+--------------------+--------------------+--------------------+

(15 rows)

I’m sure the problem originated with data migrated via EA/MSSQL. The conversion staging data is float which shows very small differences.

original data in mssql

doc_no amount_due orig_amount

-------------------- ---------------------- ---------------------- ----------------------

149286 20481 185366 -164885

153212 29990 207969 -177979

156443 3226 229390 -226164

155694 1 1456663 -1456662

157139 6485 113873 -107388

157401 1998 246752 -244754

157414 28033 2046820 -2018787

159616 5636 402837 -397201

157900 75648 741297 -665649

159064 262 572335 -572073

159731 3399 785843 -782444

161071 36491 1291316 -1254825

162950 260516 260516 2.91038304567337E-11

163130 126538 126538 1.45519152283669E-11

162827 501557 501557 5.82076609134674E-11

CR-015297 -345 -3800 3455

(16 row(s) affected)

The migration steps used copy table statement via copy.out and copy.in scripts. EA/MSSQL creates float fields to store money.

sql mysqldb/mssql < extract.sql

create table peer_open_items(

cust_code char(12) not null not default,

assignee_no integer not null default 0,

doc_no varchar(20) not null not default,

doc_date date not null not default,

trans_type integer not null not default,

amount_due money not null not default,

orig_amount money not null not default,

gst_amount float not null default 0,

[steps to populate staging table]

copy table peer_open_items () into 'peer_open_items.ingres'; \p\g

sql myingresdb < load.sql

create table peer_open_items(

cust_code char(12) not null not default,

assignee_no integer not null default 0,

doc_no varchar(20) not null not default,

doc_date date not null not default,

trans_type integer not null not default,

amount_due money not null not default,

orig_amount money not null not default,

gst_amount float not null default 0,

copy table peer_open_items () from 'peer_open_items.ingres'; \p\g

insert into open_items select * from peer_open_items \p\g

regards

Paul White

& Shift Seven Solutions

84 Annie Drive Peregian Beach QLD 4573

Mob 0414 681799

Ingres Forums

unread,

Jan 3, 2013, 2:40:04 PM1/3/13

Paul White;44323 Wrote:
> Happy new year all.
> I have two money fields which appear to be equal but are not. Has
> anyone
> encountered this before? There are 3 records below which should not
> display. An OpenROAD screen shows the difference is -0 which makes me
> think
> there are some bits floating around somewhere. The faulty data
> originates
> from data conversion via EA/MSSQL. Data is fixed now so I have just
> documented this for future reference.
>
> >

Code:
--------------------

> >
> select doc_no, status, amount_due, orig_amount,
> amount_due - orig_amount from open_items
> where cust_code = 'C00524'
> and orig_amount <> amount_due
> and status <> 'F'
>
> Executing . . .
>
> +--------------------+------+--------------------+--------------------+--------------------+
> |doc_no |status|amount_due |orig_amount |col5|
> +--------------------+------+--------------------+--------------------+--------------------+

> .......

> |162827 | | $5015.57| $5015.57|$0.00|
> |162950 | | $2605.16| $2605.16|$0.00|
> |163130 | | $1265.38| $1265.38|$0.00|
> |CR-015297 | | $-3.45| $-38.00|$34.55|
> +--------------------+------+--------------------+--------------------+--------------------+
>
> (18 rows)
>

--------------------

> .....snip...

> (15 rows)
>
>
> I'm sure the problem originated with data migrated via EA/MSSQL. The
> conversion staging data is float which shows very small differences.
>
>
> original data in mssql
> >

Code:
--------------------

> >
> doc_no amount_due orig_amount
> -------------------- ---------------------- --------------------------------------------
> 149286 20481 185366 -164885
> 153212 29990 207969 -177979
> 156443 3226 229390 -226164
> 155694 1 1456663 -1456662
> 157139 6485 113873 -107388
> 157401 1998 246752 -244754
> 157414 28033 2046820 -2018787
> 159616 5636 402837 -397201
> 157900 75648 741297 -665649
> 159064 262 572335 -572073
> 159731 3399 785843 -782444
> 161071 36491 1291316 -1254825
> 162950 260516 260516 2.91038304567337E-11
> 163130 126538 126538 1.45519152283669E-11
> 162827 501557 501557 5.82076609134674E-11
> CR-015297 -345 -3800 3455
>
> (16 row(s) affected)
>

--------------------

> >
>
> The migration steps used copy table statement via copy.out and
> copy.in
> scripts. EA/MSSQL creates float fields to store money.
>
>
> sql mysqldb/mssql < extract.sql
> create table peer_open_items(
> cust_code char(12) not null not default,
> assignee_no integer not null default 0,
> doc_no varchar(20) not null not default,
> doc_date date not null not default,
> trans_type integer not null not default,
> amount_due money not null not default,
> orig_amount money not null not default,
> gst_amount float not null default 0,

> ...

> [steps to populate staging table]

> ...

> copy table peer_open_items () into 'peer_open_items.ingres'; \p\g
>
> \q
>
> sql myingresdb < load.sql
> create table peer_open_items(
> cust_code char(12) not null not default,
> assignee_no integer not null default 0,
> doc_no varchar(20) not null not default,
> doc_date date not null not default,
> trans_type integer not null not default,
> amount_due money not null not default,
> orig_amount money not null not default,
> gst_amount float not null default 0,

> ...

> copy table peer_open_items () from 'peer_open_items.ingres'; \p\g

> ...

> insert into open_items select * from peer_open_items \p\g
>

The variations in the original mssql data shown above a very small
amount that doesn't fit in the default money format display, so the
delta is displayed as zero. The money type is a floating point value
internally and so it has many of the same characteristics of float. If
we look at:

Code:
--------------------

162827 501557 501557 5.82076609134674E-11

--------------------

I'm assuming the delta is the column on the right hand side. The values
have been truncated for display. Looking at the delta (I'm using Python,
quicker than knocking up a C demo):

Code:
--------------------

Python 2.7.3 (default, Apr 10 2012, 23:31:26) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> x = 5.82076609134674E-11
>>> print '%026.25f' % x
0.0000000000582076609134674

--------------------

We can see it really is small (I like using regular decimal display
rather than E to show how small things really are). It is much smaller
than a cent/penny which is why the delta appear to be zero in the
initial query.

The less than a penny value probably ended up in the database due to the
binary copy table, as this does not go through any rounding. If you
where to try and use a literal value that small it would be rounded up
automatically, e.g.

Code:
--------------------

drop table peer_demo;

create table peer_demo(

cust_code char(12) not null not default,

amount_due money not null not default,
orig_amount money not null not default

);
Executing . . .

continue
* * * *

insert into peer_demo(cust_code, amount_due, orig_amount) values
('162827', 501557, 501557 + 5.82076609134674E-11);
Executing . . .

(1 row)
continue
* * * insert into peer_demo(cust_code, amount_due, orig_amount) values
('1', 501557, 501557.0000000000582076609134674);
Executing . . .

(1 row)

continue
* * * *

select *
from peer_demo
Executing . . .

+------------+--------------------+--------------------+
|cust_code |amount_due |orig_amount |
+------------+--------------------+--------------------+
|162827 | $501557.00| $501557.00|
|1 | $501557.00| $501557.00|
+------------+--------------------+--------------------+
(2 rows)

continue
* * * * *

select *, orig_amount - amount_due
from peer_demo
where orig_amount <> amount_due;
Executing . . .

+------------+--------------------+--------------------+--------------------+
|cust_code |amount_due |orig_amount |col4 |
+------------+--------------------+--------------------+--------------------+
+------------+--------------------+--------------------+--------------------+
(0 rows)
continue
*
Your SQL statement(s) have been committed.

--------------------

I'm not sure what types were used originally but I wanted to comment
on:

Paul White;44323 Wrote:
> EA/MSSQL creates float fields to store money.

I'm guessing one of the (two possible) mssql money types was used as the
source?

Unless you are using an (over ten years) old version of EA, you have
control over the datatype EA uses for money. The recommended type for
OpenSQL money is the Microsoft SQL Server Money type. See iigwcatmss for
how to choose the type. Old versions of EA only supported float and we
still support that for backwards compatibility. However this is more of
an interesting aside as money is a type of float so the same issue could
potentially happen which ever type is used. But it may be worth looking
into money mapping if you perform these conversions on a regular basis.

Coming back to your original question, "I have two money fields which

appear to be equal but are not. Has anyone encountered this before?"

This is a classic inexact datatype comparison problem, most commonly
seen with floats. The typically approach to this is to perform safe
comparisons on the difference, e.g. something like:

select ... where abs(float_col1 - float_col2) > 0.001;

Or the rounding operation you performed.

The alternative is to use a different type, e.g. integers of pennies
($0.01 == 1, $1.00 == 100) or decimal. But it depends on how you expect
rounding (etc.) to be performed, ints and decimals typically truncate.

--
clach04
------------------------------------------------------------------------
clach04's Profile: http://community.actian.com/forum/member.php?userid=648
View this thread: http://community.actian.com/forum/showthread.php?t=14963

Ian Kirkham

unread,

Jan 3, 2013, 3:02:50 PM1/3/13

to Ingres and related product discussion forum

Hi Paul,

MONEY is an inexact numeric datatype so you should be expecting representation differences due to rounding.

Regarding the ROUND function, I suggest you raise an issue & mention me.

Regards,

Ian

From: info-ingr...@kettleriverconsulting.com [mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of Paul White
Sent: 03 January 2013 17:39
To: 'Ingres and related product discussion forum'
Subject: [Info-Ingres] Weird money problem. EA/MSSQL conversion to Ingres

Happy new year all.

select doc_no, status, amount_due, orig_amount,

amount_due - orig_amount from open_items

where cust_code = 'C00524'

and orig_amount <> amount_due

and status <> 'F'

Executing . . .

+--------------------+------+--------------------+--------------------+--------------------+

+--------------------+------+--------------------+--------------------+--------------------+

|149286 | | $204.81| $1853.66| $-1648.85|

|153212 | | $299.90| $2079.69| $-1779.79|

|155694 | | $0.01| $14566.63| $-14566.62|

|156443 | | $32.26| $2293.90| $-2261.64|

|157139 | | $64.85| $1138.73| $-1073.88|

|157401 | | $19.98| $2467.52| $-2447.54|

|157414 | | $280.33| $20468.20| $-20187.87|

|157900 | | $756.48| $7412.97| $-6656.49|

|159064 | | $2.62| $5723.35| $-5720.73|

|159616 | | $56.36| $4028.37| $-3972.01|

|159731 | | $33.99| $7858.43| $-7824.44|

|161071 | | $364.91| $12913.16| $-12548.25|

|162080 |P | $503.01| $10655.86| $-10152.85|

|162258 |P | $277.25| $3600.98| $-3323.73|

|162827 | | $5015.57| $5015.57| $0.00|

|162950 | | $2605.16| $2605.16| $0.00|

|163130 | | $1265.38| $1265.38| $0.00|

|CR-015297 | | $-3.45| $-38.00| $34.55|

+--------------------+------+--------------------+--------------------+--------------------+

(18 rows)

I checked these variations also

|149286 | | $204.81| $1853.66| $-1648.85|

|153212 | | $299.90| $2079.69| $-1779.79|

|155694 | | $0.01| $14566.63| $-14566.62|

|156443 | | $32.26| $2293.90| $-2261.64|

|157139 | | $64.85| $1138.73| $-1073.88|

|157401 | | $19.98| $2467.52| $-2447.54|

|157414 | | $280.33| $20468.20| $-20187.87|

|157900 | | $756.48| $7412.97| $-6656.49|

|159064 | | $2.62| $5723.35| $-5720.73|

|159616 | | $56.36| $4028.37| $-3972.01|

|159731 | | $33.99| $7858.43| $-7824.44|

|161071 | | $364.91| $12913.16| $-12548.25|

|162080 |P | $503.01| $10655.86| $-10152.85|

|162258 |P | $277.25| $3600.98| $-3323.73|

|CR-015297 | | $-3.45| $-38.00| $34.55|

+--------------------+------+--------------------+--------------------+--------------------+

(15 rows)

I’m sure the problem originated with data migrated via EA/MSSQL. The conversion staging data is float which shows very small differences.

original data in mssql

doc_no amount_due orig_amount

-------------------- ---------------------- ---------------------- ----------------------

149286 20481 185366 -164885

153212 29990 207969 -177979

156443 3226 229390 -226164

155694 1 1456663 -1456662

157139 6485 113873 -107388

157401 1998 246752 -244754

157414 28033 2046820 -2018787

159616 5636 402837 -397201

157900 75648 741297 -665649

159064 262 572335 -572073

159731 3399 785843 -782444

161071 36491 1291316 -1254825

162950 260516 260516 2.91038304567337E-11

163130 126538 126538 1.45519152283669E-11

162827 501557 501557 5.82076609134674E-11

CR-015297 -345 -3800 3455

(16 row(s) affected)

The migration steps used copy table statement via copy.out and copy.in scripts. EA/MSSQL creates float fields to store money.

sql mysqldb/mssql < extract.sql

create table peer_open_items(

cust_code char(12) not null not default,

assignee_no integer not null default 0,

doc_no varchar(20) not null not default,

doc_date date not null not default,

trans_type integer not null not default,

amount_due money not null not default,

orig_amount money not null not default,

gst_amount float not null default 0,

[steps to populate staging table]

copy table peer_open_items () into 'peer_open_items.ingres'; \p\g

sql myingresdb < load.sql

create table peer_open_items(

cust_code char(12) not null not default,

assignee_no integer not null default 0,

doc_no varchar(20) not null not default,

doc_date date not null not default,

trans_type integer not null not default,

amount_due money not null not default,

orig_amount money not null not default,

gst_amount float not null default 0,

copy table peer_open_items () from 'peer_open_items.ingres'; \p\g

insert into open_items select * from peer_open_items \p\g

regards

Paul White

unread,

Jan 3, 2013, 3:41:10 PM1/3/13

to Ingres and related product discussion forum

Hi Chris and Ian,

Thanks for your replies.

> The money type is a floating point value internally and so it has many of the same characteristics of float.

> MONEY is an inexact numeric datatype so you should be expecting representation differences

Wow. Over countless years I have relied heavily on Ingres money type to be exact.

>select ... where abs(float_col1 - float_col2) > 0.001;

I can’t imagine having to change all our applications to do the same when comparing money fields.

Should we be using decimal(16,2) for example?

EAVersion.rel

EA 2.7/1001 (int.w32/103)

14265

I used the default when creating the EA catalogs. Float. Perhaps the default should be changed to money?

E:\IngresEA\ingres>iigwcatmss

EA MSSQL 2.7/1001 (int.w32/103), MSSQL Catalog Program

At any time enter HELP for more info, QUIT to abort.

Alias Name> edlynsql

DBA name used to qualify unqualified objects> ingres

OpenSQL MSSQL Money Mapping

1 - FLOAT - default

2 - MONEY

[1-2]?>

Yes it was binary unload/load on the same machine but two different instances.

The source MSSQL data types were MONEY but as I mentioned, the staging table is float by default. The extract sql is something like this

insert into peer_open_items

select …, sourceamt1 * 100, sourceamt2 * 100, …

Paul

_______________________________________________

Info-Ingres mailing list

Info-...@kettleriverconsulting.com

http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres

Chris

unread,

Jan 3, 2013, 4:55:10 PM1/3/13

to Ingres and related product discussion forum

On Thursday, January 3, 2013 12:41:10 PM UTC-8, Paul White wrote:
> Hi Chris
> and Ian,
>
> Thanks
> for your replies.
>
> > The
> money type is a floating point value internally and so it has many of the same
> characteristics of float.
>
> > MONEY
> is an inexact numeric datatype so you should be expecting representation
> differences
>
> Wow. Over
> countless years I have relied heavily on Ingres money type to be exact.
>
> >select
> ... where abs(float_col1 - float_col2) > 0.001;
>
> I can’t
> imagine having to change all our applications to do the same when comparing
> money fields.
>
> Should we
> be using decimal(16,2) for example?

For most currencies that English speakers deal with, that is a fine type and range to use. Again be aware of the semantics at play, truncation is going to occur for math so be aware of that. In the example below is 0.66 or 0.67 expected?

drop table peer_dec;
create table peer_dec(
col1 varchar(10),
amt decimal(16, 2)
);

insert into peer_dec(col1, amt) values ('a', '1.00');

select *, (amt / 3) * 2 from peer_dec;

Executing . . .

(1 row)

+----------+------------------+-----------------------------------------+
|col1 |amt |col3 |
+----------+------------------+-----------------------------------------+
|a | 1.00| 0.6666666666666666666666666|
+----------+------------------+-----------------------------------------+
(1 row)
continue
* * * * * *
update peer_dec
set amt = (amt / 3) * 2;

select * from peer_dec;

Executing . . .

(1 row)

+----------+------------------+
|col1 |amt |
+----------+------------------+
|a | 0.66|
+----------+------------------+
(1 row)

continue
*
Your SQL statement(s) have been committed.

One of the advantages of decimal is that most API's have some sort of support for it. For example, ODBC has decimal but NOT money support (this is true for Microsoft SQL Server as well as Ingres). But you need to be aware of the math rules (e.g. truncation) just as one needs to be aware of floats being inexact. If you know the use case(s) you can pick the datatype and the operations on it.

Chris

Karl Schendel

unread,

Jan 3, 2013, 4:41:04 PM1/3/13

to Ingres and related product discussion forum

On Jan 3, 2013, at 3:02 PM, Ian Kirkham wrote:

> Hi Paul,
> MONEY is an inexact numeric datatype so you should be expecting representation differences due to rounding.

Well, not really.

MONEY is stored internally as a float, true enough. The thing is, the float is scaled by 2 decimal places
so that in effect it's really an integer. Floating point can store integer values exactly as long as they
aren't too large. (Had there been sufficient compiler / library support for 64 bit integers back when
MONEY was invented, I'm virtually certain that MONEY would be integer8 and not float.)

My guess is that the float -> money coercion does nothing, at least in the EA context. That
would allow bogus bits to appear in the money value. MONEY values which originate as
tokens, strings, decimals, or integers should be exact unless there are bugs in the
MONEY coercions. From what I can see of ADF it looks like some attempt is made to
trim the garbage away from coerced floats, so maybe that code isn't in EA, or maybe
there is a bug in there.

Karl

Chris

unread,

Jan 3, 2013, 4:55:10 PM1/3/13

to comp.datab...@googlegroups.com, Ingres and related product discussion forum

On Thursday, January 3, 2013 12:41:10 PM UTC-8, Paul White wrote:

> Hi Chris
> and Ian,
>
> Thanks
> for your replies.
>
> > The
> money type is a floating point value internally and so it has many of the same
> characteristics of float.
>
> > MONEY
> is an inexact numeric datatype so you should be expecting representation
> differences
>
> Wow. Over
> countless years I have relied heavily on Ingres money type to be exact.
>
> >select
> ... where abs(float_col1 - float_col2) > 0.001;
>
> I can’t
> imagine having to change all our applications to do the same when comparing
> money fields.
>
> Should we
> be using decimal(16,2) for example?

Executing . . .

(1 row)

(1 row)
continue

* * * * * *
update peer_dec
set amt = (amt / 3) * 2;

select * from peer_dec;

Executing . . .

(1 row)

+----------+------------------+
|col1 |amt |
+----------+------------------+
|a | 0.66|
+----------+------------------+
(1 row)

continue
*
Your SQL statement(s) have been committed.

0 new messages