View previous topic :: View next topic
|
Author |
Message |
Khwairakpam Raju Singh
New User
Joined: 05 Dec 2007 Posts: 29 Location: Bangalore
|
|
|
|
HI,
I have a column COL_A in DB2 which is defined as INTEGER of length(4). I want to run follwoing query to get the sum of this column( amount). Eg:
Select sum(Col_A) from tab_A
If i ran the same query which have less data, its working fine but if I tried to run this query which have huge no of rows, I am getting
SQLCODE =-802 and SQLstate= 22003.
Looks like, the output results exceed the boundary of bytes that a integer field can hold. Is there any other way to fix this issue without changing the fields attributes of Col_A.
Thanks in advance |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Quote: |
...defined as INTEGER of length(4)... |
why do posters try to impress us with their wealth of knowledge
when it is just useless facts?
the number of bytes a column contains is only worthwhile consideration when referring to character data.
numeric data types require knowledge of capacity - max value.
have not tested it, but it would seem to me that if you were to CAST COL_A as BIG INTEGER and then SUM, it may work. |
|
Back to top |
|
|
Khwairakpam Raju Singh
New User
Joined: 05 Dec 2007 Posts: 29 Location: Bangalore
|
|
|
|
Wow its working with
Select sum(cast((Col_A) as dec(16,2))) from Tab_A
Dick, thanks a lot for your help :-) |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
glad you were able to overlook my criticism and find a workable solution. |
|
Back to top |
|
|
|