By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,916 Members | 1,286 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,916 IT Pros & Developers. It's quick & easy.

RTRIM is not truncating trailing spaces at the end of the line

P: n/a
Hi,
I am using the following sql statement

SELECT rtrim(rtrim(coalesce(substr(char(v.creationdate),1 ,4) || '-' ||
substr(char(v.creationdate),6,2) || '-' ||
substr(char(v.creationdate),9,2) || ' ' ||
substr(char(v.creationdate),12,2) || ':' ||
substr(char(v.creationdate),15,2) || ':' ||
substr(char(v.creationdate),18,2), '')) ||'~'
|| rtrim(coalesce(substr(char(v.lastmodifieddate),1,4 ) || '-' ||
substr(char(v.lastmodifieddate),6,2) || '-' ||
substr(char(v.lastmodifieddate),9,2) || ' ' ||
substr(char(v.lastmodifieddate),12,2) || ':' ||
substr(char(v.lastmodifieddate),15,2) || ':' ||
substr(char(v.lastmodifieddate),18,2), '')) ||'~'
|| rtrim(coalesce(substr(char(v.escalationdate),1,4) || '-' ||
substr(char(v.escalationdate),6,2) || '-' ||
substr(char(v.escalationdate),9,2) || ' ' ||
substr(char(v.escalationdate),12,2) || ':' ||
substr(char(v.escalationdate),15,2) || ':' ||
substr(char(v.escalationdate),18,2), '')) ||'~'
|| rtrim(coalesce(v.status, ''))||'~'
|| rtrim(coalesce(v.referralnumber, ''))||'~'
|| rtrim(coalesce(v.casenumber, '')) ||'~'
|| rtrim(coalesce(v.CONTRACTNUMBER , '')) ||'~'
|| rtrim(coalesce(v.CONTRACTSUFFIX, '')) ||'~'
|| rtrim(coalesce(v.CONTRACTGROUPNUMBER, '')) ||'~'
|| rtrim(coalesce(v.destinationgroup, '')) ||'~'
|| rtrim(coalesce(v.originatingcsclastname, '')) ||'~'
|| rtrim(coalesce(v.originatingcscfirstname, '')) ||'~'
|| rtrim(coalesce(cs.lname, '')) ||'~'
|| rtrim(coalesce(cs.fname, '')) ||'~'
|| rtrim(coalesce(c.lname, '')) ||'~'
|| rtrim(coalesce(c.fname, '')) ||'~'
|| rtrim(coalesce(v.TYPE, '')) ||'~'
|| rtrim(coalesce(v.followuprequired, '')) ||'~'
|| rtrim(coalesce(v.callbackrequired, '')) ||'~'
|| rtrim(coalesce(v.PRIORITY, '')) ||'~'
|| rtrim(char(days(bd.STARTDATETIME ) - days(v.escalationdate))) ||'~'
|| rtrim(coalesce(v.catagory, '')) ||'~'
|| rtrim(coalesce(v.callgroup, '')) ||'~'
|| rtrim(coalesce(v.CONTACTLASTNAME, '')) ||'~'
|| rtrim(coalesce(v.CONTACTFIRSTNAME, '')) ||'~'
|| rtrim(coalesce(v.DELIVERYADDRLINE, '')) ||'~'
|| rtrim(coalesce(v.CITY, '')) ||'~'
|| rtrim(coalesce(v.STATE, '')) ||'~'
|| rtrim(coalesce(v.ATTN, '')) ||'~'
|| 'TNG')
FROM vcity v, rajusers c, rajusers cs, batches bd
WHERE v.USERID = c.userid
AND v.usersupervisorid = cs.userid
AND v.status not in ('Disposed', 'Finalized')
AND v.callgroupcd = '015'
AND bd.ID = 1
AND (v.creationdate > bd.startdatetime - 7 days
OR v.lastmodifieddate > bd.startdatetime - 7 days)
ORDER BY v.creationdate
;
The output i am getting is

2005-06-01 16:11:53~2005-06-01 16:11:53~2005-06-08
00:00:00~Sent~00000-01314-038~00000-04418-043~969560126028060~~96956~GUL
Aurora Accounting~Vakkalagadda~Madhu
Babu~Srinivasan~Sridhar~Vakkalagadda~Madhu Babu~Status Of
Loan/Withdrawal/Surrender~N~N~1~0~Accounting~GUL~BROWN
III~BENJAMIN~8205 CITY LIGHTS DR~ALISO VIEJO~CA~~TNG


2005-06-01 16:44:01~2005-06-01 16:44:01~2005-06-08
00:00:00~Sent~00000-01318-000~00000-04431-000~969560126028060~~96956~GUL
Aurora Accounting~Vakkalagadda~Madhu
Babu~Srinivasan~Sridhar~Vakkalagadda~Madhu Babu~Apply Suspense Money -
Regular~N~N~0~0~Accounting~GUL~BROWN III~BENJAMIN~8205 CITY LIGHTS
DR~ALISO VIEJO~CA~~TNG


