Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Sort Joins

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Sort Joins
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri May 25, 2012 5:37 pm    Post subject: Reply to: Sort Joins
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    Post subject:
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

Moderator


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

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

On what condition you need?

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

Moderator


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

PostPosted: Fri May 25, 2012 8:44 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject: Reply to: Sort Joins
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us