Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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
No new posts What is the Extract Function in JCL dharmaraok JCL & VSAM 3 Wed Jun 14, 2017 9:12 am
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm
No new posts Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am
No new posts Release of Adabas Cursor mohitsethi All Other Mainframe Topics 1 Mon Feb 06, 2017 8:36 pm
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

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us