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
 

 

JCL SORT - GROUP Select on the basis of Detail Record

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
srinathds83

New User


Joined: 17 Jul 2007
Posts: 41
Location: pune

PostPosted: Mon Feb 24, 2014 1:01 pm    Post subject: JCL SORT - GROUP Select on the basis of Detail Record
Reply with quote

Hello,

I have below file:

Code:

"XXXXXXXX@URS.COM",\                                     
"A-BCD-EFG",\   
"QURS",,"                     ",\                               
"A","B","B","B","B","B",\                                               
"A","B","B","B","B","B",\                                                   
"A","B","B","B","B","B",\                                                 
"A","B","B","B","B","B",\                                               
"A","B","B","B","B","B",\       ,,,,,,,,,,,                                                   
"XXXXXXXX@ABC.IN",\                             
"ABC-BCDEFG",\   
"QURS",,"                     ",\                               
"A","B","B","B","B","B",\                                               
"A","B","B","B","B","B",\                                                   
"A","B","B","B","B","B",\                                                 
"A","B","B","B","B","B",\                                               
"A","B","B","B","B","B",\       ,,,,,,,,,,,             
"XXXXXXXX@XYZ.COM",\                                     
"G-HIJ-EFG",\   
"QURS",,"                     ",\                               
"A","B","B","B","B","B",\                                               
"A","B","B","B","B","B",\                                                   
"A","B","B","B","B","B",\                                                 
"A","B","B","B","B","B",\                                               
"A","B","B","B","B","B",\       ,,,,,,,,,,,           


Begining of Group starts wherever we find Charachter '@' in line and next 7 records are in the group.

I want all the groups with '-' on the 3rd column in 1st detail record for ex "A-BCD-EFG.

And other groups not staisfying the crieteria in another file:

O/P:
in first file
Code:

"XXXXXXXX@URS.COM",\                                     
"A-BCD-EFG",\   
"QURS",,"                     ",\                               
"A","B","B","B","B","B",\                                               
"A","B","B","B","B","B",\                                                   
"A","B","B","B","B","B",\                                                 
"A","B","B","B","B","B",\                                               
"A","B","B","B","B","B",\       ,,,,,,,,,,,     
"XXXXXXXX@XYZ.COM",\                                     
"G-HIJ-EFG",\   
"QURS",,"                     ",\                               
"A","B","B","B","B","B",\                                               
"A","B","B","B","B","B",\                                                   
"A","B","B","B","B","B",\                                                 
"A","B","B","B","B","B",\                                               
"A","B","B","B","B","B",\       ,,,,,,,,,,,     


For this i had written below sort Card which is not at all working:

Code:

OPTION COPY                                       
INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,80,SS,EQ,C'@'), 
              PUSH=(81:ID=1))                     
OUTFIL FNAMES=OUT11,                               
  INCLUDE=(5,1,CH,NE,C'-',AND,81,1,CH,C'1'),       
  BUILD=(1,80)                                     
OUTFIL FNAMES=OUT12,                               
  INCLUDE=(5,1,CH,EQ,C'-',AND,81,1,CH,C'1'),       
  BUILD=(1,80)                                     


Please provide the sort card.

Thanks
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7240

PostPosted: Mon Feb 24, 2014 2:20 pm    Post subject: Reply to: JCL SORT - GROUP Select on the basis of Detail Rec
Reply with quote

