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

Concatenation Between columns of Two Tables


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

New User


Joined: 13 Sep 2007
Posts: 37
Location: India

PostPosted: Wed Oct 17, 2007 5:03 pm
Reply with quote

Hi All

My requirement is to Concatenate Columns of two tables.

Suppose I have two Tables
REGION
Code:
REGION_NBR  REGION_NAME
100         East Region
200         Central Region
300         Virtual Region
400         West Region


BRANCH

Code:
BRANCH_NBR   BRANCH_NAME   REGION_NBR  EMPLOYEE_COUNT
108         New York          100       10
110         Boston            100       6
212         Chicago           200       5
404         San Diego         400       6
415         San Jose          400       3


My requirement is to get results as below in single column

Code:
BRANCH_NAME (REGION_NAME)
New York (East Region)
Boston (East Region)
Chicago (Central Region)
San Diego (West Region)
San Jose (West Region)



I tried below query

SELECT BRANCH_NAME||REGION_NAME
FROM REGION,
BRANCH
WHERE BRANCH.REGION_NBR = REGION.REGION_NBR;


But i am getting result as

Code:
NEW YORK 
BOSTON   
CHICAGO   
SAN JOSE 
SAN DIEGO

Its not getting Region name from 2nd Table. Can Someone Help me on this
Back to top
View user's profile Send private message
hemanth.nandas

Active User


Joined: 18 Aug 2007
Posts: 120
Location: India

PostPosted: Wed Oct 17, 2007 5:18 pm
Reply with quote

Hi Snehal Patel,

Try This,

SELECT B.BRANCH_NAME | | R.REGION_NAME AS 'BRANCH_NAME (REGION_NAME)'
FROM REGION R, BRANCH B
WHERE B.REGION_NBR = R.REGION_NBR;
Back to top
View user's profile Send private message
snehalpatel

New User


Joined: 13 Sep 2007
Posts: 37
Location: India

PostPosted: Wed Oct 17, 2007 6:21 pm
Reply with quote

hemanth.nandas wrote:
Hi Snehal Patel,

Try This,

SELECT B.BRANCH_NAME | | R.REGION_NAME AS 'BRANCH_NAME (REGION_NAME)'
FROM REGION R, BRANCH B
WHERE B.REGION_NBR = R.REGION_NBR;


Hi Hemanth

Thanks for reply. IF i use 'AS' 'BRANCH_NAME then it would only change the column name and not the actual data format. I tried query given by you but it gave me below error

Code:
SELECT B.BRANCH_NAME||R.REGION_NAME AS 'BRANCH_NAME (REGION_NAME)'    0004006
  FROM REGION R, BRANCH B                                               0005006
  WHERE B.REGION_NBR = R.REGION_NBR;                                    0006006
---------+---------+---------+---------+---------+---------+---------+---------
DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "'BRANCH_NAME (REGION_NAME)'".
         SOME SYMBOLS THAT MIGHT BE LEGAL ARE: <IDENTIFIER>                   
 
Back to top
View user's profile Send private message
hemanth.nandas

Active User


Joined: 18 Aug 2007
Posts: 120
Location: India

PostPosted: Wed Oct 17, 2007 6:30 pm
Reply with quote

Hi Snehal,

Quote:
Thanks for reply. IF i use 'AS' 'BRANCH_NAME then it would only change the column name and not the actual data format. I tried query given by you but it gave me below error


Ok, But wrongly I had mentioned quotes in 'BRANCH_NAME (REGION_NAME)'.

But REMOVE AS 'BRANCH_NAME (REGION_NAME)' OR make as BRANCH_NAME_REGION_NAME.

Let me know if you faced problem again.
Back to top
View user's profile Send private message
snehalpatel

New User


Joined: 13 Sep 2007
Posts: 37
Location: India

PostPosted: Thu Oct 18, 2007 11:05 am
Reply with quote

hemanth.nandas wrote:
Hi Snehal,

Quote:
Thanks for reply. IF i use 'AS' 'BRANCH_NAME then it would only change the column name and not the actual data format. I tried query given by you but it gave me below error