2005-06-01 16:48:02~2005-06-01 16:48:02~2005-06-08
00:00:00~Sent~00000-01318-002~00000-04431-000~969560126028060~~96956~GUL
Escalation~Vakkalagadda~Madhu
Babu~Srinivasan~Sridhar~Vakkalagadda~Madhu Babu~Apply Suspense Money -
Resource~N~N~0~0~Accounting~GUL~BROWN III~BENJAMIN~8205 CITY LIGHTS
DR~ALISO VIEJO~CA~~TNG
There is a lot of space between each row of output. How can i eliminate
those spaces? How can i get the result sets one after another without
any spaces in between two lines of output?

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
rajdb2 wrote:
Hi,
I am using the following sql statement

SELECT rtrim(rtrim(coalesce(substr(char(v.creationdate),1 ,4) || '-' ||
substr(char(v.creationdate),6,2) || '-' ||
substr(char(v.creationdate),9,2) || ' ' ||
substr(char(v.creationdate),12,2) || ':' ||
substr(char(v.creationdate),15,2) || ':' ||
substr(char(v.creationdate),18,2), '')) ||'~'
|| rtrim(coalesce(substr(char(v.lastmodifieddate),1,4 ) || '-' ||
substr(char(v.lastmodifieddate),6,2) || '-' ||
substr(char(v.lastmodifieddate),9,2) || ' ' ||
substr(char(v.lastmodifieddate),12,2) || ':' ||
substr(char(v.lastmodifieddate),15,2) || ':' ||
substr(char(v.lastmodifieddate),18,2), '')) ||'~'
|| rtrim(coalesce(substr(char(v.escalationdate),1,4) || '-' ||
substr(char(v.escalationdate),6,2) || '-' ||
substr(char(v.escalationdate),9,2) || ' ' ||
substr(char(v.escalationdate),12,2) || ':' ||
substr(char(v.escalationdate),15,2) || ':' ||
substr(char(v.escalationdate),18,2), '')) ||'~'
|| rtrim(coalesce(v.status, ''))||'~'
|| rtrim(coalesce(v.referralnumber, ''))||'~'
|| rtrim(coalesce(v.casenumber, '')) ||'~'
|| rtrim(coalesce(v.CONTRACTNUMBER , '')) ||'~'
|| rtrim(coalesce(v.CONTRACTSUFFIX, '')) ||'~'
|| rtrim(coalesce(v.CONTRACTGROUPNUMBER, '')) ||'~'
|| rtrim(coalesce(v.destinationgroup, '')) ||'~'
|| rtrim(coalesce(v.originatingcsclastname, '')) ||'~'
|| rtrim(coalesce(v.originatingcscfirstname, '')) ||'~'
|| rtrim(coalesce(cs.lname, '')) ||'~'
|| rtrim(coalesce(cs.fname, '')) ||'~'
|| rtrim(coalesce(c.lname, '')) ||'~'
|| rtrim(coalesce(c.fname, '')) ||'~'
|| rtrim(coalesce(v.TYPE, '')) ||'~'
|| rtrim(coalesce(v.followuprequired, '')) ||'~'
|| rtrim(coalesce(v.callbackrequired, '')) ||'~'
|| rtrim(coalesce(v.PRIORITY, '')) ||'~'
|| rtrim(char(days(bd.STARTDATETIME ) - days(v.escalationdate))) ||'~'
|| rtrim(coalesce(v.catagory, '')) ||'~'
|| rtrim(coalesce(v.callgroup, '')) ||'~'
|| rtrim(coalesce(v.CONTACTLASTNAME, '')) ||'~'
|| rtrim(coalesce(v.CONTACTFIRSTNAME, '')) ||'~'
|| rtrim(coalesce(v.DELIVERYADDRLINE, '')) ||'~'
|| rtrim(coalesce(v.CITY, '')) ||'~'
|| rtrim(coalesce(v.STATE, '')) ||'~'
|| rtrim(coalesce(v.ATTN, '')) ||'~'
|| 'TNG')
FROM vcity v, rajusers c, rajusers cs, batches bd
WHERE v.USERID = c.userid
AND v.usersupervisorid = cs.userid
AND v.status not in ('Disposed', 'Finalized')
AND v.callgroupcd = '015'
AND bd.ID = 1
AND (v.creationdate > bd.startdatetime - 7 days
OR v.lastmodifieddate > bd.startdatetime - 7 days)
ORDER BY v.creationdate
;
The output i am getting is

2005-06-01 16:11:53~2005-06-01 16:11:53~2005-06-08
00:00:00~Sent~00000-01314-038~00000-04418-043~969560126028060~~96956~GUL
Aurora Accounting~Vakkalagadda~Madhu
Babu~Srinivasan~Sridhar~Vakkalagadda~Madhu Babu~Status Of
Loan/Withdrawal/Surrender~N~N~1~0~Accounting~GUL~BROWN
III~BENJAMIN~8205 CITY LIGHTS DR~ALISO VIEJO~CA~~TNG


