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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


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