Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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 Join in SQL Query vickey_dw DB2 1 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us