|
View previous topic :: View next topic
|
| Author |
Message |
snehalpatel
New User
Joined: 13 Sep 2007
Posts: 37
Location: India
|
|
|
|
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 |
|
 |
hemanth.nandas
Active User
Joined: 18 Aug 2007
Posts: 120
Location: India
|
|
|
|
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 |
|
|
snehalpatel
New User
Joined: 13 Sep 2007
Posts: 37
Location: India
|
|
|
|
| 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 |
|
|
hemanth.nandas
Active User
Joined: 18 Aug 2007
Posts: 120
Location: India
|
|
|
|
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 |
|
|
snehalpatel
New User
Joined: 13 Sep 2007
Posts: 37
Location: India
|
|
|
|
| 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 |
|
|
hemanth.nandas
Active User
Joined: 18 Aug 2007
Posts: 120
Location: India
|
|
|
|
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 |
|
|
nuthan
Active User
Joined: 26 Sep 2005
Posts: 146
Location: Bangalore
|
|
|
|
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 |
|
|
snehalpatel
New User
Joined: 13 Sep 2007
Posts: 37
Location: India
|
|
|
|
| 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 |
|
|
snehalpatel
New User
Joined: 13 Sep 2007
Posts: 37
Location: India
|
|
|
|
| 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 |
|
|
hemanth.nandas
Active User
Joined: 18 Aug 2007
Posts: 120
Location: India
|
|
|
|
Hi Snehal,
| Quote: |
| This query worked!! |
Nice to hear.  |
|
| Back to top |
|
|
snehalpatel
New User
Joined: 13 Sep 2007
Posts: 37
Location: India
|
|
|
|
| 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 |
|
|
nuthan
Active User
Joined: 26 Sep 2005
Posts: 146
Location: Bangalore
|
|
|
|
| Yes, You are right. |
|
| Back to top |
|
|
hemanth.nandas
Active User
Joined: 18 Aug 2007
Posts: 120
Location: India
|
|
|
|
Hi Snehal,
Thanks Snahel.  |
|
| Back to top |
|
|
|
|
