DB2 casting to DECIMAL

Question

I'm trying to cast a number to DECIMAL(10,2). If I understand well, a number like "1234567890" should be a valid DECIMAL(10,2). But when I try to cast it, it returns an -413 error:

"OVERFLOW OR UNDERFLOW OCCURRED DURING NUMERIC DATA TYPE CONVERSION"

This is the SQL:

select CAST( 1234567890 AS DECIMAL(10,2)) from SYSIBM.SYSDUMMY1;

As I see it, the problem is that it is adding two zero decimal positions, so the total length is 12, which is not valid number for a (10,2).

In fact, if I try to CAST it to (12,2) it works (showing "1234567890.00" ).

I wonder if this is an error or if I'm doing something wrong. CASTING to (12,2) does not seem to be a valid option, because it will accept numbers like "1234567890.12" that should not be valid for a (10,2).

Thanks in advance.

Answer 1

The DECIMAL data type is specified by providing two numbers:

1. The first integer is the precision of the number; that is, the total number of digits; it may range from 1 to 31.
2. The second integer is the scale of the number; that is, the number of digits to the right of the decimal point; it may range from 0 to the precision of the number.

In your case DECIMAL(10,2) means a total of 10 digits with 2 of them used for the scale. The number 1234567890 therefore does not fit, 12345678.90 would.