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
 

 

Totalize a table's line thru sql

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

New User


Joined: 16 Aug 2005
Posts: 46

PostPosted: Mon Dec 21, 2009 10:38 pm    Post subject: Totalize a table's line thru sql
Reply with quote

hello ... i tried to search the forum, but i had no lucky with previous posts.. so if someone points out how to totalize a table's line thru sql or reply here, i would appreciate... I have the following table in db2 v8 :

Code:
name   s1   s2 
-------------------
john    10    20
mary     4     5
helen   10     2



i would like to have
Code:
name   s1   s2     tot
----------------------
john   10   20      30
mary    4    5       9
helen  10    2      12
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Dec 21, 2009 11:03 pm    Post subject: Reply to: Totalizing lines
Reply with quote

search the manuals for something along the line of ...
select name,s1,s2,sum(s1,s2) ...
Back to top
View user's profile Send private message
knobi

New User


Joined: 16 Aug 2005
Posts: 46

PostPosted: Mon Dec 21, 2009 11:52 pm    Post subject:
Reply with quote

would be possible to point some place with an example? i did some variations and the code itself you provided but didn't work.
a see places using union... but not sure how it works...
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Dec 22, 2009 12:45 am    Post subject:
Reply with quote

Hello,

Quote:
i did some variations and the code itself you provided but didn't work
What happened? Did you get unexpected results or some negative sqlcode?

If you post only "it didn't work", we have nothing to use to help you.

Unless there can be multiple rows returned with the same name, you probably don't want SUM. . . I'm not connected just now so this is untested, but you might try something like "select name,s1,s2, (s1 + s2) as tot. . ."
Back to top
View user's profile Send private message
knobi

New User


Joined: 16 Aug 2005
Posts: 46

PostPosted: Tue Dec 22, 2009 12:50 am    Post subject:
Reply with quote

dick scherrer... when i do :

select col1,
col2,
col1 + col2 as tot

from table

i get a msg saying tot column doesn't exist... (this msg come on the QMF panel) ...
Back to top
View user's profile Send private message
knobi

New User


Joined: 16 Aug 2005
Posts: 46

PostPosted: Tue Dec 22, 2009 12:51 am    Post subject:
Reply with quote

dick scherrer.. thanks for your attention on this thread ...
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Dec 22, 2009 1:02 am    Post subject:
Reply with quote

Hello,

Instead of "col1 + col2 as tot ", try "DEC((COL1 + COL2),8,2) AS TOT" or even "(COL1 + COL2) AS TOT".

Hopefully, tomorrow i'll be where i have more connectivity<g>.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Tue Dec 22, 2009 1:05 am    Post subject: Reply to: Totalizing lines
Reply with quote

just had a braincheck...
the syntax should be select name, s1,s2,(s1+s2)

but here is the link to the db2 manuals bookshelf

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/Shelves/DSNSHKA3
Back to top
View user's profile Send private message
karisurya

New User


Joined: 02 Aug 2007
Posts: 64
Location: Bangalore

PostPosted: Tue Dec 22, 2009 3:52 pm    Post subject:
Reply with quote

Hi,
I tried
Code:

SELECT S1,S2,(S1+S2) AS TOTAL FROM XXXXX


and it worked for me with out any problem in SPUFI.
Can you post the exact error you got?

Regards,
Surya
Back to top
View user's profile Send private message
knobi

New User


Joined: 16 Aug 2005
Posts: 46

PostPosted: Thu Dec 24, 2009 6:05 pm    Post subject: Reply to: Totalize a table's line thru sql
Reply with quote

Thanks all those who really contributed for this thread... I tried exhaustively each suggestion provided here but with no success. I understand now once this SQL is executed on QMF these approaches don't work as they work in another tools (like spufi, thru batch job etc..). If anyone has any other suggestion, i appreciate.. thanks.


by the way I've sent a private message to the global moderator.. and so far i didn't have a response. enrico-sorichetti, would please take a look at my msg, thanks !
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Thu Dec 24, 2009 6:09 pm    Post subject:
Reply with quote

Hello Knobi,

Which tool you are using Spufi.Qmf, Batch etc? What exactly error message you are getting?
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Thu Dec 24, 2009 6:38 pm    Post subject:
Reply with quote

Knobi,

Check this out.

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSQGC090/5.5?SHELF=&DT=20070130233237
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Dec 27, 2009 2:30 am    Post subject:
Reply with quote

Hello,

Quote:
I tried exhaustively each suggestion provided here but with no success
One more time. . . Do not post "it didn't work". . .

You need to post exactly what you tried and what happened for each attempt. If an sqlcode was presented or if unexpected/undesired results occurred, you need to show these.

Quote:
I understand now once this SQL is executed on QMF these approaches don't work as they work in another tools
This new understanding may not be correct. We can see more when the actual problems are posted.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Sun Dec 27, 2009 1:06 pm    Post subject: Reply to: Totalize a table's line thru sql
Reply with quote

Quote:
by the way I've sent a private message to the global moderator.. and so far i didn't have a response. enrico-sorichetti, would please take a look at my msg, thanks !
a public complaint deserve a public reply...
I did read Your message, since I am not stupid I understood it, and took the action You requested..
so what <derogative interjection> do You have to complain about now!
I deleted Your post also because of the offensive remark
learn to read and understand what is going on, and how to post and behave

Quote:
I understand now once this SQL is executed on QMF these approaches don't work as they work in another tools.If anyone has any other suggestion,

Your understanding is just plain wrong
no reason for a well constructed query not to work in QMF,SPUFI,DSNTEP2
so the best suggestion is ... try to do things properly,
read the manuals
since the query worked for all of us and we did not get error messages,
there is no reason that it should not work for You.
just telling "it did not work" does not give any material to provide help
such approach deserves the only logical reply....
how bad, let' s hope that You will be able to fix it sooner or later
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 Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Insert Lines in JCL with Rexx after a... Willy Jensen CLIST & REXX 3 Tue Aug 30, 2016 4:18 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm
No new posts Getting soc4 on internal table in cobol abdulrafi COBOL Programming 13 Fri May 06, 2016 3:39 pm


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