db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dag.Wan...@Sun.COM (Dag H. Wanvik)
Subject Re: Alter Table Column Attribute
Date Sun, 21 Mar 2010 18:05:59 GMT
stephensr <stephensr@acm.org> writes:


> I have a table that has a Decimal value column with the attributes shown below. When
I try to enter a decimal value less than 1 in the column it always shows up as 0. I am assuming
this is due to the fact that the column attribute "DECIMAL_DIGITS" is currently set to 0.
>
> Does anyone know the proper Alter Table syntax for changing this DECIMAL_DIGITS attribute
from 0 to non-zero? Also if it is set to say 4 - does that mean that this column will accept
values with 4 digits after the decimal point?

I don't think you can change the column's length or implied decimal
point position. You can work around by adding a column and dropping
the old one, cf ij script below. Cf also 

http://db.apache.org/derby/docs/10.5/ref/rrefsqlj15260.html#rrefsqlj15260

Thanks,
Dag


ij> create table t(d decimal);
0 rows inserted/updated/deleted

ij> insert into t values 0.14,5.0;
2 rows inserted/updated/deleted

ij> select * from t;
D     
------
0     
5     

2 rows selected
ij> alter table t add column d2 decimal (7,2);
0 rows inserted/updated/deleted

ij> update t set d2=d;
2 rows inserted/updated/deleted

ij> select * from t;
D     |D2       
----------------
0     |0.00     
5     |5.00     

2 rows selected

ij> alter table t drop column d;
0 rows inserted/updated/deleted

ij> select * from t;
D2       
---------
0.00     
5.00     

2 rows selected

Mime
View raw message