MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Pick Records matching the criteria using SORT
Goto page 1, 2  Next
 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
sublak123
Beginner


Joined: 29 Jun 2017
Posts: 6
Topics: 6
PostPosted: Tue Jul 04, 2017 9:46 am    Post subject: Pick Records matching the criteria using SORT Reply with quote
Input File is Report File(FBA File) :
The report file contains data for several cities chennai,madurai,etc with multiple pages for each city.
Input file data is shown below.
Code:

HEADING :(PAGE1)
1PQ9111-001 ABCD TEST BANK 001                   
 MLS1111S   CHENNAI                                 GEN
                   TYPE 3                                     
DETAIL RECORD :
RECORD 1
RECORD 2

HEADING :(PAGE2)
1PQ9111-001 EFGH TEST BANK 001                   
 MLS1111S   CHENNAI                                 GEN
                   TYPE 3
                   STATE TOTALS                                   
DETAIL RECORD :
19541112          VEHICLE   - DEFERRED DEALER COMMISSION       25
42041117          VEHICLE   - INTEREST INCOME                  50
42041119          VEHICLE   - PAID LATE CHARGES                75

In output file, I want to pick 'VEHICLE - INTEREST INCOME' record and amount 50 should be written into particular position(say 40th position) of output file using sort. The 'VEHICLE - INTEREST INCOME' record is available for all cities but I want to pick data only for chennai city.
Can anyone plz help me in resolving this problem.
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12373
Topics: 75
Location: San Jose
PostPosted: Tue Jul 04, 2017 12:23 pm    Post subject: Reply with quote
sublak123,


Since you are a new member you need to follow a couple of rules.

