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
 

 

SUM FIELDS=NONE with INCLUDE

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

Active User


Joined: 02 Jan 2009
Posts: 114
Location: Hyderabad

PostPosted: Mon Dec 28, 2015 12:18 pm    Post subject: SUM FIELDS=NONE with INCLUDE
Reply with quote

Hello Experts,

Would you please suggest on how this can be done. I have the below input file: Position 1 to 5 is acc number and 6 to 7 is tran code. I need to get only one record per account number. So I used sum fields=none. But requirement changed: I need to pick records selectively - if an account tran code is 01 or 02 or 51 pick any one of those records. If an account doesn't have any of these 3, then pick any record. I.e the prioritizing while removing duplicates. I know include condition is executed before sum fiels=none. So I am not getting the correct results. Please suggest how to get this.

Code:

0000100
0000101
0000102
0000103
0000178
0000278
0000279
0000280
0000281
0000282
0000350
0000351
0000359
0000378
0000399


My JCL

Code:

SORT FIELDS=(1,5,CH,A)             
SUM FIELDS=NONE                   
INCLUDE COND=((6,2,CH,EQ,C'01',OR,
              6,2,CH,EQ,C'02',OR, 
              6,2,CH,EQ,C'51'),   
              OR,                 
              (6,2,CH,NE,C'01',AND,
               6,2,CH,NE,C'02',AND,
               6,2,CH,NE,C'51'))   


Output expected is

Code:

0000101
0000278
0000351
Back to top
View user's profile Send private message

kranthikumarb

Active User


Joined: 02 Jan 2009
Posts: 114
Location: Hyderabad

PostPosted: Mon Dec 28, 2015 1:22 pm    Post subject:
Reply with quote

Please note I have a way to do it in 3 steps. But wanted to know if there is a way to do it in 1 or 2 steps.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


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

PostPosted: Mon Dec 28, 2015 1:45 pm    Post subject:
Reply with quote

It is better to have a two step solution than a single step.
Back to top
View user's profile Send private message
kranthikumarb

Active User


Joined: 02 Jan 2009
Posts: 114
Location: Hyderabad

PostPosted: Mon Dec 28, 2015 1:48 pm    Post subject:
Reply with quote

I was thinking to split the input file into 2
1. File 1 with 01, 02, 51 records, with sum fields=none
2. File 2 with sum fields=none.

then overlay record from file 1 if it is found in file 2, if not leave file 2 record as is. This seems little round about way. So, I was thinking if there is any other easy way to do it.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


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

PostPosted: Mon Dec 28, 2015 2:04 pm    Post subject:
Reply with quote

Try this

Step 1
1.Overlay when records has 01,02,51 with a value say '01' position 20
2.Sort the file based on Account asc,Position 20 desc and tran code asc

Step 2
1.Elimnate duplicates by sorting based on account
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Mon Dec 28, 2015 2:57 pm    Post subject:
Reply with quote

Pandora-Box wrote:
It is better to have a two step solution than a single step.


Any typos there? Or are you redefining "better" in some unusual way?
Back to top
View user's profile Send private message
boyti ko

New User


Joined: 03 Nov 2014
Posts: 78
Location: Malaysia

PostPosted: Mon Dec 28, 2015 3:01 pm    Post subject:
Reply with quote

Try this one. I have tested it only for the input you have given.
You may enhance it to give much better approach or if I have missed something.

Code:
//TEST     EXEC PGM=SORT                                 
//SORTIN   DD  *                                         
0000100                                                   
0000101                                                   
0000102                                                   
0000103                                                   
0000178                                                   
0000278                                                   
0000279                                                   
0000280                                                   
0000281                                                   
0000282                                                   
0000350                                                   
0000351                                                   
0000359                                                   
0000378                                                   
0000399                                                   
//SORTOUT  DD  SYSOUT=*                                   
//SYSOUT   DD  SYSOUT=*                                   
//SYSIN    DD  *                                         
  INREC IFTHEN=(WHEN=(6,2,CH,EQ,C'01'),OVERLAY=(10:C'0')),
        IFTHEN=(WHEN=(6,2,CH,EQ,C'02'),OVERLAY=(10:C'0')),
        IFTHEN=(WHEN=(6,2,CH,EQ,C'51'),OVERLAY=(10:C'0')),
        IFTHEN=(WHEN=NONE,OVERLAY=(10:C'1'))             
  SORT FIELDS=(1,5,CH,A,10,1,CH,A)                       
  OUTREC OVERLAY=(20:SEQNUM,4,ZD,START=0,RESTART=(1,5))   
  OUTFIL INCLUDE=(20,4,CH,EQ,C'0000'),BUILD=(1,7)         