Ok, But wrongly I had mentioned quotes in 'BRANCH_NAME (REGION_NAME)'.

But REMOVE AS 'BRANCH_NAME (REGION_NAME)' OR make as BRANCH_NAME_REGION_NAME.

Let me know if you faced problem again.




Hi Hemanth
I tried the below query

Code:
  SELECT B.BRANCH_NAME||R.REGION_NAME AS BRANCH_NAME_REGION_NAME
  FROM REGION R, BRANCH B                                       
  WHERE B.REGION_NBR = R.REGION_NBR;                             
---------+---------+---------+---------+---------+---------+-----
BRANCH_NAME_REGION_NAME                                         
---------+---------+---------+---------+---------+---------+-----
                                                                 
---------+---------+---------+---------+---------+---------+-----
NEW YORK                                                         
BOSTON                                                           
CHICAGO                                                         
SAN JOSE                                                         
SAN DIEGO                                                       
DSNE610I NUMBER OF ROWS DISPLAYED IS 5                           


In such case only column heading is affected and not the actual data.
The requirement is to get the result as per my first post.
Anyways Thanks for quick reply.
Back to top
View user's profile Send private message
hemanth.nandas

Active User


Joined: 18 Aug 2007
Posts: 120
Location: India

PostPosted: Thu Oct 18, 2007 11:17 am
Reply with quote

Hi Snehal,

Sorry for Inconvenience. I can't able to check the querries here.

But you've to try out this & let me know,

Code:
SELECT CONCAT(B.BRANCH_NAME, R.REGION_NAME )
AS BRANCH_NAME_REGION_NAME
FROM REGION R, BRANCH B                                       
WHERE B.REGION_NBR = R.REGION_NBR;
Back to top
View user's profile Send private message
nuthan

Active User


Joined: 26 Sep 2005
Posts: 146
Location: Bangalore

PostPosted: Thu Oct 18, 2007 11:32 am
Reply with quote

Can you try this one and let me know

SELECT RTRIM(B.BRANCH_NAME) || '(' || RTRIM(R.REGION_NAME) || ')' AS BRANCH_NAME_REGION_NAME
FROM REGION R, BRANCH B
WHERE B.REGION_NBR = R.REGION_NBR;
Back to top
View user's profile Send private message
snehalpatel

New User


Joined: 13 Sep 2007
Posts: 37
Location: India

PostPosted: Thu Oct 18, 2007 12:01 pm
Reply with quote

hemanth.nandas wrote:
Hi Snehal,

Sorry for Inconvenience. I can't able to check the querries here.

But you've to try out this & let me know,

Code:
SELECT CONCAT(B.BRANCH_NAME, R.REGION_NAME )
AS BRANCH_NAME_REGION_NAME
FROM REGION R, BRANCH B                                       
WHERE B.REGION_NBR = R.REGION_NBR;


Thanks Hemanth

I tried below query but still got same result

Code:
    SELECT CONCAT(B.BRANCH_NAME, R.REGION_NAME )   
    AS BRANCH_NAME_REGION_NAME                     
    FROM REGION R, BRANCH B                       
    WHERE B.REGION_NBR = R.REGION_NBR;             
---------+---------+---------+---------+---------+-
BRANCH_NAME_REGION_NAME                           
---------+---------+---------+---------+---------+-
                                                   
---------+---------+---------+---------+---------+-
NEW YORK                                           
BOSTON                                             
CHICAGO                                           
SAN JOSE                                           
SAN DIEGO                                         
DSNE610I NUMBER OF ROWS DISPLAYED IS 5             


I guess the verb CONCAT and symbol || works the same way.
Back to top
View user's profile Send private message
snehalpatel

New User


Joined: 13 Sep 2007
Posts: 37
Location: India

PostPosted: Thu Oct 18, 2007 12:03 pm
Reply with quote

nuthan wrote:
Can you try this one and let me know

