473,216 Members | 1,272 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 software developers and data experts.

Handling Money Via UDT / UDF (i.e. homegrown "extender")

Folks,

I've been exercising my mind recently about the complexities of implementing
a "currency" data type within DB2 to cope with multiple currencies.

A monetary value is often simply represented as a DECIMAL column : for
example many times I've seen DECIMAL(12,2) used. The issue with this is
that there is nothing to interpret what currency this relates to (US
dollars, Canadian dollars, Euros, British pounds or whatever), and a
mixture of currencies and the SUM() function could be lethal.

So I thought that it would be a good idea to build an "extender" to handle
the situation (by "extender" I mean a set of complex user defined types and
related user defined functions to go with them).

I'm wondering if anyone else had done such a thing, and knows of any code
which is freely available ?

My initial thoughts were that the "currency" type consists of at least the
following elements -

MONETARY_VALUE DECIMAL(12,2)
CURRENCY_TYPE CHAR(3) : there is an ISO standard for this I believe

We'd need homegrown versions of the SUM() and other similar functions.

We'd also need functions to do conversions from one currency to another (and
potentially some tables to store conversion rates : perhaps we need a date
in the "type" as any conversion will be at a specific exchange rate for
that date ?

All thoughts welcome.

Sorry if this is a bit unusual topic, but it interests me ... sad I know.

Phil
Nov 12 '05 #1
7 2703

"Philip Nelson" <te*****@scotdb.com> wrote in message
news:Mh********************@fe06.news.easynews.com ...
Folks,

I've been exercising my mind recently about the complexities of implementing a "currency" data type within DB2 to cope with multiple currencies.

A monetary value is often simply represented as a DECIMAL column : for
example many times I've seen DECIMAL(12,2) used. The issue with this is
that there is nothing to interpret what currency this relates to (US
dollars, Canadian dollars, Euros, British pounds or whatever), and a
mixture of currencies and the SUM() function could be lethal.

So I thought that it would be a good idea to build an "extender" to handle
the situation (by "extender" I mean a set of complex user defined types and related user defined functions to go with them).

I'm wondering if anyone else had done such a thing, and knows of any code
which is freely available ?

My initial thoughts were that the "currency" type consists of at least the
following elements -

MONETARY_VALUE DECIMAL(12,2)
CURRENCY_TYPE CHAR(3) : there is an ISO standard for this I believe

We'd need homegrown versions of the SUM() and other similar functions.

We'd also need functions to do conversions from one currency to another (and potentially some tables to store conversion rates : perhaps we need a date
in the "type" as any conversion will be at a specific exchange rate for
that date ?


This is a good thought and I think a feasible implementation.

--
Matt Emmerton
Nov 12 '05 #2
Philip Nelson wrote:
Folks,

I've been exercising my mind recently about the complexities of implementing
a "currency" data type within DB2 to cope with multiple currencies.

A monetary value is often simply represented as a DECIMAL column : for
example many times I've seen DECIMAL(12,2) used. The issue with this is
that there is nothing to interpret what currency this relates to (US
dollars, Canadian dollars, Euros, British pounds or whatever), and a
mixture of currencies and the SUM() function could be lethal.

So I thought that it would be a good idea to build an "extender" to handle
the situation (by "extender" I mean a set of complex user defined types and
related user defined functions to go with them).

I'm wondering if anyone else had done such a thing, and knows of any code
which is freely available ?

My initial thoughts were that the "currency" type consists of at least the
following elements -

MONETARY_VALUE DECIMAL(12,2)
CURRENCY_TYPE CHAR(3) : there is an ISO standard for this I believe

We'd need homegrown versions of the SUM() and other similar functions.

We'd also need functions to do conversions from one currency to another (and
potentially some tables to store conversion rates : perhaps we need a date
in the "type" as any conversion will be at a specific exchange rate for
that date ?

All thoughts welcome.

Sorry if this is a bit unusual topic, but it interests me ... sad I know.

Phil

I would NOT use this approach for a few reasons:
1. Performance will suck
2. There are limits on LOAD, EXPORT, IMPORT of structured datatypes
3. Did I mention that performance will suck?

