|
View previous topic :: View next topic
|
| Author |
Message |
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hello All,
I am finding strange problem. I have one table which have few column out of all two of them are
CAS4_ADJ_QTY decimal (15,0)
CAS7_ADJ_QTY decimal(15,0)
both are nullable . I want them to alter as decimal(15,3). Forgot to mention that my test table is empty. I am using below query.
| Code: |
alter table t6qjdbg.CLAIM_REMIT_CAS
alter column CAS4_ADJ_QTY
set data type decimal(15,3); |
| Code: |
alter table t6qjdbg.CLAIM_REMIT_CAS
alter column CAS7_ADJ_QTY
set data type decimal(15,3); |
For first query i received sqlcode -190
| Code: |
DSNT408I SQLCODE = -190, ERROR: THE ATTRIBUTES SPECIFIED FOR THE COLUMN CAS4_ADJ_QTY ARE NOT COMPATIBLE WITH THE EXISTING
COLUMN DEFINITION |
while next query executed successfuily.
What might be the reason. Please help. I know we can drop and recreate the table with new def of fields. but i want to work with Alter as prod data is huge. |
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| most probably the original column definitions are not the same ! |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
DECIMAL(p,s) to DECIMAL(q,t) : q>=p and (q-t)>=(p-s)
so (15,0) to (15,3) is not possible : (15 - 3) < (15 - 0)
(15,0) to (18,3) or (12,0) to (15,3) are possible
(Stating DB2 version is also nice) |
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hello GuyC,
I m using DB2 V9.1. and i dont agree DECIMAL(P,S) is going to use P bytes do whether its decimal(p,s) or decimal(p,s+2) would not be any issue,The field length will not changed only the number of decimal places - so, the field is defined
here is a clip
| Code: |
Column Name Col No Col Type Length Scale
* * * * *
-----------------> ------ -------- ------ ------
CAS4_ADJ_QTY 9 DECIMAL 15 3 <=============== 3 decimal places
CAS5_ADJ_RSN_CD 10 CHAR 5 0
CAS6_ADJ_AMT 11 DECIMAL 18 2
CAS7_ADJ_QTY 12 DECIMAL 15 0 <=============== 0 decimal places |
|
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
I agree that the size of the new column is the same, but DB2 disallows the change because of the risk of truncation. For example, if the existing column contains
| Code: |
| 999 999 999 999 999 (spaces shown for readabilty) |
and DB2 allowed you to ALTER it to (15,3) the field would have to be truncated to
| Code: |
| 999 999 999 999 000 (decimal point implied) |
The bottom line is that DB2 is preventing you from damaging your data. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
1) "DECIMAL(p,s) to DECIMAL(q,t) : q>=p and (q-t)>=(p-s) "
I didn't invent this. It's straight out of the SQL reference guide.
2) What don.leahy said |
|
| Back to top |
|
|
|
|
