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

Omitting records on a specific condition with Group by some
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
maxsubrat

Active User


Joined: 27 Feb 2008
Posts: 110
Location: india
PostPosted: Mon Jul 19, 2010 2:42 pm
 Reply with quote
Omitting records on a specific condition with Group by some other fields.
Code:
----------------------------------------------------------------------------------------------------------------

=COLS> ----+----1----+----2----+----3----+----4-
****** ***************************** Top of Data
000001 F1F1200030165680032010070280000051000001F
000002 F1ZZ3000301656800320100702800000510000020
000003 F1F1200030185640032010070280000051100001F
000004 F1ZZ3000301856400320100702800000511000020
000005 F1F120003018929   2010070280000051200001F
000006 F1ZZ3000301892900320100702800000512000020
000007 F1F1200030204870032010070280000051300001F
000008 F1ZZ3000302048700320100702800000513000020
000009 F1F1200030205520032010070280000051400001F
000010 F1ZZ3000302055200320100702800000514000020
000011 F1F1200030258740032010070280000051500001F
000012 F1ZZ3000302587400320100702800000515000020
000013 F1F120003026369   2010070280000051600001F
000014 F1ZZ3000302636900320100702800000516000020
000015 F1F1200030424080032010070280000051700001F
000016 F1ZZ3000304240800320100702800000517000020
000017 F1F120003042648   2010070280000051800001F
000018 F1F1200030426480032010070280000051800001F
000019 F1ZZ3000304264800320100702800000518000020

I am sorting on the basis of

SORT FIELDS=(27,9,CH,A,5,2,CH,A,16,3,CH,A)

Among which I have to check the condition

COND=((16,3,CH,EQ,C' '),OR,(16,3,CH,EQ,C'000'))

But the OMITTING should be done as a Group of 27,9,CH

i.e. for 17th row if there is a space in field starting from 16:3 bytes then we have to omit the subsequent rows(i.e. 18 & 19 as well) depending on the equal value of 27:9 (i.e. 800000518).

so 2 O/p file will be created like..

O/p: 1
Code:
-------

=COLS> ----+----1----+----2----+----3----+----4-
****** ***************************** Top of Data

000005 F1F120003018929   2010070280000051200001F
000006 F1ZZ3000301892900320100702800000512000020

000013 F1F120003026369   2010070280000051600001F
000014 F1ZZ3000302636900320100702800000516000020

000017 F1F120003042648   2010070280000051800001F
000018 F1F1200030426480032010070280000051800001F
000019 F1ZZ3000304264800320100702800000518000020


O/p: 2
Code:
-------
=COLS> ----+----1----+----2----+----3----+----4-
****** ***************************** Top of Data
000001 F1F1200030165680032010070280000051000001F
000002 F1ZZ3000301656800320100702800000510000020
000003 F1F1200030185640032010070280000051100001F
000004 F1ZZ3000301856400320100702800000511000020
-->  2 rows omitted..
000007 F1F1200030204870032010070280000051300001F
000008 F1ZZ3000302048700320100702800000513000020
000009 F1F1200030205520032010070280000051400001F
000010 F1ZZ3000302055200320100702800000514000020
000011 F1F1200030258740032010070280000051500001F
000012 F1ZZ3000302587400320100702800000515000020
---> 2 rows omitted...
000015 F1F1200030424080032010070280000051700001F
000016 F1ZZ3000304240800320100702800000517000020
---> 3 rows omitted
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Mon Jul 19, 2010 11:07 pm
 Reply with quote
maxsubrat,

Use the following DFSORT JCL which will give you the desired results. I assumed that your input is FB recfm and LRECL of 80.


Code:

//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD *                                                       
F1F1200030165680032010070280000051000001F                             
F1ZZ3000301656800320100702800000510000020                             
F1F1200030185640032010070280000051100001F                             
F1ZZ3000301856400320100702800000511000020                             
F1F120003018929   2010070280000051200001F                             
F1ZZ3000301892900320100702800000512000020                             
F1F1200030204870032010070280000051300001F                             
F1ZZ3000302048700320100702800000513000020                             
F1F1200030205520032010070280000051400001F                             
F1ZZ3000302055200320100702800000514000020                             
F1F1200030258740032010070280000051500001F                             
F1ZZ3000302587400320100702800000515000020                             
F1F120003026369   2010070280000051600001F                             
F1ZZ3000302636900320100702800000516000020                             
F1F1200030424080032010070280000051700001F                             
F1ZZ3000304240800320100702800000517000020                             
F1F120003042648   2010070280000051800001F                             
F1F1200030426480032010070280000051800001F                             
F1ZZ3000304264800320100702800000518000020                             
//OUT1     DD SYSOUT=*                                               
//OUT2     DD SYSOUT=*                                               
//SYSIN    DD *                                                       
  SORT FIELDS=(27,9,CH,A,5,2,CH,A,16,3,CH,A),EQUALS                   
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,8,ZD,RESTART=(27,9))), 
  IFTHEN=(WHEN=GROUP,BEGIN=(81,8,ZD,EQ,1),PUSH=(90:16,3))             
                                                                     
  OUTFIL FNAMES=OUT1,BUILD=(1,80),                                   
  INCLUDE=(90,3,CH,EQ,C' ',OR,90,3,CH,EQ,C'000')                     
                                                                     
  OUTFIL FNAMES=OUT2,SAVE,BUILD=(1,80)                               
//*
Back to top
View user's profile Send private message
VijayKrish

New User


Joined: 30 Jun 2010
Posts: 10
Location: India
PostPosted: Wed Jul 21, 2010 12:14 pm
 Reply with quote
Hi Kolusu,

Can you please explain in detail how the above sort statement works.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Wed Jul 21, 2010 11:43 pm
 Reply with quote
VijayKrish wrote:
Hi Kolusu,

Can you please explain in detail how the above sort statement works.


Read the PDF of User Guide for DFSORT PTF UK90013 (SORTUGPF) which explains the WHEN=GROUP function with examples.

www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000085
Back to top
View user's profile Send private message
maxsubrat

Active User


Joined: 27 Feb 2008
Posts: 110
Location: india
PostPosted: Thu Jul 22, 2010 6:58 pm
 Reply with quote
Hi,

We have the input like this:

F1F1200030258740032010070280000051500001F
F1ZZ3000302587400320100702800000515000020
F1F1200030263690032010070280000051600001F
F1ZZ30003026369 20100702800000516000020

We have records starting with F1F120 as detail and F1ZZ30 as trailer.
In detail record F1F120 the value at position 16 with length 3 is there.But at the trailer F1ZZ30 the value at position 16 with length 3 is not there.

We want the position 16 with length 3 in trailer to be filled by the value at position 16 with length 3 of the previous detail record.

The matching key for both detail and trailer is the field with position 27 of length 9 .This can be used as a key.

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

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Thu Jul 22, 2010 9:36 pm
 Reply with quote
maxsubrat,

Your sample second record is F1ZZ30 and it does have a value populated at pos 16.

Please use code tags so that column positions are preserved and it is easy to read. Since you haven't mentioned the LRECL and RECFM , the following DFSORT JCL will give you the desired results assuming the input is FB recfm and LRECL of 80.

Code:

//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD *                                                   
----+----1----+----2----+----3----+----4----+----5----+----6----+--
F1F1200030258740012010070280000051500001F                         
F1ZZ30003025874   20100702800000515000020                         
F1F1200030263690032010070280000051600001F                         
F1ZZ30003026369   20100702800000516000020                         
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                   
  SORT FIELDS=COPY                                               
  INREC IFOUTLEN=80,                                             
  IFTHEN=(WHEN=GROUP,BEGIN=(1,6,CH,EQ,C'F1F120'),PUSH=(81:16,3)),
  IFTHEN=(WHEN=(1,6,CH,EQ,C'F1ZZ30'),OVERLAY=(16:81,3))           
//*
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 Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts EZT program to build a flat file with... All Other Mainframe Topics 9
No new posts JCL sortcard to print only the records DFSORT/ICETOOL 11
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us