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: 116

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: 397
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: 1788
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: 116

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: 116

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: 1788
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: 116

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: 1788
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 Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
This topic is locked: you cannot edit posts or make replies. Selecting two copybooks of different ... Vignesh Sid COBOL Programming 8 Tue Sep 05, 2017 7:28 pm
This topic is locked: you cannot edit posts or make replies. Limit access to certain RACF group cvnlynn CLIST & REXX 5 Wed Aug 23, 2017 2:28 am
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am

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