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
 
Can we limit length in concatenation in SQL select query

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

Active User


Joined: 29 Jun 2005
Posts: 118

PostPosted: Tue Aug 22, 2017 2:50 am    Post subject: Can we limit length in concatenation in SQL select query
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: 408
Location: USA

PostPosted: Tue Aug 22, 2017 3:07 am    Post subject: Reply to: Can we limit length in concatenation in SQL select query
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

Senior Member


Joined: 21 Sep 2010
Posts: 1817
Location: NY,USA

PostPosted: Tue Aug 22, 2017 3:33 am    Post subject:
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: 118

PostPosted: Tue Aug 22, 2017 4:06 am    Post subject: Re: Reply to: Can we limit length in concatenation in SQL select query
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: 118

PostPosted: Tue Aug 22, 2017 4:09 am    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1817
Location: NY,USA

PostPosted: Tue Aug 22, 2017 8:54 pm    Post subject:
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: 118

PostPosted: Tue Aug 22, 2017 11:02 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1817
Location: NY,USA

PostPosted: Tue Aug 22, 2017 11:29 pm    Post subject:
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    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 Split files upto certain limit based ... bubbu75 DFSORT/ICETOOL 5 Tue Nov 14, 2017 11:11 pm
No new posts Query on IEFBR14 with GDG Ashishpanpaliya JCL & VSAM 3 Tue Nov 07, 2017 8:34 pm
No new posts Query on XMITIP abdulrafi All Other Mainframe Topics 1 Wed Oct 25, 2017 6:54 pm
No new posts sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm

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