With performance I don't mean raw performnec of structured types. No
problem there, but what happens when you try to compare two values?
You need to compare through some funny functions (normalize to a
predefine(yet floating) currency)?
Selfdescribing datatypes spell death for a system geared towards
optimizing based on static information....

The standard solution is to define one distinct type (UDT) per currency.
I.e. you can have a EURO based on DEC(12, 2) and a USD on DEC(12, 2).
Then when you compare EUROs to EURO's you can do native compares,
indexing, hash-joins, .. all the good stuff.
You can also add some comparison functions between the currencies,
perhaps using a webservice to look up the latest exchange rates.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3
Decimal(12,.2) may be adequate for a piggy bank, but is way too small for
the currencies and sums dealt with in the real world. In fact, it's not even
adequate to deal with simple things like the balance of the bank I work in
(expressed in USD).

Another problem you face is that there's no such thing as AN exchange rate,
so that a function as simple as adding two values in different currencies
has no single answer. Exchange rates are variables within a range, where the
range of values tends towards one side or the other, depending on whether
you're buying or selling.

I fear that your view of the world is too simplistic. That's why there's no
"free code" available.

"Philip Nelson" <te*****@scotdb.com> wrote in message
news:Mh********************@fe06.news.easynews.com ...
Folks,

I've been exercising my mind recently about the complexities of
implementing
a "currency" data type within DB2 to cope with multiple currencies.

A monetary value is often simply represented as a DECIMAL column : for
example many times I've seen DECIMAL(12,2) used. The issue with this is
that there is nothing to interpret what currency this relates to (US
dollars, Canadian dollars, Euros, British pounds or whatever), and a
mixture of currencies and the SUM() function could be lethal.

So I thought that it would be a good idea to build an "extender" to handle
the situation (by "extender" I mean a set of complex user defined types
and
related user defined functions to go with them).

I'm wondering if anyone else had done such a thing, and knows of any code
which is freely available ?

My initial thoughts were that the "currency" type consists of at least the
following elements -

MONETARY_VALUE DECIMAL(12,2)
CURRENCY_TYPE CHAR(3) : there is an ISO standard for this I believe

We'd need homegrown versions of the SUM() and other similar functions.

We'd also need functions to do conversions from one currency to another
(and
potentially some tables to store conversion rates : perhaps we need a date
in the "type" as any conversion will be at a specific exchange rate for
that date ?

All thoughts welcome.

Sorry if this is a bit unusual topic, but it interests me ... sad I know.

Phil

Nov 12 '05 #4
AK
Serge,

"The standard solution is to define one distinct type (UDT) per
currency."

that leads to tons of tables like

PAYMENT_USD,
PAYMENT_CAD,
PAYMENT_EURO,

whatever, and all these tables having almost identical structure / RI /
Check constraints etc. Looks like an anomaly to me - storing data
(currency code) in metadata.

Maybe it would be simpler to:

1. have one single table PAYMENT
....
MONETARY_VALUE DECIMAL(12,2)
CURRENCY_TYPE CHAR(3)
....

2. create a set of UDDTs for all the currecncies you need
3. Create views WITH CHECK OPTION on top of single table PAYMENT and
expose your data via those view only. Have these views convert
MONETARY_VALUE to the UDDTs

Does it make sense?

Nov 12 '05 #5
AK wrote:
Serge,

"The standard solution is to define one distinct type (UDT) per
currency."

that leads to tons of tables like

PAYMENT_USD,
PAYMENT_CAD,
PAYMENT_EURO,

whatever, and all these tables having almost identical structure / RI /
Check constraints etc. Looks like an anomaly to me - storing data
(currency code) in metadata.

Maybe it would be simpler to:

1. have one single table PAYMENT
...
MONETARY_VALUE DECIMAL(12,2)
CURRENCY_TYPE CHAR(3)
...

2. create a set of UDDTs for all the currecncies you need
3. Create views WITH CHECK OPTION on top of single table PAYMENT and
expose your data via those view only. Have these views convert
MONETARY_VALUE to the UDDTs

Does it make sense?

Yes, if that is the need then I agree.
But how many cases are there where arbitrary currency values are held in
a table?
At my bank I can have an account in CAND and USD, only...

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #6
Serge Rielau wrote:
But how many cases are there where arbitrary currency values are held in
a table?
At my bank I can have an account in CAND and USD, only...


I have to think that a company such as IBM itself would deal in
arbitrary currencies. I would imagine each national "branch" would
deal in its own currency for most transactions (plus maybe USD), but
then when producing the quarterly results, everything needs to be tied
back to USD for SEC reporting.

Banks, too, will deal with even more "currencies": most banks allow you
to buy/sell other currencies at some given rate loosely based on the
market.

And then do other currencies count? Bonds, mutuals, stocks, handled by
most trading companies - are these currencies? One could really
abstract the heck out of this, I would think.

Just because I can only get CDN at my local ATM, or I can only open CDN
and USD accounts at my local branch, doesn't mean that's all the
currencies a bank deals with ;-)
Nov 12 '05 #7
Darin McBride wrote:
Serge Rielau wrote:

