IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Sort Joins


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
umanaga

New User


Joined: 09 Apr 2007
Posts: 33
Location: India

PostPosted: Fri May 25, 2012 4:56 pm
Reply with quote

I have two files with data as below
Code:

X002027174
X002267035
X002846116


Code:
X00202717401010101000000000100010000000000000081420190814201900000000
X002027174020100000010010000100000101000000001F0081420192101L4100200{
X002027174020200000010010000100000101000000001F0081420192101L4100200{
X00226703501010101000000000100010000000000000012820120128201200000000
X002267035021000000010000000100000001000000001F0122820112101L5100200{
X00284611601010101000000000100010000000000000071920310719203100000000


I need to join these 2 files, while joining i need to fetch fields from below recs in 2nd file.
Code:

X00202717401
X0020271740201


o/p as below
Code:
X002027174 008 F00
X002267035 001 F01
X002846116 007 blanks


is it possible?
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Fri May 25, 2012 5:37 pm
Reply with quote

Yes, probably.

What are the positions of the fields you need? What RECFM/LRECL of files?
Back to top
View user's profile Send private message
umanaga

New User


Joined: 09 Apr 2007
Posts: 33
Location: India

PostPosted: Fri May 25, 2012 8:10 pm
Reply with quote

Key is of 10 bytes 1,10 in both files.

in second file after 11,2 can be 01 or 02. 01 will always be one associated with key, 02's can be multiple.

To o/p i need 1,10 byte key, from 01 record i need 45,3 bytes. On 02 record which has value '01' in postion 13,2 need 47,3

i hope its clear..thanks for throwing a hope..
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Fri May 25, 2012 8:16 pm
Reply with quote

On what condition you need?

Code:
F00
F01
blanks
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Fri May 25, 2012 8:44 pm
Reply with quote

Assuming your inpputs are correct

Code:
//SRTJK EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//JNA DD *
X002027174
X002267035
X002846116
//JNB DD *
X00202717401010101000000000100010000000000000081420190814201900000000
X002027174020100000010010000100000101000000001F0081420192101L4100200{
X002027174020200000010010000100000101000000001F0081420192101L4100200{
X00226703501010101000000000100010000000000000012820120128201200000000
X002267035021000000010000000100000001000000001F0122820112101L5100200{
X00284611601010101000000000100010000000000000071920310719203100000000
//OUT DD SYSOUT=*
//T1 DD DSN=&&T1,SPACE=(TRK,(5,5)),
//  DISP=(MOD,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
 SORT JKFROM TO(OUT) USING(CTL1)
/*
//CTL1CNTL DD *
  JOINKEYS F1=JNA,FIELDS=(1,10,A)
  JOINKEYS F2=JNB,FIELDS=(1,10,A)
  REFORMAT FIELDS=(F2:1,80)
  INREC IFTHEN=(WHEN=(11,2,CH,EQ,C'01'),
          BUILD=(1:1,10,X,12:45,3)),
        IFTHEN=(WHEN=(11,2,CH,EQ,C'02'),
          BUILD=(1:1,10,X,12:47,3)),
        IFTHEN=(WHEN=NONE,
          BUILD=(1:1,10,4X))
  OPTION EQUALS
  SORT FIELDS=COPY
/*


Output is

Code:
X002027174 008
X002027174 F00
X002027174 F00
X002267035 001
X002267035 F01
X002846116 007
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri May 25, 2012 9:52 pm
Reply with quote

umanaga wrote:
Key is of 10 bytes 1,10 in both files.

in second file after 11,2 can be 01 or 02. 01 will always be one associated with key, 02's can be multiple.

To o/p i need 1,10 byte key, from 01 record i need 45,3 bytes. On 02 record which has value '01' in postion 13,2 need 47,3

i hope its clear..thanks for throwing a hope..


If you have multiple 02 records, do they ALL have the same value at pos 47? or does it vary with each 02 record?

ex:
Code:

----+----1----+----2----+----3----+----4----+----5----+
MY-KEY01  01                                ABC       
MY-KEY01  02                                  DEF     
MY-KEY01  02                                  GHI     
MY-KEY01  02                                  IJK     


Do you want your output to look like this?

Code:

MY-KEY01  ABC DEF GHI IJK (merging all 02 records into 1 record)


or

Code:

MY-KEY01  ABC DEF (only 1st 02 record value is taken)
Back to top
View user's profile Send private message
umanaga

New User


Joined: 09 Apr 2007
Posts: 33
Location: India

PostPosted: Fri May 25, 2012 10:27 pm
Reply with quote

Multiple 02 look like below.

Code:
----+----1----+----2----+----3----+----4----+----5----+
MY-KEY01  01                                ABC       
MY-KEY01  0201                                DEF     
MY-KEY01  0202                                GHI     
MY-KEY01  0203                                IJK     


i want o/p like

Code:
MY-KEY01  ABC DEF (DEF is to be taken from 02 having 01 in 13,2)
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri May 25, 2012 11:29 pm
Reply with quote

The following DFSORT JCL will give you the desired results. I assumed that you had a typo for the record X002267035 as it did NOT have '01' at pos 13.
Code:

//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//INA      DD *                                                     
X00202717401010101000000000100010000000000000081420190814201900000000
X002027174020100000010010000100000101000000001F0081420192101L4100200{
X002027174020200000010010000100000101000000001F0081420192101L4100200{
X00226703501010101000000000100010000000000000012820120128201200000000
X002267035020100000010000000100000001000000001F0122820112101L5100200{
X00284611601010101000000000100010000000000000071920310719203100000000
//INB      DD *                                                     
X002027174                                                           
X002267035                                                           
X002846116                                                           
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  OPTION COPY                                                       
  JOINKEYS F1=INA,FIELDS=(1,10,A)                                   
  JOINKEYS F2=INB,FIELDS=(1,10,A)                                   
  REFORMAT FIELDS=(F1:1,14)                                         
                                                                     
  INREC IFOUTLEN=20,                                                 
  IFTHEN=(WHEN=INIT,BUILD=(1,10,22:11,4)),                           
  IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,10),PUSH=(21:SEQ=1),RECORDS=2),     
  IFTHEN=(WHEN=GROUP,BEGIN=(21,1,ZD,EQ,1),PUSH=(11:22,4),RECORDS=2),
  IFTHEN=(WHEN=(21,1,ZD,EQ,2),OVERLAY=(15:22,4))                     
                                                                     
  OUTFIL REMOVECC,NODETAIL,SECTIONS=(1,10,TRAILER3=(1,20))           
//*                                                                 
//JNF1CNTL DD *                                                     
  INCLUDE COND=(11,2,CH,EQ,C'01',OR,11,4,CH,EQ,C'0201')             
  INREC IFTHEN=(WHEN=(11,2,CH,EQ,C'01'),BUILD=(1,10,X,45,3)),       
        IFTHEN=(WHEN=(11,2,CH,EQ,C'02'),BUILD=(1,10,X,47,3))         
//*



The output from this is
Code:

X002027174 008 F00
X002267035 001 F01
X002846116 007     
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts JCL sort card - get first day and las... JCL & VSAM 9
No new posts Sort First/last record of a subset th... DFSORT/ICETOOL 7
No new posts how to calculate SUM value for VB fil... DFSORT/ICETOOL 1
No new posts how to calculate SUM for VB file usin... JCL & VSAM 1
Search our Forums:

Back to Top