Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Concatenation Between columns of Two Tables

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Concatenation Between columns of Two Tables
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    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 Can EDCICONV convert EBCDIC to ASCII... natt.sut IBM Tools 3 Fri Nov 17, 2017 8:46 pm
No new posts Copy selected columns from 2 input fi... Poha Eater DFSORT/ICETOOL 3 Thu Nov 02, 2017 3:43 pm
No new posts Selecting a row from multiple tables! Vignesh Sid DB2 2 Thu Oct 26, 2017 6:09 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us