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

Can we limit length in concatenation in SQL select query


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

Active User


Joined: 29 Jun 2005
Posts: 141

PostPosted: Tue Aug 22, 2017 2:50 am
Reply with quote

Hi Team,
I am Selecting 2 columns and concatenate the same in my result .
Three columns are
1) Company_code 4 bytes(char)
2) BUSINESS_CODE (20) bytes char
3) Resultant column - concat of company_code ||'.'|| business_code

business_code will have 'XYZ' , 'ABC'.
company_code will have 'YYY', 'EEE' like .

My result column should limit to 10 bytes and also i should not get any space in concatenation result .

In below SQL Query ,

Code:

Select COMPANY_CODE,BUSINESS_CODE, COMPANY_CODE||'.'||BUSINESS_CODE
FROM TEST_TABLE
WITH UR

Result shows

YYY, XYZ ,  YYY .XYZ

but i need
YYY,XYZ,YYY.XYZ no space before '.'


Trim will work on this? and also would like to limit length of the result to 10 bytes . How we can achieve?

Thanks
Balaji K
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Tue Aug 22, 2017 3:07 am
Reply with quote

Why don't you use TRIM and SUBSTR?

Code:
Select COMPANY_CODE,BUSINESS_CODE, SUBSTR(TRIM(COMPANY_CODE)||'.'||BUSINESS_CODE,1,10)
 FROM TEST_TABLE
 WITH UR


.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2673
Location: NYC,USA

PostPosted: Tue Aug 22, 2017 3:33 am
Reply with quote

It doesn't make sense, why you need a truncated data? That's totally incorrect.
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 141

PostPosted: Tue Aug 22, 2017 4:06 am
Reply with quote

RahulG31 wrote:
Why don't you use TRIM and SUBSTR?

Code:
Select COMPANY_CODE,BUSINESS_CODE, SUBSTR(TRIM(COMPANY_CODE)||'.'||BUSINESS_CODE,1,10)
 FROM TEST_TABLE
 WITH UR


.


Sure Rahul, Thanks for the help.
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 141

PostPosted: Tue Aug 22, 2017 4:09 am
Reply with quote

Rohit Umarjikar wrote:
It doesn't make sense, why you need a truncated data? That's totally incorrect.


Rohit,

So far we have data in prod doesn't exist more than 3 chars but still variable is defined more than of it . I need to populate the combination of this two which is passed to downstream applications and they have some restrictions to receive the same with some fixed byte length at present . Logic will change over the time for sure.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2673
Location: NYC,USA

PostPosted: Tue Aug 22, 2017 8:54 pm
Reply with quote

Okay, Why was it so difficult for you to know STRIP/TRIP/REPLACE/SUBSTRING functions by doing little research?
btw , You will need to TRIM both the columns.
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 141

PostPosted: Tue Aug 22, 2017 11:02 pm
Reply with quote

Rohit Umarjikar wrote:
Okay, Why was it so difficult for you to know STRIP/TRIP/REPLACE/SUBSTRING functions by doing little research?
btw , You will need to TRIM both the columns.


Yes Rohit ,it should have been done from my end on this post . First thing in my mind was TRIM . I used to post my queries always with my research or finding before seeking assistance or help . But this post i didn't do that and i apologize for the same .
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2673
Location: NYC,USA

PostPosted: Tue Aug 22, 2017 11:29 pm
Reply with quote

All right, hope all this solutions worked for you.
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 COBOL Table data to a flat file with ... COBOL Programming 5
No new posts DB2 SQL query to read and update data... DB2 12
No new posts Getting OUTREC - SHORT REC error for ... DFSORT/ICETOOL 12
No new posts Cobol-db2 : SQL query is running for ... DB2 4
No new posts Reading subsequent rows in a query. DB2 12
Search our Forums:

Back to Top