For your GROUP, PUSH your entire identifying record onto each item in the group: PUSH=(81:1,80... and include a SEQ. Use RECORDS=8

Use a second GROUP which is for sequence number equal to two, RECORDS=7. This is to PUSH the third position to all the records.

First OUTFIL with INCLUDE= for all the extra position being "-". IFTHEN=(WHEN=(sequence number is two),BUILD=(81,80,/,1,80), IFTHEN=(WHEN=NONE),BUILD=1,80

Second OUTFIL with OMIT= for above and for all sequence number to to one records. Same BUILDs
Back to top
View user's profile Send private message
srinathds83

New User


Joined: 17 Jul 2007
Posts: 41
Location: pune

PostPosted: Mon Feb 24, 2014 3:31 pm    Post subject: Re: Reply to: JCL SORT - GROUP Select on the basis of Detail
Reply with quote

Bill Woodger wrote:
For your GROUP, PUSH your entire identifying record onto each item in the group: PUSH=(81:1,80... and include a SEQ. Use RECORDS=8

Use a second GROUP which is for sequence number equal to twp, RECORDS=7. This is to PUSH the third position to all the records.

First OUTFIL with INCLUDE= for all the extra position being "-". IFTHEN=(WHEN=(sequence number is two),BUILD=(81,80,/,1,80), IFTHEN=(WHEN=NONE),BUILD=1,80

Second OUTFIL with OMIT= for above and for all sequence number to to one records. Same BUILDs


Hi Bill,

I got confused with the 2 Group condition. Could you please explain about the 2 Group Selection.

Thanks!
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7240

PostPosted: Mon Feb 24, 2014 3:48 pm    Post subject: Reply to: JCL SORT - GROUP Select on the basis of Detail Rec
Reply with quote

The first GROUP is going to put the first record onto each member of the group, with a sequence number. The actual first record is no longer needed as it will be recreated later.

Now you need your selection criteria (the "-") on each record of the group. So you start a GROUP for the sequence number two, and PUSH that position onto all the records in the second group.

Then in OUTFIL you get all the records you want, plus re-BUILD the first record first. Opposite for the other OUTFIL, though there you have to exclude the unwanted zero-sequence records as well.
Back to top
View user's profile Send private message
srinathds83

New User


Joined: 17 Jul 2007
Posts: 41
Location: pune

PostPosted: Mon Feb 24, 2014 4:38 pm    Post subject: Reply to: JCL SORT - GROUP Select on the basis of Detail Rec
Reply with quote

Hi Bill,

I used the below Sort card which is not working:

Code:


OPTION COPY                                                       
INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,80,SS,EQ,C'@'),RECORDS=8,       
              PUSH=(81:1,80,161:SEQNUM,8,ZD))                     
      IFTHEN=(WHEN=GROUP,BEGIN=(161,8,ZD,EQ,2,AND,83,1,CH,EQ,C'-'),
            RECORDS=7,PUSH=(171:83,1))                             
OUTFIL FNAMES=OUT11,                                               
  INCLUDE=(161,8,ZD,EQ,1,OR,171,1,CH,EQ,C'-'),                     
          BUILD=(81,80)                                           
OUTFIL FNAMES=OUT12                                               
  INCLUDE=(161,8,ZD,NE,1,AND,171,1,CH,NE,C'-'),                   
          BUILD=(81,80)                                           
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Feb 24, 2014 5:16 pm    Post subject: Reply to: JCL SORT - GROUP Select on the basis of Detail Rec
Reply with quote

not working... is not enough,
post the output received
so that people willing to help do not have to run the snippet to find out!
Back to top
View user's profile Send private message
srinathds83

New User


Joined: 17 Jul 2007
Posts: 41
Location: pune

PostPosted: Mon Feb 24, 2014 5:41 pm    Post subject: Reply to: JCL SORT - GROUP Select on the basis of Detail Rec
Reply with quote

Below is the Spool output:

Code:
   OPTION COPY                                                           
   INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,80,SS,EQ,C'@'),RECORDS=8,           
                 PUSH=(81:1,80,161:SEQNUM,8,ZD))                         
                              *                                           
         IFTHEN=(WHEN=GROUP,BEGIN=(161,8,ZD,EQ,2,AND,83,1,CH,EQ,C'-'),   
         *                                                               
               RECORDS=7,PUSH=(171:83,1))                                 
               *                                                         
   OUTFIL FNAMES=OUT11,                                                   
     INCLUDE=(161,8,ZD,EQ,1,OR,171,1,CH,EQ,C'-'),                         
             BUILD=(81,80)                                               
   OUTFIL FNAMES=OUT12                                                   
     INCLUDE=(161,8,ZD,NE,1,AND,171,1,CH,NE,C'-'),                       
            *                                                             
             BUILD=(81,80)                                               
             *                                                           
WER161B  ALTERNATE PARM USED                                             
WER268A  INREC STATEMENT   : SYNTAX ERROR                                 
WER275A  NO KEYWORDS FOUND ON CONTROL STATEMENT                           
WER275A  NO KEYWORDS FOUND ON CONTROL STATEMENT                           
WER268A  INCLUDE STATEMENT : SYNTAX ERROR                                 
WER275A  NO KEYWORDS FOUND ON CONTROL STATEMENT                           
WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000                             
WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE                             
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7240

PostPosted: Mon Feb 24, 2014 5:44 pm    Post subject: Reply to: JCL SORT - GROUP Select on the basis of Detail Rec
Reply with quote

