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
 

 

How to use SUM function in declaring cursor in db2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: How to use SUM function in declaring cursor in db2
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    Post subject: Reply to: How to use SUM function in declaring cursor in db2
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    Post subject:
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    Post subject: Reply to: How to use SUM function in declaring cursor in db2
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    Post subject:
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    Post subject: Re: Reply to: How to use SUM function in declaring cursor in
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    Post subject:
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    Post subject:
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    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
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Strange EXEC function call in z/VM Willy Jensen CLIST & REXX 3 Wed Oct 05, 2016 2:07 pm
No new posts Replace function abdulrafi COBOL Programming 13 Mon Sep 12, 2016 10:12 am


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