|
View previous topic :: View next topic
|
| Author |
Message |
helga
New User
Joined: 11 Sep 2006
Posts: 23
|
|
|
|
I ran a SPLICE in ICETOOL:
| Code: |
SPLICE FROM(IN1) TO(OUT1) ON(001,005,CH) KEEPNODUPS -
WITHEACH WITH(013,005) WITH(019,005) USING(CTL1) |
Where the input was:
| Code: |
ITEM1 XXXXX
ITEM4 XXXXX
ITEM5 XXXXX
ITEM7 XXXXX
ITEM2 XXXXX
ITEM4 XXXXX
ITEM6 XXXXX
ITEM7 XXXXX
ITEM3 XXXXX
ITEM5 XXXXX
ITEM6 XXXXX
ITEM7 XXXXX |
I expected the results to be:
| Code: |
ITEM1 XXXXX
ITEM2 XXXXX
ITEM3 XXXXX
ITEM4 XXXXX XXXXX
ITEM5 XXXXX XXXXX
ITEM6 XXXXX XXXXX
ITEM7 XXXXX XXXXX XXXXX |
But instead I got:
| Code: |
ITEM1 XXXXX
ITEM2 XXXXX
ITEM3 XXXXX
ITEM4 XXXXX XXXXX
ITEM5 XXXXX
ITEM6
ITEM7 XXXXX XXXXX XXXXX |
ie the WITHEACHing stopped as soon as it found a record with spaces in one of the WITH fields (look at ITEM5 and ITEM6).
Is that how it's supposed to work? |
|
| Back to top |
|
 |
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
SPLICE is working as expected:
WITHEACH WITH(013,005) WITH(019,005)
tells DFSORT to overlay 13,5 from the second record with the same key onto the base record, and to overlay 19,5 from the third record with the same key onto the base record.
So when you have:
| Code: |
ITEM5 XXXXX <--- base
ITEM5 11111 <--- second record
|
you get
ITEM5 XXXXX comes from the base record. Blanks come from 13,5 of the second record overlay the blanks in 13,5 of the base record. Since there's no third record, you don't get 19,5 from anywhere.
When you have:
| Code: |
ITEM6 XXXXX <--- base
ITEM6 22222 <--- second record
|
you get:
ITEM6blanksXXXXX comes from the base record. Blanks from 13,5 of the second record overlay the XXXXX in the base record. Since there's no third record, you don't get 19,5 from anywhere.
Now is your next question, how can I get what I want? |
|
| Back to top |
|
|
helga
New User
Joined: 11 Sep 2006
Posts: 23
|
|
|
|
Yes 
Thanks Frank, that makes perfect sense when you explain it that way and also makes me realise why the examples in the book are the way they are.
So SPLICE works very well with "reliable" data, ie a list of state codes and then an invoice total by states for whatever states you sold in, or in this case a specific number of records for each state activity, say gross sales and taxes paid.
It's a little bit trickier if you're merging two files with some state codes on one file and some state codes on another and either might or might not have repeated records. Though I have managed somethign along those lines. How come I always seem to be pushing the envelope? 
To make the above example work, all I have to do is insert records for the "blank" fields before I get to the SPLICE |
|
| Back to top |
|
|
helga
New User
Joined: 11 Sep 2006
Posts: 23
|
|
|
|
...what I hadn't realised was that with WITHEACH each WITH applies to an individual duplicate record. I was looking at it like OCCURS and using the WITHs to select fields from all the duplicate records. This wouldn't work anyway as if every WITH applied to every duplicate record, they'd overwrite the WITHs from the previous duplicate record.
...erm I think 
Good stuff, but like I said, suited mostly to dependalbe file configurations. |
|
| Back to top |
|
|
|
|