Look at the manual for PUSH. Even if SEQNUM were correct, that is an eight-digit number you are trying to allow for, not a one-digit (which happens to have a maximum value of eight).

Follow enrico's advice. I've spotted one problem, don't have time for more now, so you've wasted that shot...
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7240

PostPosted: Mon Feb 24, 2014 5:46 pm    Post subject: Reply to: JCL SORT - GROUP Select on the basis of Detail Rec
Reply with quote

Where commas are needed, you need to code them.

You have completely ignored the use of the '/' in the BUILD - you'll end up without your first record that way.
Back to top
View user's profile Send private message
hailashwin

New User


Joined: 16 Oct 2008
Posts: 73
Location: Boston

PostPosted: Mon Feb 24, 2014 6:00 pm    Post subject:
Reply with quote

Hi Bill,
In this case, wouldn't having '/' in the OUTFIL make us have another pass to split the file?
I could build the first record as below, but was wondering if the splitting can happen in the same step rather than a separate step. icon_rolleyes.gif

Code:


//SYSIN DD *                                                         
  OPTION COPY                                                       
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,80,SS,EQ,C'@'),RECORDS=8,       
                PUSH=(81:1,80,SEQ=1)),                               
        IFTHEN=(WHEN=GROUP,BEGIN=(161,1,ZD,EQ,2,AND,3,1,CH,EQ,C'-'),
              RECORDS=7,PUSH=(162:3,1))                             
  OUTFIL IFTHEN=(WHEN=(162,1,CH,EQ,C'-',AND,161,1,ZD,EQ,2),         
                BUILD=(81,80,162:162,1,/,1,162))                     



Thanks,
Ashwin.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7240

PostPosted: Mon Feb 24, 2014 6:06 pm    Post subject: Reply to: JCL SORT - GROUP Select on the basis of Detail Rec
Reply with quote

That's one reason for wanting the sequence number of the group on the record. When it is two, do thee BUILD=(81,80,/,1,80) otherwise just BUILD=(1,80).

The other reason is to exclude the first record of a group, becaue the selection criteria is unknown (which is why we do the first bit...).
Back to top
View user's profile Send private message
hailashwin

New User


Joined: 16 Oct 2008
Posts: 73
Location: Boston

PostPosted: Mon Feb 24, 2014 6:12 pm    Post subject:
Reply with quote

Bill, I had initially used OUTREC instead of the OUTFIL in the above code, but '/' did not work on my OUTREC. Then ended up replacing OUTREC with OUTFIL which worked, but I had no place for my OUTFIL FNAMES. I dont know if I am missing something here.. icon_confused.gif

Thanks,
Ashwin.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7240

PostPosted: Mon Feb 24, 2014 8:56 pm    Post subject: Reply to: JCL SORT - GROUP Select on the basis of Detail Rec
Reply with quote

OUTFIL FNAMES=OUT11, you need INCLUDE for the selction character. IFTHEN=(WHEN=(logical expression) for seq value being "2" to do the BUILD with the slash-operator and IFTHEN=(WHEN=NONE to do the oridinary BUILD.

For the second OUTFIL, needs to OMIT everything the first OUTFIL has, plus the seq = C'1' records, same IFTHEN processing for the two BUILDs.
Back to top
View user's profile Send private message
srinathds83

New User


Joined: 17 Jul 2007
Posts: 41
Location: pune

PostPosted: Tue Feb 25, 2014 12:13 pm    Post subject: Reply to: JCL SORT - GROUP Select on the basis of Detail Rec
Reply with quote

I coded the below sort Card and its producing both the files same as input file

Code:
OPTION COPY                                                     
INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,80,SS,EQ,C'@'),RECORDS=8,     
              PUSH=(81:1,80,161:SEQ=2)),                       
 IFTHEN=(WHEN=GROUP,BEGIN=(161,2,ZD,EQ,2,AND,83,1,CH,EQ,C'-'), 
            RECORDS=7,PUSH=(163:3,1)),                         
 IFTHEN=(WHEN=GROUP,BEGIN=(161,2,ZD,EQ,2,AND,85,1,CH,EQ,C'-'), 
            RECORDS=7,PUSH=(164:5,1))                           
