Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to Concat a DB2 Field into 1 field

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: How to Concat a DB2 Field into 1 field
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts READ A PACKED "NEGATIVE" FI... jdesouza CA Products 3 Tue May 02, 2017 11:43 pm
No new posts Alter &DATENS field in HEADER1 Angad DFSORT/ICETOOL 4 Mon Apr 24, 2017 11:49 am
No new posts SORT VSAM file with each field one by... maxsubrat DFSORT/ICETOOL 6 Tue Mar 14, 2017 1:07 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
No new posts outrec field outside range Danielle.Filteau SYNCSORT 10 Sat Mar 04, 2017 2:37 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us