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
 

 

How to extract records for a specific condition on key

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Venkata Ramayya

New User


Joined: 03 Dec 2007
Posts: 39
Location: United States

PostPosted: Tue Mar 31, 2015 2:02 am    Post subject: How to extract records for a specific condition on key
Reply with quote

Hello,

Please help me extracting records for the below scenario

Input file has below set of record types

001
002
005
002
005
002
003
003
003
004
005
002
005
099

I want to omit records '002' and '005' if record type 005 immediately followed by 002

Output:
001
002
003
003
003
004
005
099
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Tue Mar 31, 2015 2:48 am    Post subject: Reply to: How to extract records for a specific condition on key
Reply with quote

So, WHEN=GROUP with BEGIN for type 005, PUSH the entire record to get a copy at the end of the current record, SEQ=1, RECORDS=2.

OUTFIL to INCLUDE= or OMIT= the records you don't want, which are when the SEQuence field is 1, or when original record equal to 002 and PUSHed record equal to 005.

The IFTHEN=(WHEN=(logical expression to identify the 2s, and BUILD two records, PUSHED first then original, seperated by / (the slash operator). IFTHEN=(WHEN=NONE to just BUILD to original size.

Should be close.
Back to top
View user's profile Send private message
Venkata Ramayya

New User


Joined: 03 Dec 2007
Posts: 39
Location: United States

PostPosted: Wed Apr 01, 2015 12:27 am    Post subject:
Reply with quote

Thanks Bill.

Somehow I am not able to put this in a right syntax. Could you help me giving sample here??
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Wed Apr 01, 2015 12:46 am    Post subject:
Reply with quote

Look for syntax here,

http://ibmmainframes.com/about62445.html
http://ibmmainframes.com/about52867.html
Back to top
View user's profile Send private message
Venkata Ramayya

New User


Joined: 03 Dec 2007
Posts: 39
Location: United States

PostPosted: Wed Apr 01, 2015 7:40 pm    Post subject:
Reply with quote

Thanks Rohit.

Bill, Looks like your solution would omit some of the required records.
May be the way I was asking my question is not right. Here is my question again

Input file has below set of data and first three postions are record types
001 A
002 B
005 C
002 D
005 E
002 F
003 G
003 H
003 I
004 J
005 K
002 L
005 M
099 N

I want to omit records '002' and '005' if record type 005 immediately followed by 002


Output:
001 A
002 F
003 G
003 H
003 I
004 J
005 K
099 N
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Wed Apr 01, 2015 10:34 pm    Post subject: Reply to: How to extract records for a specific condition on key
Reply with quote

Venkata,

A lot has already been written on this regarding your query. I suppose you may want to search the web (for your own better understanding) OR read the DFSORT manual for WHEN=GROUP (if nothing more).
Back to top
View user's profile Send private message
Venkata Ramayya

New User


Joined: 03 Dec 2007
Posts: 39
Location: United States

PostPosted: Fri Apr 10, 2015 2:26 am    Post subject:
Reply with quote

Thanks Rahul!

I'm still struggling to get the final result.

I got rid of '5' records using the below sort card, but still struggling to get rid of some of the '2' records..can some one help?

OPTION COPY
INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'002'),
PUSH=(6:1,5),RECORDS=2)
OUTFIL OMIT=(1,3,CH,EQ,C'005',AND,6,3,CH,EQ,C'002')

So far my output is
001 A
002 B002 B
002 D002 D
002 F002 F
003 G002 F
003 H
003 I
004 J
005 K
002 L002 L
099 N


I dont want below records
---------------
001 A
002 B002 B - Not required
002 D002 D - Not required
002 F002 F
003 G002 F
003 H
003 I
004 J
005 K
002 L002 L - Not required
099 N
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Fri Apr 10, 2015 5:06 am    Post subject: Reply to: How to extract records for a specific condition on key
Reply with quote

Hi Venkata,

You seem to be on the right track. Keep in mind, Analysis comes first and then the coding. So, most of the times (if not all), we first need to think of a logic to achieve what we want.

Follow me on this:

1. Use a group of 2 records that starts with 002 and push the first 3 bytes. You will get something like this
001 A
002 002 B
005 002 C
002 002 D
005 002 E
002 002 F
003 002 G
003 H
003 I
004 J
005 K
002 002 L
005 002 M
099 N

2. Now you can easily omit records that have '002 002' and '005 002'. But that will also omit record 'F' which we don't want.

3. Now think of this: What if we write the records in the opposite order and delete/omit group of 2 records that begin with 005 in the same way we did in point 1. This time the group will begin with 005.

009 N
005 005 M
002 005 L
005 005 K
004 005 J
003 I
003 H
003 G
002 F
005 005 E
002 005 D
005 005 C
002 005 B
001 A

This will delete an additional record 'K'. Point to note: 'F' is not deleted here and 'K' was not deleted in point 1. Rest of the records that we deleted/omitted, were the records that we actually wanted to remove.

4. Now, if you do a SUM=NONE to remove duplicates on the 'addition' of sets you get from point 1 and point 3 (i.e. add them 1 after other), you should be able to get what you want.

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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Apr 10, 2015 5:11 am    Post subject: Reply to: How to extract records for a specific condition on key
Reply with quote

This seems to work, for your words:

Code:
  OPTION COPY
                                           
  INREC IFTHEN=(WHEN=GROUP,
                  BEGIN=(1,3,CH,EQ,C'005'),
                  PUSH=(6:1,5,SEQ=1),
                  RECORDS=2)
  OUTFIL OMIT=(11,1,CH,EQ,C'1',
              OR,
               1,3,CH,EQ,C'002',
                AND,
               6,3,CH,EQ,C'005'),
         IFOUTLEN=5,
         IFTHEN=(WHEN=(6,1,CH,NE,C' '),
                  BUILD=(6,5,
                         /,
                         1,5))


With this data:

Code:
//SORTIN   DD *
001 A
002 B
005 C
002 D
005 E
002 F
003 G
003 H
003 I
004 J
005 K
002 L
005 M
099 N


Output is:

Code:
001 A
002 B
003 G
003 H
003 I
004 J
005 M
099 N


Where a 005 record is followed by a 002 record, the pair are dropped.

Are you asking for where a 002 record is followed by a 005?

Swapping the 002 for 005 and 005 for 002:

Code:
//SYSIN    DD *
  OPTION COPY
                                           
  INREC IFTHEN=(WHEN=GROUP,
                  BEGIN=(1,3,CH,EQ,C'002'),
                  PUSH=(6:1,5,SEQ=1),
                  RECORDS=2)
  OUTFIL OMIT=(11,1,CH,EQ,C'1',
              OR,
               1,3,CH,EQ,C'005',
                AND,
               6,3,CH,EQ,C'002'),
         IFOUTLEN=5,
         IFTHEN=(WHEN=(6,1,CH,NE,C' '),
                  BUILD=(6,5,
                         /,
                         1,5))


Output becomes:

Code:
001 A
002 F
003 G
003 H
003 I
004 J
005 K
099 N


Which seems to be what you want.

Your code is missing the SEQ on the PUSH and the OMIT where the sequence within the group is one. Then you need a BUILD with a slash operator, /, to get any remaining doubled-up records into two separate records. I used IFOUTLEN to get all records to original length.

This is only lightly tested, as your test data is not extensive.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Fri Apr 10, 2015 5:15 am    Post subject:
Reply with quote

Since you have to do multiple things here and I am sure you would like to do all of this in a single step, let's go to our multi talented friend ICETOOL.

What you want is here:
Code:
//TOOLIN DD *                                             
 COPY FROM(IN1) TO(T1) USING(CTL1)                         
 COPY FROM(IN1) TO(T1) USING(CTL2)                         
 COPY FROM(T1) TO(OUT1) USING(CTL3)                       
/*                                                         
//CTL1CNTL DD *                                           
 INREC IFTHEN=(WHEN=INIT,OVERLAY=(5:SEQNUM,2,ZD))         
 SORT FIELDS=(5,2,CH,A)                                   
 OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'002'),       
 PUSH=(7:1,3),RECORDS=2)                                   
 OUTFIL OMIT=((1,3,CH,EQ,C'002',AND,7,3,CH,EQ,C'002'),OR, 
                (1,3,CH,EQ,C'005',AND,7,3,CH,EQ,C'002')), 
 BUILD=(1,6)                                               
/*                                                         
//CTL2CNTL DD *                                           
 INREC IFTHEN=(WHEN=INIT,OVERLAY=(5:SEQNUM,2,ZD))         
 SORT FIELDS=(5,2,CH,D)                                   
 OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'005'),       
 PUSH=(7:1,3),RECORDS=2)                                   
 OUTFIL OMIT=((1,3,CH,EQ,C'005',AND,7,3,CH,EQ,C'005'),OR, 
                (1,3,CH,EQ,C'002',AND,7,3,CH,EQ,C'005')), 
 BUILD=(1,6)                                               
/*                                               
//CTL3CNTL DD *                                   
 SORT FIELDS=(5,2,CH,A)                           
 SUM FIELDS=NONE                                 
 OUTREC BUILD=(1,3)                               

Let me know if you feel happy with this ;-)

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

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Fri Apr 10, 2015 5:19 am    Post subject: Reply to: How to extract records for a specific condition on key
Reply with quote

@ Bill Woodger: Let me know your thoughts.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Apr 10, 2015 12:27 pm    Post subject: Reply to: How to extract records for a specific condition on key
Reply with quote

You think I wouldn't anyway? :-)
Back to top
View user's profile Send private message
Venkata Ramayya

New User


Joined: 03 Dec 2007
Posts: 39
Location: United States

PostPosted: Fri Apr 10, 2015 7:17 pm    Post subject:
Reply with quote

It worked. Thanks a lot Bill.

Rahul, I havent tried yours yet.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Apr 10, 2015 9:34 pm    Post subject: Reply to: How to extract records for a specific condition on key
Reply with quote

RahulG31,

If you are passing the data three times, mostly there will be a better way to do it, and if not, it will be better in a general-purpose language.

Avoid actually sorting the data unless it needs to be sorted. Which leads to avoiding SUM FIELDS=NONE unless the data has to be sorted (NB, because JNFnCNTL cannot use OUTFIL, sometimes you will SORT and SUM= to arrive at data for the join itself).
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Fri Apr 10, 2015 10:52 pm    Post subject: Reply to: How to extract records for a specific condition on key
Reply with quote

Thanks for your thoughts Bill. I knew you would ;-)
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Fri Apr 10, 2015 11:12 pm    Post subject: Reply to: How to extract records for a specific condition on key
Reply with quote

@ Bill: As you said, your control card is 'lightly tested'. So, I tried to modify the input data with consecutive 002 and it didn't work there. I had input as:
Code:
001 A
002 B
005 C
002 D
002 E
005 F
099 G
and it only gave me A and G
Code:
001 A
099 G
I think we should have also got a D in the output?

Anyways, I think Venkata would be able to handle that
.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Sat Apr 11, 2015 2:07 am    Post subject: Reply to: How to extract records for a specific condition on key
Reply with quote

Yes, they won't work if there are consecutive records with the value which starts the group.

Whether that can arise with the actual data, I don't know.

It won't be so "easy" to fix, so we may be hearing from Venkata Ramayya on this again.
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
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 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