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
 
Concatenate two fields including trailing spaces.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
AjmalMohammed

New User


Joined: 02 Jun 2010
Posts: 10
Location: My Cubicle, Hyderabad, India

PostPosted: Thu Jun 17, 2010 2:11 pm    Post subject: Concatenate two fields including trailing spaces.
Reply with quote

Hi,

How can i concatenate two strings such that trailing spaces(if any) are also included in the end result ?

Code:

Exec SQL
Select Field1 || Field2 into :Host-Var From TableName where <condition>
from TableName
End-Exec.


This query ignores trailing spaces.

Assume Field1 is defined as char(20) holding value 'abc' and Field2 is defined as char(20) with value 'def'. I want host-var to have value of field1 plus 17 blanks then field2 plus 17 blanks.

I think the Substr function might work. I cant experiment as i dont have access to mainframes right now.

Thank you,
Ajmal.
Back to top
View user's profile Send private message

anshul_gugnani

New User


Joined: 02 Nov 2009
Posts: 73
Location: Mumbai

PostPosted: Thu Jun 17, 2010 3:10 pm    Post subject:
Reply with quote

Hello Ajmal,

You can cast each column to fixed length column and then concatenate them -
Trying using somthing like -

Code:

SELECT CAST (Field1 AS CHAR(20)) ||
            CAST (Field2 AS CHAR(20))     
FROM table                 
WHERE ....         


Hope this helps.....
Back to top
View user's profile Send private message
AjmalMohammed

New User


Joined: 02 Jun 2010
Posts: 10
Location: My Cubicle, Hyderabad, India

PostPosted: Thu Jun 17, 2010 3:53 pm    Post subject:
Reply with quote

Will this work?

Code:

SELECT SUBSTR(FIELD1,1,20) || SUBSTR (FIELD2,1,20) FROM TABLE
WHERE <CONDITION>
Back to top
View user's profile Send private message
anshul_gugnani

New User


Joined: 02 Nov 2009
Posts: 73
Location: Mumbai

PostPosted: Thu Jun 17, 2010 3:59 pm    Post subject:
Reply with quote

Hello,

Yes this will also work.Will give the same result as above example of CAST.
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 SORT BUT RETAIN HIGHEST VALUE ON NON-... leondan22 DFSORT/ICETOOL 2 Thu Dec 14, 2017 8:13 pm
No new posts Inefficient BUILD - VB records paddin... Daniel Prosser SYNCSORT 6 Thu Dec 14, 2017 3:52 pm
No new posts Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 2 Wed Dec 06, 2017 1:50 am
No new posts Subtract the ZD fields to provide neg... Balaryan DFSORT/ICETOOL 4 Wed Oct 11, 2017 10:51 pm
No new posts Addition of two records on 2 fields amar143 SYNCSORT 8 Tue Oct 03, 2017 11:14 am

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