Re: Multiplying numeric(19,4) Values

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Dear Scott,

Many thanks for your posting and your comments, in answer the points
raised in your post please note the following as this whole thing has
been built as an example to the underlying problem:

1) The posting from before with Hugo is irrelevant as it was part of a
topic that was later concluded by us paying him for an amount of
consultancy work privately: namely the only part of this that is
relevant to the current topic is his suggestion to use numeric(19,4)
for the data as opposed to float which were in use at the time. The
previous topics have no other relevance.
2) Since you have "difficulty in believing" then please consider that
this is financial markets data / transactions, and therefore the
confines of the datatypes (while perhaps not perfect) certainly
accomodate what I believe to be correct. Namely:
a) baldata represents financial considerations for trades which can
get pretty large when you are talking about equity trading / size of
trade / underlying equity price, even more so when you are talking
about "large value" currencies such as Japanese Yen etc.
b) pricedata again can get pretty large given (using the earlier
example) Nikkei index trades where the trade price is in the format
nnnnn.nn
c) valuedata represents percentage values that are applied to the
trade consideration, and are inclusive of sales tax thus contributing
to the need to have extended decimal places. Plus as this particular
field is multi-use depending on other flags in the data structure, it
could be in the format nnnnn.nnnnnn or larger precision.
d) currencydata represents foreign exchange rates, again using the
earlier example, and given the SIZE of the financial considerations
you want as many decimal places as you can get to ensure accuracy and
lack of rounding being needed on consolidated totals. As an example
the GBP/JPY exchange rate given by XE.com is 149.9156760542 and while
this only has three digits to the left if you consider Idonesia
Rupiahs and Iran Rials (five digits to the left, same to the right) or
South Korean Won (four digits to the left, same to the right) then you
can see the need for the datatype precision)
3) In terms of my comments "DOESNT WORK" I was referring back to the
original error message causing me to lose my mind, that being (from
the top of this thread) ""Arithmetic overflow error converting numeric
to data type numeric. "" so apologies if I should have stated this
more clearly, I did think it made sense.

I think what I need to do is try and reduce the components of the
arithmetic calculation, i.e. one side of the mutiply separately to the
other, using CAST or CONVERT but am flummoxed as to how this wouls fit
together: hence the post.

Many thanks anyway,

Alex
.

Relevant Pages