|
View previous topic :: View next topic
|
| Author |
Message |
joelacs0710
New User
.jpg)
Joined: 30 Oct 2007
Posts: 10
Location: China
|
|
|
|
Hi,
Good day.
I have a problem in manipulating the data in DB2.
Data
Input Data:
Manager Employee
JOHN JASON
JOHN HELEN
JOHN CHARLES
ERIC VIVIAN
ERIC COCO
OUTPUT:
JOHN JASON HELEN CHARLES
ERIC VIVIAN COCO
Is it possible to manipulate using the SQL statement?
I want to have an SQL statement that will have the desired output above.
Thanks. |
|
| Back to top |
|
 |
joelacs0710
New User
Joined: 30 Oct 2007
Posts: 10
Location: China
|
|
|
|
| for Each Manager the Employee data will be concatenated |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| I dont think this is possible using a Query. You may have to define a cursor and concat the Employee name for each fetch until the Manger name changes .... |
|
| Back to top |
|
|
sainathvinod
New User
Joined: 01 Apr 2008
Posts: 11
Location: Chennai
|
|
|
|
| Is there any limit for the maximum number of employees that can come under a manager ? If yes, then it is possible |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| Post process this with a cursor as suggested. Turning an unknown number of multiple rows into a single row with a single SQL query is not possible. |
|
| Back to top |
|
|
the_gautam
Active User
Joined: 05 Jun 2005
Posts: 165
Location: Bangalore
|
|
|
|
I think you have to go for a program. Its not possible by using a query.
In the program also, first you have to find out the maximum number of employees for a manager to concatenate the data. |
|
| Back to top |
|
|
stodolas
Active Member
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:
| Code: |
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.
| Code: |
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 |
|
| Back to top |
|
|
joelacs0710
New User
Joined: 30 Oct 2007
Posts: 10
Location: China
|
|
|
|
Hi sainathvinod
If we put a maximum limit, how can we do it by just using query.
Thanks. |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| Post process it. Quit trying to solve the problem the wrong way. |
|
| Back to top |
|
|
sainathvinod
New User
Joined: 01 Apr 2008
Posts: 11
Location: Chennai
|
|
|
|
Hi Joel,
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
tablet t2
ON t1.manager =t2.manager
AND t1.empl !=t2.empl LEFT OUTER JOIN
tablet t3
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. |
|
| Back to top |
|
|
joelacs0710
New User
Joined: 30 Oct 2007
Posts: 10
Location: China
|
|
|
|
Thanks Sainath.. I can use your solution in the future..
Also I would like to thank everybody for sharing your thoughts and experise..
more power to IBMMAINRAMES.COM |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| Don't forget, the hit on the database for all of those joins won't be pretty... |
|
| Back to top |
|
|
|
|