The INREC and SORT used here is what I can think of on Pandora-Box's comment.
The OUTREC will put 00000 on the first instances of your 1,5
Lastly, the OUTFIL will only include all those 0000.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


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

PostPosted: Mon Dec 28, 2015 3:01 pm    Post subject:
Reply with quote

Bill,

I used better in this context as to say prefer to have 2 step than having 1 complicated step.

And Yes Boyti provided one step solution
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Mon Dec 28, 2015 3:21 pm    Post subject: Reply to: SUM FIELDS=NONE with INCLUDE
Reply with quote

kranthikumarb,

You show your data to be already in sequence.

So any solution involving SORT is absurd: wasteful of resources. Unless absolutely necessary :-)

For information, you can use MERGE for a single file if you want to insist on using SUM, but OUTFIL reporting functions REMOVECC,NODETAIL and SECTIONS with TRAILER3 will do what you wanted previously.

Can you confirm that the file is in order by the data?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1712
Location: UK

PostPosted: Mon Dec 28, 2015 3:53 pm    Post subject:
Reply with quote

Kranthikumarb,
In your first post you have a section "My JCL". In this section you have absolutely no JCL at all. What you do have is sort control cards. Please use the correct terminology. JCL is defined in the JCL language reference manual, sort control cards in the sort manual.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Mon Dec 28, 2015 10:48 pm    Post subject:
Reply with quote

I am sure Bill wouldn't advise this card but you can take a look for performance, I see EXCP is lesser for ICETOOL compared to Boyti ko card.
Code:
//STEP1  EXEC  PGM=ICETOOL                           
//TOOLMSG  DD  SYSOUT=*                             
//DFSMSG   DD  SYSOUT=*                             
//TEMP1     DD DSN=&&TEMP2,DISP=(MOD,PASS),         
//             UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
//TEMP2     DD DSN=&&TEMP2,DISP=(MOD,PASS),         
//             UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
//TEMP3     DD DSN=&&TEMP2,DISP=(MOD,PASS),         
//             UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
//TEMP4     DD SYSOUT=*                             
//IN       DD  *                                     
//TOOLIN   DD *                                                     
  SORT FROM(IN)   TO(TEMP1,TEMP2)  USING(CTL1)                       
  SORT FROM(TEMP2) TO(TEMP3)  USING(CTL2)                           
  SORT FROM(TEMP1) TO(TEMP3)  USING(CTL3)                           
  SORT FROM(TEMP3) TO(TEMP4)  USING(CTL4)                           
//CTL1CNTL DD *                                                     
  OPTION COPY                                                       
  OUTFIL FNAMES=TEMP1,INCLUDE=(6,2,CH,EQ,C'01',OR,6,2,CH,EQ,C'02',OR,
                              6,2,CH,EQ,C'51')                       
  OUTFIL FNAMES=TEMP2,SAVE                                           
//CTL2CNTL DD *                                                     
  JOINKEYS F1=TEMP2,FIELDS=(1,5,A),SORTED,NOSEQCK                   
  JOINKEYS F2=TEMP1,FIELDS=(1,5,A),SORTED,NOSEQCK                   
  JOIN UNPAIRED,F1,ONLY                                             
  REFORMAT FIELDS=(F1:1,8)                                           
  OPTION COPY                                                       
//CTL3CNTL DD *                                                     
  OPTION COPY                                                       
//CTL4CNTL DD *               
  SORT FIELDS=(1,5,CH,A)     
  SUM FIELDS=NONE             
Back to top
View user's profile Send private message
kranthikumarb

Active User


Joined: 02 Jan 2009
Posts: 114
Location: Hyderabad

