porapu wrote:
I work on a database where some columns are created with Numeric datatype whose precision and scale is Numeric(15,2) where I want more than only 2 decimals. So if I am trying to alter the scale of that column ie to Numeric(15,5) SQL is throwing the error like below:
Unsuccessful metadata update New scale specified for the column must be at most 2
I used the below query to Alter the scale:
ALTER TABLE EMP ALTER NOTINT TYPE Numeric(15,5)
If I am trying to create the column with Numeric(15,5) it is working fine.
But I want to alter the columns whose scale is Numeric(15,2) to Numeric(15,5) through coding..
Helen Borrie answers:
Use:
ALTER TABLE EMP ALTER NOTINT TYPE Numeric(18,5)
You can't alter a numeric type by "pushing the decimal places leftward". It leaves nowhere for the (theoretical) left-hand integers to go.
Note, there is no difference in storage terms between a numeric(15) and a numeric(18). Both are stored as 64-bit integers. What your change will do is alter the exponent of the number from (-2) to (-5). It is stored elsewhere.