Query/400 - drop trailing blanks & right-justify
Last Post 19 Feb 2007 01:22 PM by Wyatt Repavich. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
Evan Weil
New Member
New Member
Posts:23

--
30 Jan 2007 03:13 PM
Hi All, I have a need to select records based on an MQ transaction time field (8 char). Unfortunately the time is written from left to right. Is there a way in Query/400 to strip off the trailing blanks and right-justify the result. I want to avoid using SQL/Query Manager, etc. if possible. Thanks.
G. Peter Posthumus
Advanced Member
Advanced Member
Posts:250

--
30 Jan 2007 05:14 PM Accepted Answer
I have a need to select records based on an MQ transaction time field (8 char). Unfortunately the time is written from left to right. Is there a way in Query/400 to strip off the trailing blanks and right-justify the result. I want to avoid using SQL/Query Manager, etc. if possible.
I'm going to assume, since don't have MQ, that you have 6 digits in the 8 char field. You want to go from '123456bb' to 'bb123456' ? [ bb = 2 blanks ] 1. Define Result field with length of 8 -- Say "RIGHTADJ" 2. In Expression: 'bb' || substr( LEFTADJ , 1 , 6 ) [ bb = 2 blanks ] Now in the Select just compare RIGHTADJ ( which will have the right adjusted values ) hth, G. Peter David
Evan Weil
New Member
New Member
Posts:23

--
02 Feb 2007 07:15 PM Accepted Answer
Thanks Peter, that did the trick!!
Evan Weil
New Member
New Member
Posts:23

--
08 Feb 2007 07:34 PM Accepted Answer
I'm going to assume, since don't have MQ, that you have 6 digits in the 8 char field. You want to go from '123456bb' to 'bb123456' ? [ bb = 2 blanks ] 1. Define Result field with length of 8 -- Say "RIGHTADJ" 2. In Expression: 'bb' || substr( LEFTADJ , 1 , 6 ) [ bb = 2 blanks ] Now in the Select just compare RIGHTADJ ( which will have the right adjusted values ) hth, G. Peter David -------------------- Unfortunately the number of blanks varies. Do you have a solution for this scenario using only Query/400?
Lynette Duffy
New Member
New Member
Posts:49

--
13 Feb 2007 03:06 PM Accepted Answer
Try this:
Select Space( MyLen - Length( Trim( MQ_Time ))) ConCat Trim( MQ_Time )
From MQ_File
Where "MyLen" is the length of the target string.
Jim Suiter
Veteran Member
Veteran Member
Posts:1568
Avatar

--
14 Feb 2007 08:30 PM Accepted Answer
Try this:
Select Space( MyLen - Length( Trim( MQ_Time ))) ConCat Trim( MQ_Time )
From MQ_File
Where "MyLen" is the length of the target string.
That don't look like no Query400 to me.
Wyatt Repavich
New Member
New Member
Posts:21

--
19 Feb 2007 01:22 PM Accepted Answer
Could you please show me an example of some of the data in this field? Try to include enough to show the variations. Also, what is the source of the data? Is this coming from the System i or from an outside source?
You are not authorized to post a reply.

Acceptable Use Policy