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
 

 

need code in cobol for the respective db2 group by clause

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
manishgaikwad

New User


Joined: 10 Feb 2009
Posts: 5
Location: Pune

PostPosted: Tue Feb 10, 2009 6:40 pm    Post subject: need code in cobol for the respective db2 group by clause
Reply with quote

Hi All,
I need a cobol code in which input file has
1)department
2)emp name
3)salary

output file should group the respective dept and will display only
emp name and sal
it should also calculate the summation of sal for the specific department

means it should be like this format.
consider 3 person is having same dept:
emp-name sal
raj 10000
rahul 10000
arpan 60000
-------------------------
total 80000
----------------------
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Feb 10, 2009 7:10 pm    Post subject:
Reply with quote

so if you have two dept, based on your format of output, you will not know which department.

also, are we dealing with files or db2 tables?
Back to top
View user's profile Send private message
manishgaikwad

New User


Joined: 10 Feb 2009
Posts: 5
Location: Pune

PostPosted: Wed Feb 11, 2009 9:59 am    Post subject: Reply to: need code in cobol for the respective db2 group by
Reply with quote

Need a cobol code in which………………





I/p file has ,



Dept NAME salary

IT RAJ 5000

IT RAM 2000

CS RAHUL 6000

CS RICKY 5000

IT VICKY 1000







O/P FILE SHOULD GROUP THE ITEMS BY THE DEPARTMENT..





Name salary

RAJ 5000

RAM 2000

VICKY 1000

-------------------------------------

Total 8000
------------------------------------

Name salary

RAHUL 6000

RICKY 5000
------------------------------
Total 11000
--------------------------
Back to top
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Wed Feb 11, 2009 10:04 am    Post subject:
Reply with quote

Hello,

Are you looking for cobol code, db2/sql code, or both?

You need to post what you have done so far and we can you help complete or correct it.
Back to top
View user's profile Send private message
manishgaikwad

New User


Joined: 10 Feb 2009
Posts: 5
Location: Pune

PostPosted: Wed Feb 11, 2009 10:08 am    Post subject:
Reply with quote

hello,
I need a cobol code for the same.

still started the code.
I can done with array .

need a efficient logic.
just give me such type of code if u have other than using array.
Back to top
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Wed Feb 11, 2009 10:23 am    Post subject:
Reply with quote

Hello,

An array is probably not necessary. . . Not sure how you would accomplish what you want with an array. . .

If the data is in db2 tables (you mention "db2 group by" and "file" but have not clarified what you are working with), you must use sql to read the rows.

Suggest you talk with whoever provided the specifications as the output as described will not be useful for any user.

Do keep in mind that the forum is here to help, not to do the work.
Back to top
View user's profile Send private message
manishgaikwad

New User


Joined: 10 Feb 2009
Posts: 5
Location: Pune

PostPosted: Wed Feb 11, 2009 10:26 am    Post subject:
Reply with quote

ok fine
Thanks a lot.....................
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1238
Location: Richfield, MN, USA

PostPosted: Wed Feb 11, 2009 10:29 am    Post subject:
Reply with quote

I'd probably define a 2 dimensional table (array):
Code:
dept occurs x times
    empl-info occurs y times
        name
        salary
then load the table by reading the input file and placing the dept, name, and salary into the appropriate buckets. This would allow the same person to work in more than 1 dept and you could accumulate the salary fields if your input file had duplicate dept/name combinations.
Back to top
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Wed Feb 11, 2009 10:55 am    Post subject:
Reply with quote

Hi Terry,

Still trying to get a working requirement definition.

I would think that if the data is in db2 tables a cursor with an order by would solve most of the work. As rows are fetched from the cursor, it becomes a simple list/summarize task.

As the process goes, detail or summary recordsd could be written to the output file. Assuming the input is database and the output ios not. . . Maybe we'll get more complete "rules" to work with.
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1238
Location: Richfield, MN, USA

PostPosted: Wed Feb 11, 2009 11:00 am    Post subject:
Reply with quote

Agree. We need a better definition of the requirement. Also, we'd like to see the code generated so far. We still don't know if DB2 is involved or not. I assumed a PS file input and no DB2.
Back to top
View user's profile Send private message
manishgaikwad

New User


Joined: 10 Feb 2009
Posts: 5
Location: Pune

PostPosted: Wed Feb 11, 2009 11:04 am    Post subject:
Reply with quote

YOU ARE ABSOLUTELY RIGHT DEAR,