PostPosted: Tue Dec 29, 2015 9:57 am    Post subject:
Reply with quote

Thanks everyone for ideas.

@Boyti ko, I am sorry. I should've specified more clearly that all the accounts may not have only 5 transactions. So, restart (1,5) may not be used. After looking at your solution, I am thinking, If I can establish groups and attach seq numbers based on groups and then filter only top row of the group just as you did. But I used begin and end group to create groups. Can we form/create a group when ever account number at position 1,5 is changed? I mean how to make all transations related to one account a group.

On the other hand, I was able to do it with ICETOOL in 1 step. Here it is.


Code:

//STEP1 EXEC PGM=ICETOOL                                 
//SORTIN   DD   DSN=INPUT.FILE,DISP=SHR   
//*                                                         
//SORTWK1  DD   DSN=&&SORTWK1,UNIT=SYSDA,SPACE=(CYL,(250,100)
//             DISP=(,PASS)                                 
//OUTPWK03 DD   DSN=*.SORTWK1,DISP=(OLD,PASS),VOL=REF=*.SORTWK1
//TOOLMSG  DD   SYSOUT=*                                     
//DFSMSG   DD   SYSOUT=*                                     
//TOOLIN   DD   *                                           
  SORT FROM(SORTIN) TO(SORTWK1) USING(CTL1)                 
  SORT FROM(OUTPWK03) TO(SORTOUT) USING(CTL2)               
//*                                                         
//CTL1CNTL DD   *                                           
 INREC IFTHEN=(WHEN=(6,2,CH,EQ,C'01',OR,6,2,CH,EQ,C'02',OR,
                     6,2,CH,EQ,C'51'),
            OVERLAY=(81:C'0'))                             
  SORT FIELDS=(1,5,CH,A,81,1,CH,D,6,2,CH,A)             
  OUTREC BUILD=(1,80)                                     
//*                                                       
//CTL2CNTL DD   *                                         
  SORT FIELDS=(1,5,CH,A)                                 
  SUM FIELDS=NONE                                         
//*                                                       
//SORTOUT  DD   SUSOUT=*
                                                       
Back to top
View user's profile Send private message
kranthikumarb

Active User


Joined: 02 Jan 2009
Posts: 114
Location: Hyderabad

PostPosted: Tue Dec 29, 2015 10:08 am    Post subject:
Reply with quote

@ Nic Clouston - Sure I will use proper wording going forward. I wanted to copy paste entire JCL initially, later I thought sort cards alone would do. That was where it went wrong :-)

But what you said is right. I should've used correct wording.

@ Bill, Data is in sorted order in the file. But, some times we may have to pick 1st record in the group, some times 2nd record. Sometimes nth record. We need to sort the data based on the priority of tran codes. If tran codes 01,02, 51 exist, pick anyone of them, if they are not present, pick anyone of the transaction. Also, as I mentioned in my previous reply, an account doesn't always have only 5 transactions. I am very interested to know how this can be achieved using the below.

Quote:

For information, you can use MERGE for a single file if you want to insist on using SUM, but OUTFIL reporting functions REMOVECC,NODETAIL and SECTIONS with TRAILER3 will do what you wanted previously.
Back to top
View user's profile Send private message
kranthikumarb

Active User


Joined: 02 Jan 2009
Posts: 114
Location: Hyderabad

PostPosted: Tue Dec 29, 2015 11:19 am    Post subject:
Reply with quote

@ boyti ko. You are correct. I have a wrong understanding on restrat=(1,5). It worked perfectly
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Tue Dec 29, 2015 11:27 am    Post subject:
Reply with quote

why do you not read manuals or other posts on sections and think of logic by yourself? why would you expect everything coded?if you're interested to learn then step one is to do research which I don't see from you.
Back to top
View user's profile Send private message
kranthikumarb

Active User


Joined: 02 Jan 2009
Posts: 114
Location: Hyderabad

PostPosted: Tue Dec 29, 2015 1:33 pm    Post subject: Reply to: SUM FIELDS=NONE with INCLUDE
Reply with quote

@ Rohit, I didn't ask for logic. I have a logic to achieve it. But I was looking for any other ways to do the same ( to reduce the coding from 2 steps to 1 step). Please see the entire thread.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


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