1. Use meaningful topics (don't use the Forum name for the topic). Use a descriptive title to explain your problem. Post detailed information on what you're trying to accomplish. Do not make people guess what you mean. This will give you a much better chance of getting a good answer to your question. I have edited your title for now but next time try to follow the rules.

2. Use code tags to have your data retain spacing and readable. Check this link which explains in detail about code tags

http://www.mvsforums.com/helpboards/viewtopic.php?p=19031#19031

Now coming to the problem it is quite simple using SS format on INCLUDE condition
Code:

//SYSIN    DD *                                               
  OPTION COPY                                                 
  INCLUDE COND=(1,N,SS,EQ,C'VEHICLE   - INTEREST INCOME')
*** N= your input file LRECL   
//*

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
t-bonham@scc.net
Supermod


Joined: 18 Oct 2012
Posts: 30
Topics: 0
Location: Minneapolis, MN
PostPosted: Tue Jul 04, 2017 3:58 pm    Post subject: Reply with quote
Kolusu -- won't that insert the '50' for every Vehicle interst income line?
OP wanted this only for certain cities within the file. I think the 3rd line of the file has the city name.
Back to top
View user's profile Send private message Send e-mail AIM Address
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12373
Topics: 75
Location: San Jose
PostPosted: Tue Jul 04, 2017 5:46 pm    Post subject: Reply with quote
t-bonham@scc.net wrote:
Kolusu -- won't that insert the '50' for every Vehicle interst income line?
OP wanted this only for certain cities within the file. I think the 3rd line of the file has the city name.


t-bonham,

I am only including the lines that have the text "VEHICLE - INTEREST INCOME ". I do NOT reformat the contents at all.

If OP and your intention is to INCLUDE the city name then OP needs WHEN=GROUP clause and Push the city name onto the detail records. So we need to know how to identify the city Name. Probably checking for MLS1111S or may 3rd header line, but not really sure.
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54
PostPosted: Wed Jul 05, 2017 3:22 pm    Post subject: Reply with quote
Untested.. assuming 13,7(offset,length) has chennai and 2,8(offset,length) has MLS1111S
Code:
                                                             
INREC IFTHEN=(WHEN=GROUP,                                           
              BEGIN=(2,8,CH,EQ,C'MLS1111S'),                       
              PUSH=(134:13,07))                                     
OUTFIL INCLUDE=(001,133,SS,EQ,C'VEHICLE   - INTEREST INCOME',AND,   
                134,007,CH,EQ,C'CHENNAI')                           


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


Joined: 29 Jun 2017
Posts: 43
Topics: 9
PostPosted: Thu Jul 20, 2017 1:07 pm    Post subject: Reply with quote
My requirement is same as above but I need all the detail record info in single line record as below
Code:

Madras     19541112    25  42041117   50 42041119    75
Bangalore 19541112    15  42041117   40 42041119    35
Cochin      19541112    35  42041117   30 42041119   55
Back to top
View user's profile Send private message
Suchay
Beginner


Joined: 29 Jun 2017
Posts: 43
Topics: 9
PostPosted: Thu Jul 20, 2017 1:12 pm    Post subject: Reply with quote
Tried below but I get multiple records for CHennai, Bangalore etc..
Code:

INREC IFTHEN=(WHEN=GROUP,                         
              BEGIN=(2,10,CH,EQ,C'PQ9111-001'),   
              PUSH(158:112,2)),                   month field
                                                 
      IFTHEN=(WHEN=GROUP,                         
              BEGIN=(02,8,CH,EQ,C'MLS1111S',AND, 
                     13,1,CH,NE,C' '),           
              PUSH(134:2,8,144:13,14)),           
                                                 
      IFTHEN=(WHEN=GROUP,                         
              BEGIN=(02,8,SS,EQ,C'19541112'),     
              PUSH(160:093,14,202:2,8)),         
                                                 
      IFTHEN=(WHEN=GROUP,                         
              BEGIN=(02,8,CH,EQ,C'42041117'),     
              PUSH(174:093,14,210:2,8)),     
         IFTHEN=(WHEN=GROUP,                                           
                 BEGIN=(02,8,CH,EQ,C'42041119'),                       
                 PUSH(188:093,14,218:2,8))                             
                                                                       
 OUTFIL INCLUDE=(134,8,CH,EQ,C'MLS9039S',AND,           
                 202,8,CH,EQ,C'19541112',AND,           
                 210,8,CH,EQ,C'42041117',AND,           
                 218,8,CH,EQ,C'42041119'),             
                                                       
 IFTHEN=(WHEN=INIT,BUILD=(01X,144,02,                   
               02X,144,14,                             
               02X,160,14,UFF,EDIT=(IIIIIIIIIT.TT),     
               02X,174,14,UFF,EDIT=(IIIIIIIIIT.TT),     
               02X,188,14,UFF,EDIT=(IIIIIIIIIT.TT),     
               02X,158,02))
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12373
Topics: 75
Location: San Jose
PostPosted: Thu Jul 20, 2017 1:19 pm    Post subject: Reply with quote
Suchay wrote:
My requirement is same as above but I need all the detail record info in single line record as below
Code:

Madras     19541112    25  42041117   50 42041119    75
Bangalore 19541112    15  42041117   40 42041119    35
Cochin      19541112    35  42041117   30 42041119   55


What is your input? Where are you picking these values from?
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Suchay
Beginner


Joined: 29 Jun 2017
Posts: 43
Topics: 9
PostPosted: Thu Jul 20, 2017 1:21 pm    Post subject: Reply with quote
Same report submitted by sublak123 (Posted: Tue Jul 04, 2017)
Back to top
View user's profile Send private message
Suchay
Beginner


Joined: 29 Jun 2017
Posts: 43
Topics: 9
PostPosted: Thu Jul 20, 2017 2:02 pm    Post subject: Reply with quote
Code:

HEADING :(PAGE1)
1PQ9111-001 ABCD TEST BANK 001                   
 MLS1111S   MADRAS                                GEN
                   TYPE 3                                     
DETAIL RECORD :
RECORD 1
RECORD 2

HEADING :(PAGE2)
1PQ9111-001 EFGH TEST BANK 001                   
 MLS1111S   MADRAS                                GEN
                   TYPE 3
                   STATE TOTALS                                   
DETAIL RECORD :
19541112          VEHICLE   - DEFERRED DEALER COMMISSION       25
42041117          VEHICLE   - INTEREST INCOME                  50
42041119          VEHICLE   - PAID LATE CHARGES                75


HEADING :(PAGE1)
1PQ9111-001 ABCD TEST BANK 001                   
 MLS1111S   Bangalore                                 GEN
                   TYPE 3                                     
DETAIL RECORD :
RECORD 1
RECORD 2

HEADING :(PAGE2)
1PQ9111-001 EFGH TEST BANK 001                   
 MLS1111S   Bangalore                                 GEN
                   TYPE 3
                   STATE TOTALS                                   
DETAIL RECORD :
19541112          VEHICLE   - DEFERRED DEALER COMMISSION       15
42041117          VEHICLE   - INTEREST INCOME                  40
42041119          VEHICLE   - PAID LATE CHARGES                35


HEADING :(PAGE1)
1PQ9111-001 ABCD TEST BANK 001                   
 MLS1111S   Cochin                                 GEN
                   TYPE 3                                     
DETAIL RECORD :
RECORD 1
RECORD 2

HEADING :(PAGE2)
1PQ9111-001 EFGH TEST BANK 001                   
 MLS1111S   Cochin                                 GEN
                   TYPE 3
                   STATE TOTALS                                   
DETAIL RECORD :
19541112          VEHICLE   - DEFERRED DEALER COMMISSION       35
42041117          VEHICLE   - INTEREST INCOME                  30
42041119          VEHICLE   - PAID LATE CHARGES                55




Above is the report and we pickup the values for each city for Accounts 19541112
42041117
42041119
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12373
Topics: 75
Location: San Jose
PostPosted: Thu Jul 20, 2017 2:36 pm    Post subject: Reply with quote
Suchay,

Use the following untested DFSORT control cards which will give you the desired results. Based on your latest data it seems that you have the accounts '19541112, 42041117 and 42041119 ) in position 1. If they are not then change the positions on the IFTHEN statements accordingly.

Also you show the numeric data as whole numbers, but your final output shows it with decimal data. Is that necessary?


Code:

//SYSIN    DD *                                             
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(2,8,CH,EQ,C'MLS1111S'),   
                PUSH=(134:13,14)),                         
        IFTHEN=(WHEN=(1,8,CH,EQ,C'19541112'),               
             OVERLAY=(150:93,14)),                         
        IFTHEN=(WHEN=(1,8,CH,EQ,C'42041117'),               
             OVERLAY=(165:93,14)),                         
        IFTHEN=(WHEN=(1,8,CH,EQ,C'42041119'),               
             OVERLAY=(180:93,14))                           
                                                           
  SORT FIELDS=(134,14,CH,A)                                 
                                                           
  OUTFIL REMOVECC,NODETAIL,                                 
  INCLUDE=(134,14,CH,GT,C' '),                             
  SECTIONS=(134,14,                                         
  TRAILER3=(134,14,                                         
            2X,                                             
            '19541112',                                     
            2X,                                             
            TOT=(150,14,UFF,EDIT=(IIIIIIIIIT.TT)),         
            2X,                                             
            '42041117',                                     
            2X,                                             
            TOT=(165,14,UFF,EDIT=(IIIIIIIIIT.TT)),         
            2X,                                             
            '42041119',                                     
            2X,                                             
            TOT=(180,14,UFF,EDIT=(IIIIIIIIIT.TT)),         
            2X))                                           