2005-06-01 16:44:01~2005-06-01 16:44:01~2005-06-08
00:00:00~Sent~00000-01318-000~00000-04431-000~969560126028060~~96956~GUL
Aurora Accounting~Vakkalagadda~Madhu
Babu~Srinivasan~Sridhar~Vakkalagadda~Madhu Babu~Apply Suspense Money -
Regular~N~N~0~0~Accounting~GUL~BROWN III~BENJAMIN~8205 CITY LIGHTS
DR~ALISO VIEJO~CA~~TNG


2005-06-01 16:48:02~2005-06-01 16:48:02~2005-06-08
00:00:00~Sent~00000-01318-002~00000-04431-000~969560126028060~~96956~GUL
Escalation~Vakkalagadda~Madhu
Babu~Srinivasan~Sridhar~Vakkalagadda~Madhu Babu~Apply Suspense Money -
Resource~N~N~0~0~Accounting~GUL~BROWN III~BENJAMIN~8205 CITY LIGHTS
DR~ALISO VIEJO~CA~~TNG
There is a lot of space between each row of output. How can i eliminate
those spaces? How can i get the result sets one after another without
any spaces in between two lines of output?

I think what you see is coming from the formatting of the client (CLP?)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

P: n/a
Followings are my guess. I'm sorry, if I miss the point.

Although actual length of result of RTRIM is defined length of the
column minus number of trailing blanks, the length of returned data
type is same as the argument column.
And Output of CLP or Command Editor keep the length of data type of the
column.
(not actual length, even for VARCHAR)
So, in your SQL part
"|| rtrim(coalesce(v.status, ''))||'~'
|| rtrim(coalesce(v.referralnumbe*r, ''))||'~'
..
..
..
|| rtrim(coalesce(v.ATTN, '')) ||'~' "
length of data type is sum of each column's defined length of data
type.
You concatenate all expression in one column including the part I
showed. The length of data type of a result column is sum of them.
I think to make less space between each row of output, estimate the
maximum length of sum of RTRIMed length(for example, say it n), then
specify it to 3rd argument of SUBSTR. Like this:
|| SUBSTR(
rtrim(coalesce(v.status, ''))||'~'
|| rtrim(coalesce(v.referralnumbe*r, ''))||'~'
..
..
..
|| rtrim(coalesce(v.ATTN, '')), 1, n) ||'~'

Nov 12 '05 #3

P: n/a
Hi Tonkuma,
Thank you very much. Your guesses habe been correct. when i
did the way u told me and used 100 as n, i got an output, where there
were no spaces between each row. But, pardon me, i was unable to
understand what you said about the problem, i just implemented your
solution. Can you please explain in more detail abut the problem and
your solution? That would be very helpful for me.

Thanks once again.


Tonkuma wrote:
Followings are my guess. I'm sorry, if I miss the point.

Although actual length of result of RTRIM is defined length of the
column minus number of trailing blanks, the length of returned data
type is same as the argument column.
And Output of CLP or Command Editor keep the length of data type of the
column.
(not actual length, even for VARCHAR)
So, in your SQL part
"|| rtrim(coalesce(v.status, ''))||'~'
|| rtrim(coalesce(v.referralnumbe*r, ''))||'~'
.
.
.
|| rtrim(coalesce(v.ATTN, '')) ||'~' "
length of data type is sum of each column's defined length of data
type.
You concatenate all expression in one column including the part I
showed. The length of data type of a result column is sum of them.
I think to make less space between each row of output, estimate the
maximum length of sum of RTRIMed length(for example, say it n), then
specify it to 3rd argument of SUBSTR. Like this:
|| SUBSTR(
rtrim(coalesce(v.status, ''))||'~'
|| rtrim(coalesce(v.referralnumbe*r, ''))||'~'
.
.
.
|| rtrim(coalesce(v.ATTN, '')), 1, n) ||'~'


Nov 12 '05 #4

P: n/a
I hope the following examples by Command Editor would give you some
suggestions.

------------------------- Commands Entered -------------------------
CREATE TABLE xxxx
(col1 INTEGER NOT NULL
,colv VARCHAR(20) NOT NULL
);
--------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------- Commands Entered -------------------------
INSERT INTO xxxx
VALUES (1, 'ABC ');
--------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------- Commands Entered -------------------------
SELECT LENGTH(colv), LENGTH(RTRIM(colv)), LENGTH(RTRIM(colv) || '****')
, RTRIM(colv), RTRIM(colv) || '****', SUBSTR(RTRIM(colv) ||
'****',1,10)
FROM xxxx;
--------------------------------------------------------------------

1 2 3 4 5
6
----------- ----------- ----------- --------------------
------------------------ ----------
5 3 7 ABC ABC****
ABC****

1 record(s) selected.
While LENGTH(RTRIM(colv))(displayed in a result column 2) is 3. A width
of displayed result column 4(RTRIM(colv)) is 20 which is same as length
of colv defined in CREATE TABLE.
A width of displayed column 5(RTRIM(colv) || '****') is 24 which is 20
+ 4. "+ 4" is for || '****'.
By using SUBSTR, a width of displayed column can be adjusted. Please
see a result column 6(adjusted to 10).

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.