PostPosted: Tue Dec 29, 2015 8:52 pm    Post subject:
Reply with quote

I have seen the thread; Since I am a regular member "Sections" in DFSORT has been recently discussed, I will let you find out. The point if you are interested then why you want Bill or others to provide everything ready. You got to search on Google , read manuals or forum and find everything about "Sections", think and wrap your logic.
Quote:
to reduce the coding from 2 steps to 1 step
it is not about the steps but the # of passes that matters.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Jan 01, 2016 8:51 am    Post subject: Reply to: SUM FIELDS=NONE with INCLUDE
Reply with quote

You go away for a few days...

Design your solution first. This means, "how do I turn this input into this output". Once you've worked that out, you're 90% of the way there.

Case 1. When there are a mix of types, take any of 01, 02, 51.

Case 2. When there is only 01, 02, 51, take any of 01, 02, 51.

Case 3. Take any of the records (there are no 01, 02, 51 in this).

Because for all cases any of the relevant records can be taken, it seems sensible to decide that one particular record should be taken. Can't take the first, because you won't know in the case of the mixed. So take the last.

Taking the last record is easy, with SECTIONS and TRAILER3 with REMOVECC and NODETAIL in OUTFIL.

As long as you can identify the single group of record-types, that's fine. What about the "mixed" record-types? Only 01, 02, 51 are needed from them, so get rid of the rest with OUTFIL INCLUDE=/OMIT=.

INCLUDE for Case 1 and Case 2, all 01, 02, 51. For Case 3 all records.

Now you just need to arrange the input so that the INCLUDE will work, and you're home free.

A simple JOINKEYS, using the same dataset for both inputs. In JNF2CNTL INCLUDE just 01, 02, 51.

The key for the join is the key for the file. SORTED,NOSEQCK of course (data - assumed, not confirmed by TS/OP - in correct order).

JOIN UNPAIRED,F1

There will be up to three records on F2 for a key, so multiple REFORMAT records may be created. This does not particularly matter, as only the last will be output. Include the match-marker in the REFORMAT record.

For Case 1 and Case 2, include B (match marker) and type 01, 02, 51. For case three, include 1 (match marker).

I'm not sure what would be so complex about such a solution.

A single-step non-JOINKEYS WHEN=GROUP solution is also possible. Is it too difficult to understand? If you never try, then yes. Otherwise, no.

And yes, you can make variations to the selection.

What should always be avoided, where possible, if the data is already in order, is to re-sort. Which mostly precludes a SORT with SUM FIELDS=NONE. MERGE is available, as is SECTIONS with either HEADER3 (first record available) or TRAILER3 (last record available). There's ICETOOL's SELECT operator.

Here's a rule of thumb. If the SORT solution requires more than two passes of the data, except on rare occasions when three passes are required, then it is not a good SORT solution. If the only possible solution requires three (mostly) or more passes of the data, then write a program, including as a first consideration (if to be included in an existing SORT, then for sure) write a SORT EXIT.

Please don't post SORT "solutions" with three or more passes of the data. Those are not solutions, they are... thoughtless. They will be deleted from here.

Please don't post SORT "solutions" which SORT data which is already in order. They will be deleted from here.

Now, I think at least a couple of you could come up with a one-step WHEN=GROUP solution. Want to try? It may take some time, but breaking through on how to think about SORT solutions is well worth it.

If you want to try, come up first with some really good test-data. Because there is no reliable END to a GROUP, watch for overflow from one key to the next. Think of sequence numbers, RESTART, SEQ, multiple conditions and PUSH to multiple locations.
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
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 Access SQLCA fields inside a stored p... gangapd DB2 4 Thu Oct 27, 2016 10:20 am
No new posts INCLUDE condition questions van bui DFSORT/ICETOOL 1 Thu Jun 30, 2016 9:52 am
No new posts Need Help to Define 36 Input Fields (... satish.ms10 CLIST & REXX 2 Sat May 07, 2016 3:08 pm
No new posts FileAid to compare(logical operations... anand1204 Compuware & Other Tools 3 Wed Apr 06, 2016 2:55 am


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