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

How to use SUM function in declaring cursor in db2


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

New User


Joined: 05 Jul 2007
Posts: 10
Location: chennai

PostPosted: Mon Aug 20, 2007 4:11 pm
Reply with quote

HI !
kindly help me , how to use SUM FUNCTION IN DECLARING CURSOR AND FETCHING THE RECORDS USING CURSOR .
REPLY ASAP
Regards
vinod kumar
Back to top
View user's profile Send private message
Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Mon Aug 20, 2007 6:19 pm
Reply with quote

Vinod,

You can create a query for cursor with the SUM() fuction on field you want to get the sum. Code group by if you are selecting othere columns also.

If you can give more specific req, people will be able give u good solutions..

Thanks
Prajesh
Back to top
View user's profile Send private message
noorkh

New User


Joined: 06 Mar 2006
Posts: 76
Location: Chennai

PostPosted: Mon Aug 20, 2007 7:30 pm
Reply with quote

Hi Vinod,

What difficulties you find in querying SUM function in CURSOR? Can you be little explanatory with an example?
Back to top
View user's profile Send private message
vinod71082

New User


Joined: 05 Jul 2007
Posts: 10
Location: chennai

PostPosted: Tue Aug 21, 2007 12:25 pm
Reply with quote

EXEC SQL DECLARE CUR1 CURSOR FOR
SELECT E002_ZACD_NOKY,
E002_ZACD_BASH,
E002_ZANM_NOKY,
E002_ZANM_ZAIK,
E002_CMCD_LKEI,
SUM(E002_ZATK_ZENK),
SUM(E002_ZATK_TOKI),
SUM(E002_ZATK_KEIN),
E002_ZARI_KETO,
SUM(E002_ZATK_KYKU),
SUM(E002_ZAAT_TESR),
E002_ZARI_TESR,
SUM(E002_ZAAT_KYKU),
SUM(E002_ZATK_CHTA),
SUM(E002_ZATK_JITA),
SUM(E002_ZATK_SASN),
SUM(E002_ZATK_HEIK),
E002_ZARI_LOOP
FROM KBBE002 GROUP BY E002_ZACD_NOKY,
E002_CMCD_LKEI
ORDER BY E002_ZACD_NOKY,
E002_CMCD_LKEI
IS THAT ABOVE CODE IS CORRECT OR NOT?
I AM GETTING SOME SQLCODE ERROR WHILE COMPILING MY PROGRAM .
PLEASE CLARIFY ME
REGARDS
VINOD
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Aug 21, 2007 5:35 pm
Reply with quote

You need to code a GROUP by on all the non-summed fields

GROUP BY E002_ZACD_NOKY, E002_ZACD_BASH, etc, etc

That should fix things. Also try running your query as a spufi before attempting to compile the program. Then you will know that it is valid SQL.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Aug 21, 2007 6:02 pm
Reply with quote

[quote="vinod71082"]IS THAT ABOVE CODE IS CORRECT OR NOT?/quote]

No it is not correct that is why you are getting an sql error, see Steve's message about group by.

vinod71082 wrote:
I AM GETTING SOME SQLCODE ERROR WHILE COMPILING MY PROGRAM .


It would be nice if you would share the details with us, it would save a lot of guessing.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Aug 21, 2007 6:11 pm
Reply with quote

Yeah, I should have emphasised the ALL in the first sentence.
Back to top
View user's profile Send private message
vinod71082

New User


Joined: 05 Jul 2007
Posts: 10
Location: chennai

PostPosted: Wed Aug 22, 2007 10:09 am
Reply with quote

thank u steve its working now .
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 Calling an Open C library function in... CICS 1
No new posts DATE2 function SYNCSORT 15
No new posts Help on PL/I jsonPutValue function PL/I & Assembler 8
No new posts how to use Tso outtrap external function All Other Mainframe Topics 8
No new posts INSYNC option with same function as I... JCL & VSAM 0
Search our Forums:

Back to Top