Posted: Thu Mar 02, 2006 8:55 am Post subject: Help needed. Compare columns and write out
Please Help. I spent a few days trying to get my ICETOOL statements to work, but
was unable to come up with anything satisfactory.
Input files LRECL=80,FB. 3 keys with position 20,1, and 30,6 and 37,4
Rules
* If record in both files and 45,7 NE 85,7 AND 53,27 EQ 94,27
write File1 to OUT1 AND File2 to OUT2
*
* If record in both files and 45,7 EQ 85,7 AND 53,27 NE 94,27
OR 45,7 NE 85,7 AND 53,27 NE 94,27
write File1 to OUT3 AND File2 to OUT4
*
* If record in both files and 1,10 NE 85,10 AND 45,35 EQ 85,35
write File1 to OUT3 AND File2 to OUT4
*
* If record from File1 not in File2 write to DCARD1
* If File1 record = File2 record (1,80 = 85,80)
Write File1 to DCARD1 and File2 to DCARD2
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
//IN1 DD *
950.00 2005 N CHF RISKA 1111 OPEN TECH HOST ABCD
2950.00 2005 N CHF EAB 1111 OPEN TECH HOST EFGHI
250.00 2005 Y CHF RISK 2222 OPEN TECH HOST MNOP
100.00 2004 Y YEN JAP 3333 BOOK MECH AS400 XYZ
999.00 2003 N CHF LUGI 4444 BOOK TEST PC ACCY
//IN2 DD *
2950.00 2005 N CHF EAB 1111 OPEN TECH HOST EFGHI
950.00 2005 N CHF RISKA 1111 BOOKED TECH HOST ABCD
550.00 2005 Y CHF RISK 2222 OPEN TECH HOST MNOP
999.00 2003 N CHF LUGI 4444 BOOK PROD PC LENT
345.00 2002 N DEM GERM 2134 SETL TRAIN HOST SCHW
/*
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT1 DD DSN=OUT1
//OUT2 DD DSN=OUT2
//OUT3 DD DSN=OUT3
//OUT4 DD DSN=OUT4
//DCARD1 DD DSN=DCARD1
//DCARD2 DD DSN=DCARD2
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
* SPLICE records on key to get:
* IN1 key/data|id|IN2 data|
* id = 21 if key is in file1 and file2
* 11 if key is in file1 only
* 22 if key is in file2 only
SPLICE FROM(T1) TO(OUT1) -
ON(20,1,CH) ON(30,6,CH) ON(37,4,CH) -
WITH(81,1) WITH(85,80) -
KEEPNODUPS USING(CTL3)
/*
//CTL1CNTL DD *
OUTREC FIELDS=(1,80,81:C'11',165:X)
//CTL2CNTL DD *
OUTREC FIELDS=(1,80,81:C'22',85:85,80)
//CTL3CNTL DD *
* If record in both files and 45,7 NE 85,7 AND 53,27 EQ 94,27
* write File1 to OUT1 AND File2 to OUT2
*
* If record in both files and 45,7 EQ 85,7 AND 53,27 NE 94,27
* OR 45,7 NE 85,7 AND 53,27 NE 94,27
* write File1 to OUT3 AND File2 to OUT4
*
* If record in both files and 1,10 NE 85,10 AND 45,35 EQ 85,35
* write File1 to OUT3 AND File2 to OUT4
*
* If record from File1 not in File2 write to DCARD1
* If File1 record = File2 record (1,80 = 85,80)
* Write File1 to DCARD1 and File2 to DCARD2
Expected output
Code:
OUT1
====
950.00 2005 N CHF RISKA 1111 OPEN TECH HOST ABCD
OUT2
====
950.00 2005 N CHF RISKA 1111 BOOKED TECH HOST ABCD
OUT3
====
250.00 2005 Y CHF RISK 2222 OPEN TECH HOST MNOP
999.00 2003 N CHF LUGI 4444 BOOK TEST PC ACCY
OUT4
====
550.00 2005 Y CHF RISK 2222 OPEN TECH HOST MNOP
345.00 2002 N DEM GERM 2134 SETL TRAIN HOST SCHW
999.00 2003 N CHF LUGI 4444 BOOK PROD PC LENT
DCARD1
=======
2950.00 2005 N CHF EAB 1111 OPEN TECH HOST EFGHI
100.00 2004 Y YEN JAP 3333 BOOK MECH AS400 XYZ
DCARD2
=======
2950.00 2005 N CHF EAB 1111 OPEN TECH HOST EFGHI
Joined: 07 Jan 2003 Posts: 1056 Topics: 91 Location: The Blue Planet
Posted: Thu Mar 02, 2006 10:07 am Post subject:
Shuko,
One more clarification. will there be any duplicates in either of your input files on the key fields (All three keys combined - 20,1, and 30,6 and 37,4) ?
If there are no duplicates, then you can go in for the traditional way of appending a Character '1' in File 1, and Char '2' in File 2. Concatenate both these files and then do a Sort on key fields and Sum on the literal - then separate the records into different files using OUTFIL INCLUDE. I believe, this would be simpler to use and understand than using Splice (Just my Personal Opinion).
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Thu Mar 02, 2006 1:23 pm Post subject:
Shuko,
I came up with the following DFSORT/ICETOOL job that I think encompasses your rules, but it doesn't produce the same output you show. As far as I can tell, your rules don't actually match the expected output you show, but it's possible I misinterpreted your rules. I can't spend any more time on this, but perhaps my job will show you the technique you need to figure out what you want to do.
Code:
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD *
950.00 2005 N CHF RISKA 1111 OPEN TECH HOST ABCD
2950.00 2005 N CHF EAB 1111 OPEN TECH HOST EFGHI
250.00 2005 Y CHF RISK 2222 OPEN TECH HOST MNOP
100.00 2004 Y YEN JAP 3333 BOOK MECH AS400 XYZ
999.00 2003 N CHF LUGI 4444 BOOK TEST PC ACCY
//IN2 DD *
2950.00 2005 N CHF EAB 1111 OPEN TECH HOST EFGHI
950.00 2005 N CHF RISKA 1111 BOOKED TECH HOST ABCD
550.00 2005 Y CHF RISK 2222 OPEN TECH HOST MNOP
999.00 2003 N CHF LUGI 4444 BOOK PROD PC LENT
345.00 2002 N DEM GERM 2134 SETL TRAIN HOST SCHW
/*
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT1 DD SYSOUT=*
//OUT2 DD SYSOUT=*
//OUT3 DD SYSOUT=*
//OUT4 DD SYSOUT=*
//DCARD1 DD SYSOUT=*
//DCARD2 DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT1) -
ON(20,1,CH) ON(30,6,CH) ON(37,4,CH) -
WITH(101,81) -
KEEPNODUPS USING(CTL3)
/*
//CTL1CNTL DD *
OUTREC FIELDS=(1,80,180:X,181:C'11')
//CTL2CNTL DD *
OUTREC FIELDS=(1,80,101:1,80,181:C'22')
//CTL3CNTL DD *
OUT1
999.00 2003 N CHF LUGI 4444 BOOK TEST PC ACCY
950.00 2005 N CHF RISKA 1111 OPEN TECH HOST ABCD
OUT2
no records
OUT3
250.00 2005 Y CHF RISK 2222 OPEN TECH HOST MNOP
OUT4
550.00 2005 Y CHF RISK 2222 OPEN TECH HOST MNOP
DCARD1
345.00 2002 N DEM GERM 2134 SETL TRAIN HOST SCHW
100.00 2004 Y YEN JAP 3333 BOOK MECH AS400 XYZ
DCARD2
345.00 2002 N DEM GERM 2134 SETL TRAIN HOST SCHW
_________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Thu Mar 02, 2006 1:27 pm Post subject:
Phantom,
I don't see how SORT and SUM vs SPLICE would make this "simpler to use and understand". As far as I can tell, the complication here isn't the use of SPLICE, but figuring out the correct OUTFIL INCLUDE conditions. _________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
