View previous topic :: View next topic
Author
Message
siva102 New User Joined: 28 Nov 2007Posts: 63 Location: Chennai
Hi all,
I just completed one task with u guys help.
I just faced once more prob while processing my tsak.
That is i am having a file of LRECL = 400 where field from 1 to 99 and 122 to 278 are same. The rest 20 bits are having start date and end date.
I need a sort card such that it will combine the duplicate rows into one row with the earliest start date and the latest end date. For ex.:--
input
(1-99);01/01/2006;01/07/2007;(122- 278)
(1-99);01/09/2007;02/05/2008;(122- 278)
(1-99);02/15/2008;09/05/2008;(122- 278)
OUTPUT
(1-99);01/01/2006;09/05/2008;(122- 278)
I suppose it can be done by using include statements.
Could u guys please tell hw to start with this typr of requiremnt.
Thanks in advance.
Back to top
Arun Raj Moderator Joined: 17 Oct 2006Posts: 2481 Location: @my desk
Hi,
What does the key field start? Is it from 1-99 and 122-178?
Thanks,
Arun
Back to top
siva102 New User Joined: 28 Nov 2007Posts: 63 Location: Chennai
Hi arun,
The Start Date field starts with position 100 to 109 and the END DATE from 111 to 120.
Except these things all the other stuffs are same for the duplicate records i.e from position 1 - 99 and from 122 - 400.
Please let me know wether i was clear to u or not ??
Back to top
siva102 New User Joined: 28 Nov 2007Posts: 63 Location: Chennai
Hi all,
Please find below my input file,
Code:
----7----+----8----+----9----+----0----+----1----+----2----+----3----+--
<---VALUE1 -->07/21/2008;07/25/2008<--- value2--
<---VALUE1 -->07/28/2008;08/01/2008<--- value2--
<---VALUE3 ->08/05/2008;08/08/2008<--- value4--
<---VALUE3 ->08/11/2008;08/13/2008<--- value4--
My Output should be
Code:
----7----+----8----+----9----+----0----+----1----+----2----+----3----+--
<---VALUE1 -->07/21/2008;08/01/2008<--- value2--
<---VALUE3 ->08/05/2008;08/13/2008<--- value4--
Could you guys please help me to do this.
I suppose u can easily get my requirement.
Thanks in advance.
Back to top
hchinnam New User Joined: 18 Oct 2006Posts: 73
Well,
Sort the records based on START-DATE and then on END-DATE along with key. (Remember to keep these two fields towards end of the sort key)
Then use a splice on original key (excluding these two fields) to override END-DATE field.
Back to top
siva102 New User Joined: 28 Nov 2007Posts: 63 Location: Chennai
Hi,
Thanks for your reply. I have sorted that as per ascending start date and the descending end date.
But i dont know how to use that SPLICE.
Please chk my sort card below,
Code:
//SYSIN DD *
SORT FIELDS=(1,99,CH,A,100,10,CH,D,111,10,CH,D,122,278,CH,A)
Could you please give me some splice example to proceed with the next step to get my output.
Back to top
hchinnam New User Joined: 18 Oct 2006Posts: 73
If I understand you correctly this is how your sort should look like.
Code:
SORT FIELDS=(1,99,CH,A,
122,156,CH,A,
106,04,CH,A,
103,02,CH,A,
100,02,CH,A,
110,02,CH,A,
113,02,CH,A,
116,02,CH,A)
can you check and let me know, if it works fine with the data. if works fine
can you post me a sample of input and output data, so that i can give you a splice card accordingly.
Back to top
hchinnam New User Joined: 18 Oct 2006Posts: 73
Sorry,
It should be
Code:
SORT FIELDS=(1,99,CH,A,
122,156,CH,A,
106,04,CH,A,
103,02,CH,A,
100,02,CH,A,
117,02,CH,A,
114,02,CH,A,
111,02,CH,A)
Back to top
siva102 New User Joined: 28 Nov 2007Posts: 63 Location: Chennai
Well i have tried but there is some mismatch in the fields,
So i just did as per u told,
The new SORT card looks like,
Code:
SORT FIELDS=(1,99,CH,A,
106,04,CH,A,
103,02,CH,A,
100,02,CH,A,
117,04,CH,A,
114,02,CH,A,
111,02,CH,A,
122,278,CH,A)
It worked fine now.
The input is as below,
Code:
--9----+----0----+----1----+----2----+----3----+----4---
WMAC;Y;GWHR;07/21/2008;07/25/2008;Graduated reduced hour
WMAC;Y;GWHR;07/28/2008;08/01/2008;Graduated reduced hour
WOAC;Y;GWHR;08/05/2008;08/08/2008;gradaul rtw schedule
WOAC;Y;GWHR;08/11/2008;08/13/2008;gradaul rtw schedule
WMAC;Y;GWHR;06/11/2008;06/17/2008;reduced schedule
WMAC;Y;GWHR;06/18/2008;06/25/2008;reduced schedule
ans the output is as below,
Code:
WMAC;Y;GWHR;07/21/2008;07/25/2008;Graduated reduced hour
WMAC;Y;GWHR;07/28/2008;08/01/2008;Graduated reduced hour
WOAC;Y;GWHR;08/05/2008;08/08/2008;gradaul rtw schedule
WOAC;Y;GWHR;08/11/2008;08/13/2008;gradaul rtw schedule
WMAC;Y;GWHR;06/11/2008;06/17/2008;reduced schedule
WMAC;Y;GWHR;06/18/2008;06/25/2008;reduced schedule
I need the output to be
Code:
WMAC;Y;GWHR;07/21/2008;08/01/2008;Graduated reduced hour
WOAC;Y;GWHR;08/05/2008;08/08/2008;gradaul rtw schedule
WMAC;Y;GWHR;06/11/2008;06/25/2008;reduced schedule
Please help me regarding this.
Back to top
hchinnam New User Joined: 18 Oct 2006Posts: 73
OK,
Full step for your req should look like this.
Code:
//S0001 EXEC PGM=SYNCTOOL
//DFSMSG DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//TOOLMSG DD SYSOUT=*
//STATA DD DSN=&&TEMP1,
// SPACE=(CYL,(10,10)),
// DISP=(NEW,PASS),DCB=(LRECL=278,BLKSIZE=2780)
//DDIN DD DISP=SHR,DSN=In.File
//DDOUT DD SYSOUT=*
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(DDIN) TO(STATA) USING(CTL1)
SPLICE FROM(STATA) TO(OUT) ON(1,99,CH) -
ON(122,156,CH) WITH(111,10) KEEPNODUPS
//CTL1CNTL DD *
SORT FIELDS=(1,99,CH,A,
122,156,CH,A,
106,04,ZD,A,
100,02,ZD,A,
103,02,ZD,A,
117,02,ZD,A,
111,02,ZD,A,
114,02,ZD,A)
It worked for me with some small test data. so try it and let me know if you see as issue
Back to top
siva102 New User Joined: 28 Nov 2007Posts: 63 Location: Chennai
Hi,
Thank you very very much.
The code is working fine.
Really its gr8 to work with you.
Thanks again.
Back to top
Arun Raj Moderator Joined: 17 Oct 2006Posts: 2481 Location: @my desk
Hello,
I just tried with the above code and the below sample input and dint get what was expected.
Input
Code:
---+----9----+----0----+----1----+----2----+----3---
<---VALUE1 -->07/21/2008;07/25/2008<--- value2--
<---VALUE1 -->07/28/2008;08/01/2008<--- value2--
<---VALUE3 -->07/21/2008;07/25/2008<--- value3--
<---VALUE3 -->07/28/2008;08/01/2008<--- value3--
<---VALUE3 -->07/28/2008;08/05/2008<--- value3--
<---VALUE3 -->07/29/2008;08/04/2008<--- value3--
Actual Output
Code:
---+----9----+----0----+----1----+----2----+----3---
<---VALUE1 -->07/21/2008;08/01/2008<--- value2--
<---VALUE3 -->07/21/2008;08/04/2008<--- value3--
Expected Output
Code:
---+----9----+----0----+----1----+----2----+----3---
<---VALUE1 -->07/21/2008;08/01/2008<--- value2--
<---VALUE3 -->07/21/2008;08/05/2008<--- value3--
I m not sure whether you come across such input. Anyway Here's a SYNCTOOL job which should work for the above input as well.
Code:
//STEP1 EXEC PGM=SYNCTOOL
//DFSMSG DD SYSOUT=*
//TOOLMSG DD SYSOUT=*
//IN DD DSN=Input.file ---------- FB , LRECL=400
//OUT DD DSN=Output.file ---------- FB , LRECL=400
//T1 DD DSN=&&T1,DISP=(MOD,PASS)
//TOOLIN DD *
SORT FROM(IN) TO(T1) USING(CTL1)
SORT FROM(IN) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,99,CH) ON(201,200,CH) WITH(111,10)
//CTL1CNTL DD *
INREC OVERLAY=(401:106,4,100,2,103,2)
SORT FIELDS=(1,99,CH,D,201,200,CH,D,401,8,CH,D)
OUTFIL REMOVECC,NODETAIL,BUILD=(1,400),
SECTIONS=(1,99,201,200,TRAILER3=(1,200,201,200))
//CTL2CNTL DD *
INREC OVERLAY=(401:117,4,111,2,114,2)
SORT FIELDS=(1,99,CH,D,201,200,CH,D,401,8,CH,A)
OUTFIL REMOVECC,NODETAIL,BUILD=(1,400),
SECTIONS=(1,99,201,200,TRAILER3=(1,200,201,200))
Thanks,
Arun
Back to top
Arun Raj Moderator Joined: 17 Oct 2006Posts: 2481 Location: @my desk
Hi,
Sorry for the any inconvenience,
Please ignore the jcl in my previous post and read it as follows. Got some file positions wrong .
Code:
//TOOLIN DD *
SORT FROM(IN) TO(T1) USING(CTL1)
SORT FROM(IN) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,99,CH) ON(121,280,CH) WITH(111,10)
//CTL1CNTL DD *
INREC OVERLAY=(401:106,4,100,2,103,2)
SORT FIELDS=(1,99,CH,D,121,280,CH,D,401,8,CH,D)
OUTFIL REMOVECC,NODETAIL,BUILD=(1,400),
SECTIONS=(1,99,121,80,201,200,TRAILER3=(1,200,201,200))
//CTL2CNTL DD *
INREC OVERLAY=(401:117,4,111,2,114,2)
SORT FIELDS=(1,99,CH,D,121,280,CH,D,401,8,CH,A)
OUTFIL REMOVECC,NODETAIL,BUILD=(1,400),
SECTIONS=(1,99,121,80,201,200,TRAILER3=(1,200,201,200))
Thanks,
Arun
Back to top
siva102 New User Joined: 28 Nov 2007Posts: 63 Location: Chennai
Hi arun,
Thanks for your concern regarding this.
It was really helpful.
Back to top
Arun Raj Moderator Joined: 17 Oct 2006Posts: 2481 Location: @my desk
Siva,
Quote:
Thanks for your concern regarding this
You're welcome.
Thanks,
Arun
Back to top
siva102 New User Joined: 28 Nov 2007Posts: 63 Location: Chennai
Hi all,
Thanks for all your replys.
But for this combinig records only i faced two more situation where i am unable to proceed.
Please help me regarding this.
For the above example i need to do the following if the condition came like below,
e.g: -
Code:
Start Date End Date
01/11/2008
08/28/2008 10/09/2008
The above Sort gives the output like
Code:
Start Date End Date
10/09/2008
but i need it to be
Code:
Start Date End Date
08/28/2008 10/09/2008
Same for the End date too.
can anyone please help me regarding this ?
Back to top
Arun Raj Moderator Joined: 17 Oct 2006Posts: 2481 Location: @my desk
Siva,
Modify the CTL1/CTL2 cards in my last post as below.
Code:
//CTL1CNTL DD *
INREC IFTHEN=(WHEN=INIT,OVERLAY=(401:8C'9')),
IFTHEN=(WHEN=(100,10,CH,NE,C' '),OVERLAY=(401:106,4,100,2,103,2))
SORT FIELDS=(1,99,CH,D,121,280,CH,D,401,8,CH,D)
OUTFIL REMOVECC,NODETAIL,BUILD=(1,400),
SECTIONS=(1,99,121,80,201,200,TRAILER3=(1,200,201,200))
//CTL2CNTL DD *
INREC IFTHEN=(WHEN=INIT,OVERLAY=(401:8C'0')),
IFTHEN=(WHEN=(111,10,CH,NE,C' '),OVERLAY=(401:117,4,111,2,114,2))
SORT FIELDS=(1,99,CH,D,121,280,CH,D,401,8,CH,A)
OUTFIL REMOVECC,NODETAIL,BUILD=(1,400),
SECTIONS=(1,99,121,80,201,200,TRAILER3=(1,200,201,200))
Back to top
siva102 New User Joined: 28 Nov 2007Posts: 63 Location: Chennai
Hi arun,
Thanks for your reply.
Actully my input file length everything has been changed and this time the LRECL comes 700. so i substitute the values with the correct ones. like below
Code:
//TOOLIN DD *
SORT FROM(IN) TO(T1) USING(CTL1)
SORT FROM(IN) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,421,CH) ON(444,256,CH) WITH(433,10)
//CTL1CNTL DD *
INREC OVERLAY=(428,4,422,2,425,2)
SORT FIELDS=(1,421,CH,D,444,256,CH,D)
OUTFIL REMOVECC,NODETAIL,BUILD=(1,700),
//CTL2CNTL DD *
INREC OVERLAY=(439,4,433,2,436,2)
SORT FIELDS=(1,421,CH,D,444,256,CH,D)
OUTFIL REMOVECC,NODETAIL,BUILD=(1,700)
But i am getting nothing in the output file.
could you please tell me how u r defining the section part and if the LRECL of the input file is 400 and u r using 401: , Is not it abending ????
In my case it got abend ...
Please help me ???
Back to top
siva102 New User Joined: 28 Nov 2007Posts: 63 Location: Chennai
Hi arun,
I have runned your Sort card with the previous recordlength i. 400.
I am getting null output means no value in the output.
Could you please check it out once and let me know.
Thanks for your help.
Back to top
expat Global Moderator Joined: 14 Mar 2007Posts: 8797 Location: Welsh Wales
Quote:
Could you please check it out once and let me know.
Siva, what about YOU checking it out and posting what you have tried and the resulting errors. I am sure that Arun is not getting paid to spend so much time to resolve your problems.
Back to top
Arun Raj Moderator Joined: 17 Oct 2006Posts: 2481 Location: @my desk
Quote:
Actully my input file length everything has been changed and this time the LRECL comes 700
The jcl which I posted works fine for me with an input LRECL of 400 and the date positions provided by you. The same jcl might not work for all the input LRECLs. If it is not giving the results, you need to post the jcl you used.
Back to top
siva102 New User Joined: 28 Nov 2007Posts: 63 Location: Chennai
Hi expat,
Please dont go on my words.
I mean to say like i was having the problem in preparation of that Sort card. and Arun was really helping me from starting onwards.
He gave me a lot of solutions...I just checked wioth the code and i got null output. I tried my level the best to get the reason but unable to find it out. So requested Arun to please check wts the prob ??
Is thr any problem in asking a soln.....
Moreover SORRY if that hurts u alot .
Back to top
expat Global Moderator Joined: 14 Mar 2007Posts: 8797 Location: Welsh Wales
Siva,
Have replied via PM
Back to top
siva102 New User Joined: 28 Nov 2007Posts: 63 Location: Chennai
Hi Arun,
Thanks for replying. There was some misunderstanding between me and expat.
please find the JCl below,
Code:
//STEP EXEC PGM=SYNCTOOL
//DFSMSG DD SYSOUT=*
//TOOLMSG DD SYSOUT=*
//IN DD DSN=Input File - length = 400
//OUT DD DSN=OUTput File - length = 400
//T1 DD DSN=&&T1,DISP=(MOD,PASS)
//TOOLIN DD *
SORT FROM(IN) TO(T1) USING(CTL1)
SORT FROM(IN) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,99,CH) ON(201,200,CH) WITH(111,10)
//CTL1CNTL DD *
INREC IFTHEN=(WHEN=INIT,OVERLAY=(401:8C'9')),
IFTHEN=(WHEN=(100,10,CH,NE,C' '),OVERLAY=(401:106,4,100,2,103,2))
SORT FIELDS=(1,99,CH,D,121,280,CH,D,401,8,CH,D)
OUTFIL REMOVECC,NODETAIL,BUILD=(1,400),
SECTIONS=(1,99,121,80,201,200,TRAILER3=(1,200,201,200))
//CTL2CNTL DD *
INREC IFTHEN=(WHEN=INIT,OVERLAY=(401:8C'0')),
IFTHEN=(WHEN=(111,10,CH,NE,C' '),OVERLAY=(401:117,4,111,2,114,2))
SORT FIELDS=(1,99,CH,D,121,280,CH,D,401,8,CH,A)
OUTFIL REMOVECC,NODETAIL,BUILD=(1,400),
SECTIONS=(1,99,121,80,201,200,TRAILER3=(1,200,201,200))
/*
The error i m getting is
Code:
IN : RECFM=FB ; LRECL= 400; BLKSIZE= 27600
INREC RECORD LENGTH = 408
POTENTIALLY INEFFICIENT USE OF INREC
Bcos here we have been using OVERLAY=(401:8C'9')) .
Could you please suggest any other trick to get the output.
Back to top
Arun Raj Moderator Joined: 17 Oct 2006Posts: 2481 Location: @my desk
Siva,
The error you are getting is not a critical error and is NOT the reason for getting an empty output as you think.
You have coded the SPLICE statement as
Quote:
SPLICE FROM(T1) TO(OUT) ON(1,99,CH) ON(201,200 ,CH) WITH(111,10)
instead of
Quote:
SPLICE FROM(T1) TO(OUT) ON(1,99,CH) ON(121,280 ,CH) WITH(111,10)
Correct this and rerun your job
Back to top
Please enable JavaScript!