Page 1 of 1

Record key out of sequence on join unpaired with same keys

PostPosted: Fri May 31, 2019 5:46 pm
by RalphEagle
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,

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

PostPosted: Fri May 31, 2019 5:52 pm
by NicC
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?

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

PostPosted: Fri May 31, 2019 6:01 pm
by RalphEagle
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 !

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

PostPosted: Fri May 31, 2019 6:06 pm
by Robert Sample
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.