OUTFIL FNAMES=OUT11,                                           
          IFTHEN=(WHEN=(161,2,ZD,EQ,2,AND,163,1,CH,EQ,C'-'),   
          BUILD=(81,80,/,1,80)),                               
          IFTHEN=(WHEN=NONE,                                   
          BUILD=(1,80))                                         
OUTFIL FNAMES=OUT12,                                           
          IFTHEN=(WHEN=(161,2,ZD,EQ,2,AND,164,1,CH,EQ,C'-'),   
          BUILD=(81,80,/,1,80)),                               
          IFTHEN=(WHEN=NONE,                                   
          BUILD=(1,80))                                         


OUT11 and OUT12 Files are same as Input file. IFTHEN=(WHEN=NONE, condition is including all other records as well. Is there any other way to get only the required one.
Back to top
View user's profile Send private message
hailashwin

New User


Joined: 16 Oct 2008
Posts: 73
Location: Boston

PostPosted: Tue Feb 25, 2014 1:03 pm    Post subject: Reply to: JCL SORT - GROUP Select on the basis of Detail Rec
Reply with quote

Thank you very much Bill, I never knew IFTHEN can be used with FNAMES icon_redface.gif

srinathds83,
Try this one below..

Code:

//SYSIN DD *                                                       
  OPTION COPY                                                       
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,80,SS,EQ,C'@'),RECORDS=8,       
                PUSH=(81:1,80,SEQ=1)),                             
        IFTHEN=(WHEN=GROUP,BEGIN=(161,1,ZD,EQ,2,AND,3,1,CH,EQ,C'-'),
              RECORDS=7,PUSH=(162:3,1))                             
  OUTFIL FNAMES=OUT11,INCLUDE=(162,1,CH,EQ,C'-'),                   
                      IFTHEN=(WHEN=(161,1,ZD,EQ,2),                 
               BUILD=(81,80,/,1,80)),                               
                      IFTHEN=(WHEN=NONE,                           
               BUILD=(1,80))                                       
  OUTFIL FNAMES=OUT12,OMIT=(162,1,CH,EQ,C'-',OR,161,1,ZD,EQ,1),     
                      IFTHEN=(WHEN=(161,1,ZD,EQ,2),                 
               BUILD=(81,80,/,1,80)),                               
                      IFTHEN=(WHEN=NONE,                           
               BUILD=(1,80))                                       


Thanks,
Ashwin.
Back to top
View user's profile Send private message
srinathds83

New User


Joined: 17 Jul 2007
Posts: 41
Location: pune

PostPosted: Tue Feb 25, 2014 2:07 pm    Post subject: Reply to: JCL SORT - GROUP Select on the basis of Detail Rec
Reply with quote

Thanks it worked!

Also i wrote another which produced the exact same result:
Code:

//SORT  EXEC PGM=ICETOOL                                       
//IN   DD DSN= INPUT Data set,DISP=SHR             
//TOOLMSG DD SYSOUT=*                                           
//DFSMSG DD SYSOUT=*                                           
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)     
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)     
//RPS1  DD DSN= OUTPUT 1,             
// DISP=(NEW,CATLG,DELETE),                     
//RPS2 DD DSN=OUTPUT 2,               
// DISP=(NEW,CATLG,DELETE),                         
//TOOLIN DD *                                                   
COPY FROM(IN) TO(T1) USING(CTL1)                               
COPY JKFROM USING(CTL2)                                         
/*                                                             
//CTL1CNTL DD *                                                 
 INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,80,SS,EQ,C'@'),RECORDS=8,   
               PUSH=(81:ID=8))                                 
 OUTFIL FNAMES=T1                                               
 OUTFIL FNAMES=T2,                                             
   INCLUDE=(3,1,CH,EQ,C'-'),                                   
   BUILD=(81,8)                                                 
/*                                                             
//CTL2CNTL DD *                                               
  JOINKEYS F1=T1,FIELDS=(81,8,A),SORTED,NOSEQCK               
  JOINKEYS F2=T2,FIELDS=(1,8,A)                               
  JOIN UNPAIRED,F1                                             
  REFORMAT FIELDS=(F1:1,80,?)                                 
  OUTFIL FNAMES=RPS1,INCLUDE=(81,1,CH,EQ,C'1'),               
    BUILD=(1,80)                                               
  OUTFIL FNAMES=RPS2,INCLUDE=(81,1,CH,EQ,C'B'),               
    BUILD=(1,80)                                               
/*                                                             
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 -> JCL & VSAM 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 How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


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