PS FILE IS THERE
AND
NO DB2 IS THERE....
NOW SUGGEST..........
Back to top
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Fri Feb 13, 2009 1:47 am    Post subject:
Reply with quote

Hello,

Turn your CAPS off.

You have probably received no replies because you have not done as requested previously:
Quote:
You need to post what you have done so far and we can you help complete or correct it.


If your problem is a cobol "group by" you would beed to write code to accomplish this. In order to group items on a report, the input must be previously sorted or the cobol program must sort the data as part of the process.
Back to top
View user's profile Send private message
amar_401

New User


Joined: 20 Sep 2006
Posts: 7

PostPosted: Fri Feb 13, 2009 2:19 am    Post subject: Reply to: need code in cobol for the respective db2 group by
Reply with quote

Hi,

If You want a report in that manner with respect to DEPT, Please go ahead and use EASYTRIEVE language it is very easy to do solve this. It takes only 5 minutes to get this report.

If you dont know EASYTRIEVE then try splitting the file with respect to DEPT in to different files using a SORT in JCL in first steo. Then in the next step displaying them with a heading and at the end of the sorted file Display the total thats it.
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Fri Feb 13, 2009 2:23 am    Post subject: Re: need code in cobol for the respective db2 group by claus
Reply with quote

amar_401 wrote:
Please go ahead and use EASYTRIEVE language it is very easy to do solve this.
True, but
manishgaikwad wrote:
I need a cobol code in which input file has...
Back to top
View user's profile Send private message
amar_401

New User


Joined: 20 Sep 2006
Posts: 7

PostPosted: Fri Feb 13, 2009 2:30 am    Post subject:
Reply with quote

This is the EASYTRIEVE Code for your requirement to display the report with respect to DEPT.

In this CODE EMPL is the input file name i.e stepname of input file in JCL
In this EMP-RPT is the report name i.e stepname of report file in JCL.

FILE EMPL FB(150 1800)

DEPT 1 8 A
NAME 9 30 N (HEADING ('EMPLOYEE' 'NAME')
SAL 31 6 P 2
TOT-SAL W 7 P 2


JOB INPUT EMPL NAME FIRST-PROGRAM


PRINT EMP-RPT
REPORT EMP-RPT LINESIZE 80
TITLE 01 'EMPLOYEE REPORT'
CONTROL DEPT
LINE 01 DEPT NAME SAL
Back to top
View user's profile Send private message
amar_401

New User


Joined: 20 Sep 2006
Posts: 7

PostPosted: Fri Feb 13, 2009 2:38 am    Post subject:
Reply with quote

01/12/90 EMPLOYEE REPORT PAGE 1
EMPLOYEE
DEPT NAME SAL
901 WALTERS 11211

901 11211

903 WIMN 12267

903 12267

912 LOYAL 04225
912 SAMUEL 51111

912 55336

This is the Output format
Back to top
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Fri Feb 13, 2009 4:01 am    Post subject:
Reply with quote

Hello,

Thank you for the contribution, however as William mentioned - cobol was the request not Easytrieve.

Please do not post code that is not cobol when cobol is requested. . .
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1238
Location: Richfield, MN, USA

PostPosted: Fri Feb 13, 2009 10:24 am    Post subject:
Reply with quote

Amar, how do you know Manishgaikwad's shop has Easytrieve?
Back to top
View user's profile Send private message
amar_401

New User


Joined: 20 Sep 2006
Posts: 7

PostPosted: Sat Feb 14, 2009 1:19 am    Post subject:
Reply with quote

Hey,

First Sort the input File in JCL with respect to DEPT. Then in the next step read the records using COBOL program until end of FILE. As you read a record store DEPT in a temporary variable and keep writing in to the report and Totaling the SAL to a temporary variable. After reading Every record check whether the TEMP-DEPT is equal to DEPT of Input file. If the condition satisfies continue writing the report. If the TEMP-DEPT variable and Input DEPT variable is does not match it means a DEPT variable has started so first write total to the report and then the heading of the report and move DEPT to TEMP-DEPT and initialize TOTAL and after that ADD SAL to TOTAL.

Hope this works.
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 -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts What is the code in CLIST to enable t... jackzhang75 CLIST & REXX 1 Fri Dec 02, 2016 3:02 am
No new posts IDEAL - Code Retrieval yugendran CA Products 0 Fri Nov 25, 2016 3:27 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
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm


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