//*

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Suchay
Beginner


Joined: 29 Jun 2017
Posts: 43
Topics: 9
PostPosted: Thu Jul 20, 2017 3:10 pm    Post subject: Reply with quote
Thanks Kolusu, code worked fine. Very Happy
Back to top
View user's profile Send private message
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54
PostPosted: Thu Jul 20, 2017 3:42 pm    Post subject: Reply with quote
kolusu wrote:

Code:

SORT FIELDS=(134,14,CH,A)



Why do we need a sort ? I think OPTION COPY would be sufficient.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12373
Topics: 75
Location: San Jose
PostPosted: Thu Jul 20, 2017 3:47 pm    Post subject: Reply with quote
Magesh_J wrote:
Why do we need a sort ? I think OPTION COPY would be sufficient.


Magesh_J,

How are you planning to get a summary record for each city? What happens if you happen to have another Bangalore after Cochin? or for any of the city if it is repeated later?

If OP confirms that the city names occur only once then he can replace the SORT FIELDS with OPTION COPY
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Magesh_J
Intermediate


Joined: 21 Jun 2014
Posts: 259
Topics: 54
PostPosted: Thu Jul 20, 2017 4:08 pm    Post subject: Reply with quote
kolusu wrote:

How are you planning to get a summary record for each city? What happens if you happen to have another Bangalore after Cochin? or for any of the city if it is repeated later?


Oh yes, you are correct.. Thanks

Regards,
Magesh
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group