|
View previous topic :: View next topic
|
| Author |
Message |
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
Hi Team,
I am building the Sort join card which match two input files which is of RECFM=FB and LRECL= 80 Bytes and creates the output file on Non matching records from File [F1] . This output will be used along with the SQL query with respect to condition on 'IN' Clause .
File F1 :
| Code: |
-----------------------------------
2018-01-19
2018-01-22
------------------------------------
|
File F2:-
| Code: |
-----------------------------------------------
2019-01-19 2018-01-20
2019-01-22 2018-01-22
-----------------------------------------------
|
SORT CARD:-
| Code: |
OPTION COPY
INREC OVERLAY=(25:C',')
JOINKEYS FILES=F1,FIELDS=(01,10,A)
JOINKEYS FILES=F2,FIELDS=(12,10,A)
JOIN UNPAIRED,F1,ONLY
REFORMAT FIELDS=(F1:01,10,?)
OUTFIL FNAMES=SORTOUT,INCLUDE=(11,1,CH,EQ,C'1'),NULLOFL=RC4,
REMOVECC,
HEADER1=(10:'('),
BUILD=(11:X'7D',
12:01,10,
22:X'7D',
23:25,01,80:X),
TRAILER1=(10:')')
|
Is there a way not to add ',' if the count is not > 1 record and format the output with ',' character accordingly
Thanks
Balaji K |
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2275
Location: USA
|
|
|
|
First of all, re-order your statements in the sequence they are really used by SORT utility. Otherwise it takes long time trying to understand the actual behavior of your example, and the final result. It doesn't worth to spend so much time for others to analyze any dirty code.
| Code: |
JOINKEYS ...
JOINKEYS ...
JOIN ...
REFORMAT ...
INREC OVERLAY=(25:C',')
SORT FIELDS=COPY
OUTFIL FNAMES=SORTOUT,
INCLUDE=(11,1,CH,EQ,C'1'),
NULLOFL=RC4,
REMOVECC,
HEADER1=(10:'('),
BUILD=(11:X'7D',
12:01,10,
22:X'7D',
23:25,01,80:X),
TRAILER1=(10:')')
END
|
|
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
Hi Team,
I come up with a solution to limit ',' using ICE TOOL . Here is the cntl card info
Assume that the input is of below format
| Code: |
IN1 DD *
'2018-01-01'
'2019-01-01'
|
| Code: |
TOOLIN DD*
DATASORT FROM(IN1) TO(OUT1) LAST USING(CTL1)
CTL1 CNTL DD *
INREC OVERLAY=(13:C',')
SORT FIELDS=(1,12,CH,A),EQUALS
SUM FIELDS=(NONE)
OUTFIL FNAMES=OUT1,REMOVECC,
HEADER1=(10:'('),
BUILD=(11:01,13,80:X),
TRAILER1=(10:')')
|
|
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
balaji81_k.
Or you could add a dummy value in the TRAILER1 in your original control card. REFORMAT and '?-match marker' are not needed if you are interested only in non-matching records from one of the inputs (F1 in this case).
| Code: |
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(01,10,A)
JOINKEYS FILES=F2,FIELDS=(12,10,A)
JOIN UNPAIRED,F1,ONLY
OPTION COPY
OUTFIL NULLOFL=RC4,
REMOVECC,
HEADER1=(10:'('),
BUILD=(11:C'''',1,10,C''',',80:X),
TRAILER1=(11:'''0001-01-01'')') |
|
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
