need code in cobol for the respective db2 group by clause

manishgaikwad · New User Joined: 10 Feb 2009 Posts: 5 Location: Pune

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
----------------------

dbzTHEdinosauer · Posted: Tue Feb 10, 2009 7:10 pm

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?

manishgaikwad · New User Joined: 10 Feb 2009 Posts: 5 Location: Pune

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
--------------------------

dick scherrer · Posted: Wed Feb 11, 2009 10:04 am

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.

manishgaikwad · New User Joined: 10 Feb 2009 Posts: 5 Location: Pune

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.

dick scherrer · Posted: Wed Feb 11, 2009 10:23 am

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.

manishgaikwad · New User Joined: 10 Feb 2009 Posts: 5 Location: Pune

ok fine
Thanks a lot.....................

Terry Heinze · Posted: Wed Feb 11, 2009 10:29 am

I'd probably define a 2 dimensional table (array):

dick scherrer · Posted: Wed Feb 11, 2009 10:55 am

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.

Terry Heinze · Posted: Wed Feb 11, 2009 11:00 am

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.

manishgaikwad · New User Joined: 10 Feb 2009 Posts: 5 Location: Pune

YOU ARE ABSOLUTELY RIGHT DEAR,

PS FILE IS THERE
AND
NO DB2 IS THERE....
NOW SUGGEST..........

dick scherrer · Posted: Fri Feb 13, 2009 1:47 am

Hello,

Turn your CAPS off.

You have probably received no replies because you have not done as requested previously:

amar_401 · New User Joined: 20 Sep 2006 Posts: 7

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.

William Thompson · Posted: Fri Feb 13, 2009 2:23 am

amar_401 · New User Joined: 20 Sep 2006 Posts: 7

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

amar_401 · New User Joined: 20 Sep 2006 Posts: 7

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

dick scherrer · Posted: Fri Feb 13, 2009 4:01 am

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. . .

Terry Heinze · Posted: Fri Feb 13, 2009 10:24 am

Amar, how do you know Manishgaikwad's shop has Easytrieve?

amar_401 · New User Joined: 20 Sep 2006 Posts: 7

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.