MVSFORUMS.com :: View topic - How can you do a JOIN on a variable length field?

MVSFORUMS.com Forum Index

MVSFORUMS.com
A Community of and for MVS Professionals

FAQ

Register

Log in to check your private messages

How can you do a JOIN on a variable length field?

printer-friendly view

MVSFORUMS.com Forum Index -> Utilities

View previous topic :: View next topic

Author

Message

Terra_au
Beginner

Joined: 21 Feb 2018
Posts: 1
Topics: 1
Location: Sydney

Posted: Thu Dec 16, 2021 8:26 am Post subject: How can you do a JOIN on a variable length field?

Reply with quote

I have 2 FB LRECL=80 files with different record layouts.

I need to do a compare of field 2 in both files and output to a match or nonmatch file. Field 2 varies in length from 6 to 42 chars.

I can't just do a JOIN on the largest one as fields 3 and 4 vary in their start position.

Files were created by running the following:-

Code:

//SYSIN DD *
SORT FIELDS=(11,10,BI,A)
OMIT COND=(1,1,CH,EQ,C'0',OR,1,1,CH,EQ,C'1',OR,2,1,CH,EQ,C' ',
OR,2,4,CH,EQ,C'JOBS',OR,2,8,CH,EQ,C'Agentmsg',
OR,6,5,CH,EQ,C'TYPE-')
OUTFIL FNAMES=EXTERNAL,INCLUDE=(2,8,CH,EQ,C'EXTERNAL')
OUTFIL FNAMES=JOBLIST,INCLUDE=(2,3,CH,EQ,C'JOB',
OR,2,6,CH,EQ,C'AixJob',
OR,2,8,CH,EQ,C'LinuxJob',
OR,2,8,CH,EQ,C'UnixJob')
OUTFIL FNAMES=OTHER,SAVE
END
/*

Any help would be appreciated.

Sample file layouts below:-
NOTE: Paste of file layout isn't showing correctly in preview. There are multiple spaces between fields 2,3 and 4

EXTFILE

Code:

----+----1----+----2----+----3----+----4----+----5----+----6----+--
EXTERNAL CBCvrz11 0 CBCv2a11
EXTERNAL CBCv2z11 0 CBCvra11
EXTERNAL ziadavqatp 0 coabab1e
EXTERNAL ziadavqbtn 0 coabab1g
EXTERNAL ziadavqtrn 0 coabab1f
EXTERNAL ziadavqtsu 0 coabab1d
EXTERNAL zipdtet31l 0 coabab1c
EXTERNAL coabdc2f 0 coabac1c, coabac1d
EXTERNAL coabdc2o 0 coabac1g
EXTERNAL coiccdaa 0 CBCvra11
EXTERNAL coiccdaa 0 coabasaa
EXTERNAL coidysza 0 CBCvra11
EXTERNAL coisbsza 0 CBCvra11
EXTERNAL coisgsza 0 CBCvra11
EXTERNAL coitbsza 0 CBCvra11

JOBLIST

Code:

----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----
UnixJob CBCvra11 11 CBCVRA11_OK_ODAT.DOWNSTREAM,CBCvrt11
UnixJob CBCvrd1b 1 CBCvrl1b
UnixJob CBCvrd1c 1 CBCvrl1c
UnixJob CBCvrd1d 1 CBCvrl1d
UnixJob CBCvrd1e 1 CBCvrl1e
UnixJob CBCvrd1f 1 CBCvrl1f
UnixJob CBCvrl1b 1 CBCvrz11
UnixJob CBCvrl1c 1 CBCvrz11
UnixJob CBCvrl1d 1 CBCvrz11
UnixJob CBCvrl1e 1 CBCvrz11
UnixJob CBCvrl1f 1 CBCvrz11
UnixJob CBCvrt11 1 CBCVRT11_OK_ODAT.DOWNSTREAM,CBCvrt21,

Send private message

kolusu
Site Admin

Site Admin

Joined: 26 Nov 2002
Posts: 12368
Topics: 75
Location: San Jose

Posted: Thu Dec 16, 2021 12:10 pm Post subject:

Reply with quote

Terra_au wrote:

NOTE: Paste of file layout isn't showing correctly in preview. There are multiple spaces between fields 2,3 and 4

Terra_au,

You need use code tags which would retain spacing. Here is an example of how to use code tags

https://www.mvsforums.com/helpboards/viewtopic.php?p=19031#19031

If the key is variable then you can parse out the key looking for a space and then build the temp key. You can add formatting the data after the match. I haven't added it.

something like this

Code:

//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//EXTFILE DD DISP=SHR,DSN=your.input.extfile
//JOBLIST DD DISP=SHR,DSN=Your.input.joblist
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
JOINKEYS F1=EXTFILE,FIELDS=(81,42,A)
JOINKEYS F2=JOBLIST,FIELDS=(81,42,A)
REFORMAT FIELDS=(F1:1,80,F2:1,80)
/*
//JNF1CNTL DD *
INCLUDE COND=(2,8,CH,EQ,C'EXTERNAL')
INREC PARSE=(%01=(ABSPOS=11,ENDBEFR=C' ',FIXLEN=42)),
OVERLAY=(81:%01)
/*
//JNF2CNTL DD *
INCLUDE COND=(2,3,CH,EQ,C'JOB',OR,
2,6,CH,EQ,C'AixJob',OR,
2,8,CH,EQ,C'LinuxJob',OR,
2,8,CH,EQ,C'UnixJob')

INREC PARSE=(%01=(ABSPOS=11,ENDBEFR=C' ',FIXLEN=42)),
OVERLAY=(81:%01)
/*

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu

Send private message

Send e-mail

Visit poster's website

Display posts from previous:

	MVSFORUMS.com Forum Index -> Utilities	All times are GMT - 5 Hours
Page 1 of 1

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

MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group