Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
You don't have to know the maximum number. That would make the program fairly inflexible.
Forgive the syntax if it is wrong, I haven't coded COBOL since November.
Open the cursor for employees for a manager. Then loop through the open cursor doing one of the following depending on how you need the data:
A. Increment a table counter by 1 for each row processed then put each employee into a working storage table along with the length of the data assuming the database column is a VARCHAR(25). Then using a construct like this:
01 WS-TABLE-COUNTER PIC 9(10).
01 WS-TABLE OCCURS 0 to 1000000000
TIMES DEPENDING ON WS-TABLE-COUNTER.
05 WS-ELEMENT-LEN PIC S9(4) USAGE COMP.
05 WS-TABLE-ELEMENT PIC X(25).
01 WS-O-LEN PIC 9(8).
01 WS-OUTPUT OCCURS 0 to 10000000
TIMES DEPENDING ON WS-O-LEN.
05 WS-CHAR PIC X(1).
ADD 1 TO WS-TABLE-COUNTER
MOVE COLUMN-LEN INTO WS-ELEMENT-LEN(WS-TABLE-COUNTER)
MOVE COLUMN-DATA INTO WS-TABLE-ELEMENT(WS-TABLE-COUNTER
COMPUTE WS-O-LEN = WS-TABLE-COUNTER * LENGTH OF WS-TABLE-ELEMENT.
Loop through the table and use STRING to put the data into WS-OUTPUT.
STRING WS-TABLE-ELEMENT[1:WS-ELEMENT-LEN] INTO .....
Then do what you want with it.
B. Define a huge working storage field and just loop through the open cursor stringing the data into the large field.
Of course either of these solutions do have a maximum number of employees and will have a problem with that number is reached.
Option A gives flexibility to do other processing on the data and supports an unknown number of employees up to the maximum size of the table. In theory it will initially use less memory because you don't have a giant working storage field defined at startup.
Option B doesn't allow any additional processing on the data and supports an unknown number of employees up to the maximum size of the field. You may get a call from operations complaining about high memory usage because of the huge field that gets allocated at program initialization
Lets assume that we know that the maximum limit of employees under one manager is 3. Then this query would work:-
SELECT DISTINCT tr.manager, tr.empl
FROM (SELECT t1.manager,
t1.empl || ' '|| t2.empl|| ' '|| t3.empl empl FROM
tablet t1 LEFT OUTER JOIN
ON t1.manager =t2.manager
AND t1.empl !=t2.empl LEFT OUTER JOIN
ON t2.manager =t3.manager
AND t2.empl !=t3.empl
AND t1.empl !=t3.empl) tr
You will have to increase the number of left outer joins depending on the maximum number of employees possible. But I dont think that this is a right solution for your requirement because the query size would increase depending on the maximum number.