SELECT RTRIM(B.BRANCH_NAME) || '(' || RTRIM(R.REGION_NAME) || ')' AS BRANCH_NAME_REGION_NAME
FROM REGION R, BRANCH B
WHERE B.REGION_NBR = R.REGION_NBR;


Hi Nuthan

Thanks a Lot

This query worked!!

Code:
     SELECT                                                       
     RTRIM(B.BRANCH_NAME) || '(' || RTRIM(R.REGION_NAME) || ')'   
     AS BRANCH_NAME_REGION_NAME                                   
     FROM REGION R, BRANCH B                                     
     WHERE B.REGION_NBR = R.REGION_NBR;                           
---------+---------+---------+---------+---------+---------+------
BRANCH_NAME_REGION_NAME                                           
---------+---------+---------+---------+---------+---------+------
                                                                 
---------+---------+---------+---------+---------+---------+------
NEW YORK(EAST REGION)                                             
BOSTON(EAST REGION)                                               
CHICAGO(CENTRAL REGION)                                           
SAN JOSE(WEST REGION)                                             
SAN DIEGO(WEST REGION)                                           
DSNE610I NUMBER OF ROWS DISPLAYED IS 5                           


Thanks For quick reply.
Back to top
View user's profile Send private message
hemanth.nandas

Active User


Joined: 18 Aug 2007
Posts: 120
Location: India

PostPosted: Thu Oct 18, 2007 12:06 pm
Reply with quote

Hi Snehal,

Quote:
This query worked!!


Nice to hear. icon_lol.gif
Back to top
View user's profile Send private message
snehalpatel

New User


Joined: 13 Sep 2007
Posts: 37
Location: India

PostPosted: Thu Oct 18, 2007 12:08 pm
Reply with quote

snehalpatel wrote:
nuthan wrote:
Can you try this one and let me know

SELECT RTRIM(B.BRANCH_NAME) || '(' || RTRIM(R.REGION_NAME) || ')' AS BRANCH_NAME_REGION_NAME
FROM REGION R, BRANCH B
WHERE B.REGION_NBR = R.REGION_NBR;


Hi Nuthan

Thanks a Lot

This query worked!!

Code:
     SELECT                                                       
     RTRIM(B.BRANCH_NAME) || '(' || RTRIM(R.REGION_NAME) || ')'   
     AS BRANCH_NAME_REGION_NAME                                   
     FROM REGION R, BRANCH B                                     
     WHERE B.REGION_NBR = R.REGION_NBR;                           
---------+---------+---------+---------+---------+---------+------
BRANCH_NAME_REGION_NAME                                           
---------+---------+---------+---------+---------+---------+------
                                                                 
---------+---------+---------+---------+---------+---------+------
NEW YORK(EAST REGION)                                             
BOSTON(EAST REGION)                                               
CHICAGO(CENTRAL REGION)                                           
SAN JOSE(WEST REGION)                                             
SAN DIEGO(WEST REGION)                                           
DSNE610I NUMBER OF ROWS DISPLAYED IS 5                           


Thanks For quick reply.



Hi Hemanth

Fyi.. The above query works as per requirement.
The Rtrim , '(' are required for formatted o/p and to remove blank spaces i guess.

Thanks all for quick reply.
Back to top
View user's profile Send private message
nuthan

Active User


Joined: 26 Sep 2005
Posts: 146
Location: Bangalore

PostPosted: Thu Oct 18, 2007 12:13 pm
Reply with quote

Yes, You are right.
Back to top
View user's profile Send private message
hemanth.nandas

Active User


Joined: 18 Aug 2007
Posts: 120
Location: India

PostPosted: Thu Oct 18, 2007 12:16 pm
Reply with quote

Hi Snehal,

Thanks Snahel. icon_smile.gif
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
No new posts Remote Unload of CLOB Columns DB2 6
No new posts Increase the number of columns in the... IBM Tools 3
No new posts DB2 Views with Multiple SQL & Col... DB2 8
No new posts Need to fetch data from so many DB2 t... DB2 9
No new posts SORT - To repeat a string in same col... SYNCSORT 3
Search our Forums:

Back to Top