Page 1 of 1

DB2 CURSOR DECLARARTION in COBOL - > to be dynamic

Posted: Thu Nov 24, 2011 5:52 pm
by dichandr
Hi Friends,

I need a clarification on my below query.

For example:
I have a program which generates a report on Employees of the organistion fetching the Employee number, Name & Age from table.

Now i have a requirement to have instance of jobs, 1 would generate a report for the employees who are working in Computers sections and other report coering employees in Production, Maintenance & Sales by using Dept field in table.

Currently SQL cursor is like

EXEC SQL
DECLARE EMP_CSR CURSOR FOR
SELECT EMP_NO
,EMP_NAME
,EMP_AGE
FROM EMP_TABLE
ORDER BY EMP_NO
END-EXEC.

Now i need this to be amended to have where condition like

WHERE EMP_DEPT in ('PROD','MAIN','SALE') for one report and for another it should be like
WHERE EMP_DEPT = 'COMP'

I want to have same program and two jobs and the where condition should be decided based on the value i pass through JCL PARM Parameter.

For example if i pass 'C' as my JCL PARM value then the job should generate a report only for employees in Computers dept. If if pass 'O' the value should generate report for employees from production, maintenanace & Sales...

Thanks,
Divakar

Posted: Fri Nov 25, 2011 1:09 pm
by Anuj Dhawan
Okay, so what have you tried?

Posted: Fri Nov 25, 2011 3:23 pm
by dichandr
I tried to have two sql cursor declarations and have separate sections to open, fetch & close these cursors. And based on the JCL PARM parameter, correponding section will be called. This is working. But i want to check if there is any better solution.

Thanks...

Posted: Tue Jan 10, 2012 5:31 pm
by devegouda.chage
I think what you did is the best way (According to me)....

otherwise you need to have an extra column having the flag value either "C" or "O"
&by adding this into cursor u acn query ....example
:idea:
EXEC SQL
DECLARE EMP_CSR CURSOR FOR
SELECT EMP_NO
,EMP_NAME
,EMP_AGE
FROM EMP_TABLE
WHERE (EMP_DEPT in ('PROD','MAIN','SALE') AND FLAG_COL='O')
OR (EMP_DEPT = 'COMP' AND FLAG_COL='C')

ORDER BY EMP_NO
END-EXEC.

where FLAG_COL= flag value stored either "O" for'PROD','MAIN','SALE'
or "C" for Computers


corrections are most welcome :)