IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Totalize a table's line thru sql


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
knobi

New User


Joined: 16 Aug 2005
Posts: 54

PostPosted: Mon Dec 21, 2009 10:38 pm
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

Superior Member


Joined: 14 Mar 2007
Posts: 10833
Location: italy

PostPosted: Mon Dec 21, 2009 11:03 pm
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: 54

PostPosted: Mon Dec 21, 2009 11:52 pm
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

Moderator Emeritus


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

PostPosted: Tue Dec 22, 2009 12:45 am
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: 54

PostPosted: Tue Dec 22, 2009 12:50 am
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: 54

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

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

Moderator Emeritus


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

PostPosted: Tue Dec 22, 2009 1:02 am
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

Superior Member


Joined: 14 Mar 2007
Posts: 10833
Location: italy

PostPosted: Tue Dec 22, 2009 1:05 am
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

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
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: 54

PostPosted: Thu Dec 24, 2009 6:05 pm
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: 1207
Location: Bangalore,India

PostPosted: Thu Dec 24, 2009 6:09 pm
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
Reply with quote

Knobi,

Check this out.

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

Moderator Emeritus


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

PostPosted: Sun Dec 27, 2009 2:30 am
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

Superior Member


Joined: 14 Mar 2007
Posts: 10833
Location: italy

PostPosted: Sun Dec 27, 2009 1:06 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Merge files with a key and insert a b... DFSORT/ICETOOL 6
No new posts Repeat a DD line- comment and insert ... CA Products 3
No new posts Best way to create an automated line ... TSO/ISPF 3
No new posts SORT - To repeat a string in same col... SYNCSORT 3
No new posts Read a flat file and update DB2 table JCL & VSAM 2
Search our Forums:

Back to Top