Decimal precision

[Vamsi]

Hi,
I have a column defined as decimal(4,3).
I get the following error when I try to insert "12.50000000000000"

[IBM][CLI Driver] CLI0111E Numeric value out of range. SQLSTATE=22003
sqlstate = 22003

I even tried with "12.500" and "12.50" , but still get the same error.

My point here is, it allows only one number before the decimal point.
It allows "1.50000000000"

Any help would be appreciated.

Thanks.

[Brian Tkatch]

Vamsi wrote:

Hi,
I have a column defined as decimal(4,3).
I get the following error when I try to insert "12.50000000000000"

[IBM][CLI Driver] CLI0111E Numeric value out of range. SQLSTATE=22003
sqlstate = 22003

I even tried with "12.500" and "12.50" , but still get the same error.

My point here is, it allows only one number before the decimal point.
It allows "1.50000000000"

Any help would be appreciated.

Thanks.

>From the documentation:

"The position of the decimal point is determined by the precision and
the scale of the number. The scale, which is the number of digits in
the fractional part of the number, cannot be negative or greater than
the precision. The maximum precision is 31 digits."

That means:

Total digits: First number
Digits before fixed-point: First number - Second number.
Digits after fixed-point: Second number.

So, to accept "12.50000000000000", the declaration would have to be:
DECIMAL(16,14).

B.

[Vamsi]

Thanks for the quick reply Brian, I think declaring DECIMAL(5,3) should
solve my problem.
Brian Tkatch wrote:

Vamsi wrote:

Hi,
I have a column defined as decimal(4,3).
I get the following error when I try to insert "12.50000000000000"

[IBM][CLI Driver] CLI0111E Numeric value out of range. SQLSTATE=22003
sqlstate = 22003

I even tried with "12.500" and "12.50" , but still get the same error.

My point here is, it allows only one number before the decimal point.
It allows "1.50000000000"

Any help would be appreciated.

Thanks.

From the documentation:

"The position of the decimal point is determined by the precision and
the scale of the number. The scale, which is the number of digits in
the fractional part of the number, cannot be negative or greater than
the precision. The maximum precision is 31 digits."

That means:

Total digits: First number
Digits before fixed-point: First number - Second number.
Digits after fixed-point: Second number.

So, to accept "12.50000000000000", the declaration would have to be:
DECIMAL(16,14).

B.

[Mark A]

"Vamsi" <va****@gmail.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...

Thanks for the quick reply Brian, I think declaring DECIMAL(5,3) should
solve my problem.

Besides the fact that you need 2 significant digits to the left of the
decimal point, using DECIMAL(5,3) takes the same amount of space internally
within DB2 (3 bytes) as DECIMAL(4,3) so it is usually prudent to round up to
the next highest odd number if the precision is an even number.