But how many cases are there where arbitrary currency values are held in
a table?
At my bank I can have an account in CAND and USD, only...

I have to think that a company such as IBM itself would deal in
arbitrary currencies. I would imagine each national "branch" would
deal in its own currency for most transactions (plus maybe USD), but
then when producing the quarterly results, everything needs to be tied
back to USD for SEC reporting.

Are you telling me you would track yoru revenue centrally, but not
convert to your "master" currency directly.
I think I would take serious offense with such a design...
Also the question whether conversion rates are those of the booking date
at the subsidiary or the report date plays in here. Im CS and not MBA..
so I shut up on that one....
Banks, too, will deal with even more "currencies": most banks allow you
to buy/sell other currencies at some given rate loosely based on the
market. Yes, OK.. but these are point in time transactions. Your account will
NOT show that you received 100 EUR. It shows you received a bucket of
CAND with a nice annotation on that this was originally 100 EUR
converted with a rate of 1/x bucket.
And then do other currencies count? Bonds, mutuals, stocks, handled by
most trading companies - are these currencies? One could really
abstract the heck out of this, I would think. And it's not a new problem, and it has been solved quite satisfactory
long before structured types came around. Banking and OO.. never seen
that combination.
Just because I can only get CDN at my local ATM, or I can only open CDN
and USD accounts at my local branch, doesn't mean that's all the
currencies a bank deals with ;-)

That wasn't the question. The question is what do I need to store
persistently and in general. No question there are cases where I want to
store holdings with a qualifying currency columns, but that sure won't
be the common case.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Bill Loren | last post by:
Hello ppl, I'm having difficulties to accomplish some simple chores with binary data. I'm having a string (?) which I received via an HTTP transactions which is a binary file. Problem is the...
12
by: David Garamond | last post by:
What do people recommend for storing money amounts? I've seen people use NUMERIC(18,3) and other use NUMERIC(18,4). Which one is more appropriate and why? This is considering various existing...
0
by: Brent Clements | last post by:
I have been trying to determine the best way to setup a directory structure for my homegrown MVC application. What do you guys suggest? I am thinking about doing the following: | +-- app
41
by: Jordan | last post by:
While writing some code, I realized I had never developed a consistent pattern for checking errors from a method. I have two styles I jump back and forth between, but I'm wondering which is...
6
by: cj | last post by:
I have a comma delimited file with 2 numeric columns. The first is just a decimal number that seems to have a max of 1 decimal place. The last column is money BUT very tiny increments of money. ...
3
by: bob | last post by:
Hi, I'm looking at a legacy string class thats been in use here for a while and I'd like to check out any options available to optimise it. I see a couple of constructors that look dubious....
44
by: Kulgan | last post by:
Hi I am struggling to find definitive information on how IE 5.5, 6 and 7 handle character input (I am happy with the display of text). I have two main questions: 1. Does IE automaticall...
7
by: Jason Kester | last post by:
Best I can tell, there are three basic ways you can deal with global error handling in ASP.NET. Namely: 1. Derive all your pages from a custom Page class, and override OnError() 2. Specify a...
4
by: gamaz | last post by:
Hi, I am trying to work on a stored procedure that will work with multiple database. I have a prototype of multiple databases. Those are named as the following: ts2_aldkm_app, ts2_aldkp_app,...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.