|
View previous topic :: View next topic
|
| Author |
Message |
knobi
New User
Joined: 16 Aug 2005
Posts: 55
|
|
|
|
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 |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
|
|
search the manuals for something along the line of ...
select name,s1,s2,sum(s1,s2) ... |
|
| Back to top |
|
|
knobi
New User
Joined: 16 Aug 2005
Posts: 55
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
knobi
New User
Joined: 16 Aug 2005
Posts: 55
|
|
|
|
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 |
|
|
knobi
New User
Joined: 16 Aug 2005
Posts: 55
|
|
|
|
| dick scherrer.. thanks for your attention on this thread ... |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
| Back to top |
|
|
karisurya
New User
Joined: 02 Aug 2007
Posts: 64
Location: Bangalore
|
|
|
|
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 |
|
|
knobi
New User
Joined: 16 Aug 2005
Posts: 55
|
|
|
|
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 |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hello Knobi,
Which tool you are using Spufi.Qmf, Batch etc? What exactly error message you are getting? |
|
| Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
|
|
| 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 |
|
|
|
|
