IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to Concat a DB2 Field into 1 field


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
joelacs0710

New User


Joined: 30 Oct 2007
Posts: 10
Location: China

PostPosted: Thu May 22, 2008 1:47 pm
Reply with quote

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
View user's profile Send private message
joelacs0710

New User


Joined: 30 Oct 2007
Posts: 10
Location: China

PostPosted: Thu May 22, 2008 3:59 pm
Reply with quote

for Each Manager the Employee data will be concatenated
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu May 22, 2008 4:36 pm
Reply with quote

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
View user's profile Send private message
sainathvinod

New User


Joined: 01 Apr 2008
Posts: 11
Location: Chennai

PostPosted: Tue May 27, 2008 2:49 pm
Reply with quote

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
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue May 27, 2008 9:40 pm
Reply with quote

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
View user's profile Send private message
the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Wed May 28, 2008 9:23 am
Reply with quote

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
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed May 28, 2008 7:30 pm
Reply with quote

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
View user's profile Send private message
joelacs0710

New User


Joined: 30 Oct 2007
Posts: 10
Location: China

PostPosted: Mon Jun 02, 2008 2:26 pm
Reply with quote

Hi sainathvinod

If we put a maximum limit, how can we do it by just using query.

Thanks.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Jun 02, 2008 6:36 pm
Reply with quote

Post process it. Quit trying to solve the problem the wrong way.
Back to top
View user's profile Send private message
sainathvinod

New User


Joined: 01 Apr 2008
Posts: 11
Location: Chennai

PostPosted: Tue Jun 03, 2008 12:32 pm
Reply with quote

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
View user's profile Send private message
joelacs0710

New User


Joined: 30 Oct 2007
Posts: 10
Location: China

PostPosted: Fri Jun 27, 2008 1:55 pm
Reply with quote

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
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Sun Jun 29, 2008 4:45 am
Reply with quote

Don't forget, the hit on the database for all of those joins won't be pretty...
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
This topic is locked: you cannot edit posts or make replies. Construct new record using specific f... DFSORT/ICETOOL 6
No new posts Count the number of characters in a f... CA Products 1
No new posts Find the occurrence of Key Field (Par... DFSORT/ICETOOL 6
No new posts Expand ISPF field up to a limit - is ... TSO/ISPF 9
No new posts Need to add field to copybook, proble... COBOL Programming 14
Search our Forums:

Back to Top