|
View previous topic :: View next topic
|
| Author |
Message |
Mehala muthiah
New User
Joined: 29 Jul 2007
Posts: 4
Location: chennai
|
|
|
|
input_data1 char(200)
I want to write a query which returns the sum of substring of input_data1 field. eg. i want the sum of 5 bytes starting location 20.
Will this query be executed sucessfully?.
Select (substring(sum(input_data1,20,5))) from table. |
|
| Back to top |
|
 |
William Thompson
Global Moderator
Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
|
|
|
|
| Doesn't the SUM need a numeric type to work on? |
|
| Back to top |
|
|
Mehala muthiah
New User
Joined: 29 Jul 2007
Posts: 4
Location: chennai
|
|
|
|
but internally from 20 to 25 byte of input_data1 variable, we are having numeric data only...... with pic clause s9(5) comp-3....
when i tried to execute this query
Select substring(input_data1,20,5) from table .
it returned the comp-3 variable (which ill be non-displayable format in my case &)
My doubt is can sum function be used on this?.
Thanks for ur quick response. |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
You can cast the data as another type
| Code: |
SELECT SUM(CAST(substring(input_data1,20,5) AS DECIMAL))
FROM TABLE
|
|
|
| Back to top |
|
|
Mehala muthiah
New User
Joined: 29 Jul 2007
Posts: 4
Location: chennai
|
|
|
|
so the output of this query ill be displayable format ?...
this approach looks good.
Thanks |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
It should be. If not, try casting to float or int or something else.
You could do a 2 way cast...
SELECT CAST(SUM(CAST(substring(input_data1,20,5) as decimal) as varchar)
FROM TABLE
This may or may not work. Check the manual on CAST |
|
| Back to top |
|
|
Mehala muthiah
New User
Joined: 29 Jul 2007
Posts: 4
Location: chennai
|
|
|
|
| Since the datatype of that field is char, i was not able to cast it to decimal. |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| Mehala muthiah wrote: |
but internally from 20 to 25 byte of input_data1 variable, we are having numeric data only...... with pic clause s9(5) comp-3....
|
pic s9(5) comp-3 is only 3 bytes! |
|
| Back to top |
|
|
William Thompson
Global Moderator
Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
|
|
|
|
| Mehala muthiah wrote: |
| Since the datatype of that field is char, i was not able to cast it to decimal. |
Strange, when I looked up CAST, char to decimal was listed as valid...... What sort of error are you getting? |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| Did you somehow store packed data in the middle of a CHAR field in DB2? That would be very strange indeed. |
|
| Back to top |
|
|
|
|
