Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Query for SUM function in DB2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Mehala muthiah

New User


Joined: 29 Jul 2007
Posts: 4
Location: chennai

PostPosted: Sun Jul 29, 2007 10:39 pm    Post subject: Query for SUM function in DB2
Reply with quote

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
View user's profile Send private message

William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Sun Jul 29, 2007 10:52 pm    Post subject:
Reply with quote

Doesn't the SUM need a numeric type to work on?
Back to top
View user's profile Send private message
Mehala muthiah

New User


Joined: 29 Jul 2007
Posts: 4
Location: chennai

PostPosted: Sun Jul 29, 2007 10:58 pm    Post subject: Re: SUM function in DB2
Reply with quote

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
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Sun Jul 29, 2007 11:04 pm    Post subject:
Reply with quote

You can cast the data as another type

Code:

SELECT SUM(CAST(substring(input_data1,20,5) AS DECIMAL))
FROM TABLE
Back to top
View user's profile Send private message
Mehala muthiah

New User


Joined: 29 Jul 2007
Posts: 4
Location: chennai

PostPosted: Sun Jul 29, 2007 11:18 pm    Post subject: Re: SUM function in DB2
Reply with quote

so the output of this query ill be displayable format ?...
this approach looks good.
Thanks
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Jul 30, 2007 12:04 am    Post subject:
Reply with quote

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
View user's profile Send private message
Mehala muthiah

New User


Joined: 29 Jul 2007
Posts: 4
Location: chennai

PostPosted: Tue Jul 31, 2007 11:40 pm    Post subject:
Reply with quote

Since the datatype of that field is char, i was not able to cast it to decimal.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Aug 01, 2007 12:54 am    Post subject: Re: SUM function in DB2
Reply with quote

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
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Wed Aug 01, 2007 2:10 am    Post subject:
Reply with quote

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
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed Aug 01, 2007 2:38 am    Post subject:
Reply with quote

Did you somehow store packed data in the middle of a CHAR field in DB2? That would be very strange indeed.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts What is the Extract Function in JCL dharmaraok JCL & VSAM 3 Wed Jun 14, 2017 9:12 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us