|
View previous topic :: View next topic
|
| Author |
Message |
arien
New User
Joined: 02 Nov 2006
Posts: 43
Location: London
|
|
|
|
Hi,
I have a delimited file as my input and I need to remove trailing spaces from the fields in this file. Eg for input and required output is given below :
Input:
| Code: |
| 36750 | I am a happy child | Thanks very much |23 |
Required Output:
| Code: |
| 36750| I am a happy child| Thanks very much|23 |
Pls note that the preceeding and middle spaces are intact. |
|
| Back to top |
|
 |
superk
Global Moderator
Joined: 26 Apr 2004
Posts: 4652
Location: Raleigh, NC, USA
|
|
|
|
| Like your Avatar! - Kevin |
|
| Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
Arien,
Is each delimiter (|) always in the same position in each record, or can they be in different positions in different records. Please show an example with more than one record for input and expected output so we can get a better idea of what they can look like.
Also, give the RECFM and LRECL of the input file and output file, and the starting position, length and format of all relevant fields. |
|
| Back to top |
|
|
arien
New User
Joined: 02 Nov 2006
Posts: 43
Location: London
|
|
|
|
Hi Frank:
As per the example given here RECFM=FB, LRECL=80
Delimiters occur at specific positions in input file: 7, 48 and 72
I have actually unloaded a table here and delimited fields with a pipe in this output file. The field definition of the table is as below:
char(6)
char(40)
char(23)
char(8)
The output file example records :
| Code: |
36750 | I am a happy child | Thanks very much |23
12189 |This is another row | Just an example| 78
10000 |Hi |Another example | 42 |
and expected output:
| Code: |
36750| I am a happy child| Thanks very much|23
12189|This is another row| Just an example| 78
10000|Hi|Another example| 42 |
Pls note that the preceeding and middle spaces are intact. |
|
| Back to top |
|
|
arien
New User
Joined: 02 Nov 2006
Posts: 43
Location: London
|
|
|
|
@ Kevin... thank you  |
|
| Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
|
|
arien,
Couldn't think of an easier way to do this, so here is a brute force method to squeeze out the trailing blanks. The max value you need to squeeze is 40 blanks.
| Code: |
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=Your input FB 80 byte input file,DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=COPY
OUTREC FINDREP=(IN=(C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |',
C' |'),OUT=C'|')
//* |
|
|
| Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
arien,
Is this related to ibmmainframes.com/viewtopic.php?t=50698
Just curious why couldn't you handle this in DB2 unload itself?
| Code: |
SELECT CONCAT(RTRIM(COL1),CHAR('|')),
CONCAT(RTRIM(COL2),CHAR('|')),
CONCAT(RTRIM(COL3),CHAR('|')),
CONCAT(RTRIM(COL4),CHAR('|'))
FROM TBL |
Thanks, |
|
| Back to top |
|
|
arien
New User
Joined: 02 Nov 2006
Posts: 43
Location: London
|
|
|
|
Hi sqlcode1,
Yes, this is the next step of my requirement. I unloaded a table into a delimited file using DSNUPROC and then I need to rtrim the fields..
I tried your example.. it did wonders. Just that I had to use DSNUTILA instead of DSNUPROC in my JCL for executing the above SELECT query.
Only thing is if you could help me with left-justifying the columns in the output. The starting position of each column in the output is still the same, and I don't want to miss the starting spaces in the columns and hence can't use LTRIM.
My DSNUPROC output file:
| Code: |
36750 | I am a happy child | Thanks very much |23
12189 |This is another row | Just an example| 78
10000 |Hi |Another example | 42 |
Current output using RTRIM in DSNUTILA:
| Code: |
36750| I am a happy child| Thanks very much| 23
12189| This is another row| Just an example| 78
10000| Hi| Another example| 42 |
Expected output :
| Code: |
36750| I am a happy child| Thanks very much|23
12189|This is another row| Just an example| 78
10000|Hi|Another example| 42 |
|
|
| Back to top |
|
|
arien
New User
Joined: 02 Nov 2006
Posts: 43
Location: London
|
|
|
|
@Skolusu : My Original file contains uto 200 blanks before the | delimiter.. it would be too crude an approach to do so...  thanks anyway |
|
| Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
arien,
You are probably in the wrong section of forum.
See if this works for you.
| Code: |
SELECT CAST (RTRIM(COL1) || '|'
|| RTRIM(COL2) || '|'
|| RTRIM(COL3) || '|'
|| RTRIM(COL4) || AS CHAR (80))
FROM TBL |
Thanks, |
|
| Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
|
|
arien,
There are ways to handle this is in SQL itself.
SQLCODE1,
Are you aware that RTRIM function actually creates a VARCHAR output string? |
|
| Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
Skolusu,
Yes, RTRIM creates VARCHAR and that is the reason I am casting the entire output with CHAR(80).
Thanks, |
|
| Back to top |
|
|
|
|
