Record key out of sequence on join unpaired with same keys



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Record key out of sequence on join unpaired with same keys

Postby RalphEagle » Fri May 31, 2019 5:46 pm

I have two files. I am comparing them on a given key, and I would like to only keep the records from the first file which do not have a match in the other.

Normally, we do that with a JOIN UNPAIRED F1,ONLY. But I get an error for a key being out of sequence every single time. My keys are absolutely identical, I checked this.

The JCL

//STEP010  EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//IN01     DD DSN=WORK.RL90.SELIS,
//            DISP=SHR
//IN02     DD DSN=WORK.RL90.SELOS,
//            DISP=SHR
//SORTOUT  DD DSN=WORK.RL90.COMPARE,
//            DISP=(NEW,CATLG,DELETE),
//            SPACE=(CYL,(10,100),RLSE)
//SYSIN    DD DATA
  JOINKEYS F1=IN01,FIELDS=(22,19,A),SORTED
  JOINKEYS F2=IN02,FIELDS=(22,19,A),SORTED
* We isolate unmatched records from F1; they don't have a match in F2
  JOIN UNPAIRED,F1,ONLY
  REFORMAT FIELDS=(F1:1,106)
  OPTION COPY
/*
//
 


And here is an excerpt of F1 file
----+----1----+----2----+----3----+----4-
 AU     2013354007A  AU     2013354007A1.
 AU     2014321293A  AU     2014321293A1.
 AU     2014321302A  AU     2014321302A1.
 AU     2014321305A  AU     2014321305A1.
 AU     2014321306A  AU     2014321306A1.
 CA        2742468A  CA        2742468A1.
 CA        2894035A  CA        2894035A1.
 CA        2920687A  CA        2920687A1.
 CA        2922688A  CA        2922688A1.
 CA        2922692A  CA        2922692A1.
 CA        2922717A  CA        2922717A1.
 CA     2011000670W  WO     2011153623A2.
 CA     2011000670W  WO     2011153623A3.
 CL     2015001511A  CL     2015001511A1.
 CN     2014094975W  WO     2016101218A1.
 CN   201080029859A  CN      102470015A .
 CN   201080029859A  CN      102470015B .
 


And here is an excerpt of the second file
----+----1----+----2----+----3----+----4-
 AU     2013354007A  AU     2013354007A1.
 AU     2014321293A  AU     2014321293A1.
 AU     2014321302A  AU     2014321302A1.
 AU     2014321305A  AU     2014321305A1.
 AU     2014321306A  AU     2014321306A1.
 CA        2742468A  CA        2742468A1.
 CA        2894035A  CA        2894035A1.
 CA        2920687A  CA        2920687A1.
 CA        2922688A  CA        2922688A1.
 CA        2922692A  CA        2922692A1.
 CA        2922717A  CA        2922717A1.
 CA     2011000670W  WO     2011153623A2.
 CA     2011000670W  WO     2011153623A3.
 CL     2015001511A  CL     2015001511A1.
 CN     2014094975W  WO     2016101218A1.
 CN   201080029859A  CN      102470015A .
 CN   201080029859A  CN      102470015B .
 


As you can see, the key I choose (22,19,A) appear rigorously identical. How is it that I am being told they are out of sequence ?!

Thank you for your help,
RalphEagle
 
Posts: 24
Joined: Mon Apr 23, 2018 2:51 pm
Has thanked: 17 times
Been thanked: 0 time

Re: Record key out of sequence on join unpaired with same ke

Postby NicC » Fri May 31, 2019 5:52 pm

Well, those may be in sequence but are the entire data sets in sequence? Obviousy not. Are those blanks or non-displayabe data? What happens if you remove the SORTED keywords from both data sets?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic

These users thanked the author NicC for the post:
RalphEagle (Fri May 31, 2019 6:45 pm)
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Record key out of sequence on join unpaired with same ke

Postby RalphEagle » Fri May 31, 2019 6:01 pm

Hi NicC,

I removed the SORTED keywords, it works just fine ! The problem was (I realize now) that I sorted with respect to the LEFT columns, while I was comparing with the RIGHT column as a key.

To answer your questions completely, all spaces here are blanks. The dots indicate binary data.

Thank you !
RalphEagle
 
Posts: 24
Joined: Mon Apr 23, 2018 2:51 pm
Has thanked: 17 times
Been thanked: 0 time

Re: Record key out of sequence on join unpaired with same ke

Postby Robert Sample » Fri May 31, 2019 6:06 pm

How is it that I am being told they are out of sequence ?!
Because starting at column 22, you have a couple of records with WO values followed by a record with a CL value -- the CL record is out of sequence.

These users thanked the author Robert Sample for the post:
RalphEagle (Fri May 31, 2019 6:46 pm)
Robert Sample
Global moderator
 
Posts: 3719
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 279 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post