View previous topic :: View next topic
|
Author |
Message |
dejunzhu
Active User
Joined: 08 May 2008 Posts: 390 Location: China
|
|
|
|
Hello,
I have the following requirement:
I want to select out the last record which has the same key with some other record?
for the following input data,
Code: |
22546B.T.0000721...34.01481..
22546B.T.0000454...34.91454.. ==to be selected
366977.T.0000721...34.01481..
366977.T.0000454...34.91454..
366977.T.0000372...34.56432.. ==to be selected
50630B.T.0000121...34.01472..2
50630B.T.0000454...34.91454..
50630B.T.0000234...34.91454..
50630B.T.0000313...34.91345.. ==to be selected
72447A.T.0000134...34.01424..2
72447A.T.0000454...34.91454.. ==to be selected |
The expected output is like this:
Code: |
22546B.T.0000454...34.91454..
366977.T.0000372...34.56432..
50630B.T.0000313...34.91345..
72447A.T.0000454...34.91454..
|
Thank you in advance. |
|
Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008 Posts: 390 Location: China
|
|
|
|
I forgot to state that:
in the above instance, the key is from column 1 to column 6. |
|
Back to top |
|
|
senjay
Active User
Joined: 10 May 2007 Posts: 147 Location: India
|
|
|
|
Hi,
Look at the last record in the below sample. Is the below scenario possible in your file. If yes, would you have to omit the record?
Code: |
22546B.T.0000721...34.01481..
22546B.T.0000454...34.91454.. ==to be selected
366977.T.0000721...34.01481..
366977.T.0000454...34.91454..
366977.T.0000372...34.56432.. ==to be selected
50630B.T.0000121...34.01472..2
50630B.T.0000454...34.91454..
50630B.T.0000234...34.91454..
50630B.T.0000313...34.91345.. ==to be selected
72447A.T.0000134...34.01424..2
72447A.T.0000454...34.91454.. ==to be selected
99999B.T.0000234...34.91454.. == will your file have a unique record?
|
|
|
Back to top |
|
|
senjay
Active User
Joined: 10 May 2007 Posts: 147 Location: India
|
|
|
|
Hi,
Here are the codes for both the requirement. Choose whichever you want.
Soln 1:
Code: |
//S010 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//FILE1 DD *
22546B SENTHIL
22546B VARUNIL
22546B KRISHNA
22333B SANGEET
22333B SOUNDAR
22123B VIJAYIL
/*
//FILEOUT DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(FILE1) TO(FILEOUT) ON(1,6,CH) LAST
/*
|
Output:
Code: |
22123B VIJAYIL
22333B SOUNDAR
22546B KRISHNA
|
Soln 2:
Code: |
//S010 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//FILE1 DD *
22546B SENTHIL
22546B VARUNIL
22546B KRISHNA
22333B SANGEET
22333B SOUNDAR
22123B VIJAYIL
/*
//FILEOUT DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(FILE1) TO(FILEOUT) ON(1,6,CH) LASTDUP
/*
|
output:
Code: |
22333B SOUNDAR
22546B KRISHNA
|
|
|
Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008 Posts: 390 Location: China
|
|
|
|
Yes, the above method works.
But if there are two (or more) input files and only one output file, how should I change the above jcl?
Thanks. |
|
Back to top |
|
|
senjay
Active User
Joined: 10 May 2007 Posts: 147 Location: India
|
|
|
|
Assuming the second soln is the one you want, here is how you need to do.
Here second file is the input file from which you want to have the last record in your output file. OPTION EQUALS is used to retain the original input order.
Similarly handle for multiple input files
Code: |
//S010 EXEC PGM=ICETOOL,COND=(0,LT),PARM='DYNALLOC=(3390,50)'
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//TOOLOUT DD SYSOUT=*
//EIN1 DD DSN=your First file...,
// DISP=SHR
// DD DSN=your second file...,
// DISP=SHR
//AUS1 DD DSN=output file....,
// DISP=(NEW,CATLG,DELETE),
// DCB=*.EIN1,
// SPACE=(CYL,(2,4),RLSE),
//TOOLIN DD *
SELECT FROM(EIN1) ON(1,06,CH) TO(AUS1) LASTDUP USING(CTL1)
/*
//CTL1CNTL DD *
OPTION EQUALS
SORT FIELDS=(1,6,CH,A)
/*
|
|
|
Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
|
|
|
|
senjay,
You don't need CTL1CNTL when you are already using the same fields in the ON statement. SELECT has EQUALS by default. So remove it |
|
Back to top |
|
|
senjay
Active User
Joined: 10 May 2007 Posts: 147 Location: India
|
|
|
|
Hi Kolusu,
I had this doubt on EQUALS. Thanks a lot for correcting me.
So the solution is,
Code: |
//S010 EXEC PGM=ICETOOL,COND=(0,LT),PARM='DYNALLOC=(3390,50)'
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//TOOLOUT DD SYSOUT=*
//EIN1 DD DSN=your First file...,
// DISP=SHR
// DD DSN=your second file...,
// DISP=SHR
//AUS1 DD DSN=output file....,
// DISP=(NEW,CATLG,DELETE),
// DCB=*.EIN1,
// SPACE=(CYL,(2,4),RLSE),
//TOOLIN DD *
SELECT FROM(EIN1) ON(1,06,CH) TO(AUS1) LASTDUP
/*
|
From the manual,
Quote: |
ICETOOL passes the EQUALS option to DFSORT to ensure that duplicates are kept in their original input order. |
I need to look in to the manuals deeply before posting any. |
|
Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
|
|
|
|
senjay,
You cannot pass parms to ICETOOL via Parm statement in your JCL. You need to pass them via DFSPARM DD statement. The ideal solution would be using the LAST keyword instead of LASTDUP. With Lastdup keyword you would miss any unique items
Code: |
//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//DFSPARM DD *
OPTION DYNALLOC=(3390,50)
//IN DD *
22546B.T.0000721...34.01481..
22546B.T.0000454...34.91454.. ==TO BE SELECTED
366977.T.0000721...34.01481..
366977.T.0000454...34.91454..
366977.T.0000372...34.56432.. ==TO BE SELECTED
50630B.T.0000121...34.01472..2
50630B.T.0000454...34.91454..
50630B.T.0000234...34.91454..
50630B.T.0000313...34.91345.. ==TO BE SELECTED
72447A.T.0000134...34.01424..2
72447A.T.0000454...34.91454.. ==TO BE SELECTED
72447B.T.0000454...34.91454.. ==TO BE SELECTED
//OUT DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(IN) ON(1,6,CH) TO(OUT) LAST
/* |
|
|
Back to top |
|
|
senjay
Active User
Joined: 10 May 2007 Posts: 147 Location: India
|
|
|
|
Hi Kolusu,
Quote: |
You cannot pass parms to ICETOOL via Parm statement in your JCL |
Yes, copy paste error.
Quote: |
With Lastdup keyword you would miss any unique items
|
From OP,
Quote: |
I want to select out the last record which has the same key with some other record |
So i believe he would need LASTDUP and not LAST. Anyways both the solutions are provided. The OP can use whatever he wants. |
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
Quote: |
PARM='DYNALLOC=(3390,50)' |
If you are going to post solutions in the DFSORT Forum, then please don't override other sites' default values arbitrarily. There's no reason to assume 3390,50 is needed for this job. And as Kolusu points out, PARM= values are ignored for ICETOOL.
Quote: |
I need to look in to the manuals deeply before posting any |
Or alternatively, let the two DFSORT developers on this board (Kolusu and myself) answer the DFSORT and ICETOOL questions. |
|
Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008 Posts: 390 Location: China
|
|
|
|
Thanks to your help, my problem has been resolved.
Thanks once again! |
|
Back to top |
|
|
|