I tried the obvious WITHEACH .. WITH ... WITH ... approach, but it failed to return base2 correctly ...
so, my approach involves two SPLICE operations :
(note that i can get more than one duplicate, but i only care about the first one ... hence the WITHEACH. In the input dataset above I would discard (2))
...
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Here's a DFSORT/ICETOOL job that will give you the expected output for your simplified input. I don't know how well this relates to your actual situation but it's the best I can do given what you've said about your data. Hopefully, you can extrapolate the technique to your real data.
almost there ... but this has the same problem as my first attempt in that it does not handle the base2 case ...
i was able to get it working ok by adding the following step (this comes just before the SPLICE step):
Code:
* 3\ APPEND TR22s AGAIN ONTO TEMP1. THIS IS TO ENSURE THAT IF
* THERE IS NO TR24 MATCH, WE STILL GET A TR22 MATCH AS EXPECTED
*
COPY FROM(INA) USING(CTL3)
*
...
//CTL3CNTL DD *
INCLUDE COND=(2,4,CH,EQ,C'TR22')
OUTFIL FNAMES=TMP1,
BUILD=(36:12,9,93:23,15,240:40,10,252:52,10,264:64,1,265:10X)
/*
not sure it's easier to read, but it does improve performance. I have to say that i've never needed to simulate two LOJs like this before, but there are times when db2 indexes just don't work in your favour ...
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Quote:
almost there ... but this has the same problem as my first attempt in that it does not handle the base2 case ...
The solution I gave you does handle the base2 case for the simple input you showed which is all I had to go on. I don't know what your real input looks like (I don't know SQL) so I couldn't code for that, but I'm glad my example helped you figure it out.