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
 

 

SORT to eliminate one record based on other records

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

New User


Joined: 11 Oct 2005
Posts: 62
Location: India

PostPosted: Tue Jun 07, 2011 10:34 am    Post subject: SORT to eliminate one record based on other records
Reply with quote

Hi

I've data in the below batch card format. Data for different country starts with 'SAC'. Each batch within SAC starts with 120 record and ends with '128'. 123-1 is invoice header and 123-2 is invoice amount detail line. I need to sort the file such that if 123-2 has amount zeroes, both 123-1 and 123-2 should be dropped. Please help on this.

SAC XXXX
120 batch header
123-1 Invoice1
123-2 AMT - 99999
123-1 Invoice 2
123-2 AMT - 00000
123-1 Invoice 3
123-2 AMT - 68293
128

Note: One SAC may have multiple batches(120 thru 128) and one file may have multiple SAC cards(SAC thru 128)

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

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Tue Jun 07, 2011 12:38 pm    Post subject:
Reply with quote

Do you really want to SORT the file (as in re-sequence it) or just use the SORT-MAGIC?

Quote:
such that if 123-2 has amount zeroes, both 123-1 and 123-2 should be dropped


Here you mean only the specific 123-1 that is related to the zero 123-2? I ask, because then the requirement becomes:

Code:

123-1 Invoice 2
123-2 AMT - 00000


"When I see 123-2 with 'AMT - 00000' I want to delete it and the 123-1 immediately in front of it. All other data is to remain, and remain in the same sequence" or something like that?

You talk of "invoice line" but there is only one 123-2 in any or your examples. Can there be multiple 123-2's? If so, what if one of those is zero? You have "country", you say, are all the amounts in one currency? Why no decimal places on the amount?
Back to top
View user's profile Send private message
srinut123

New User


Joined: 11 Oct 2005
Posts: 62
Location: India

PostPosted: Tue Jun 07, 2011 2:21 pm    Post subject:
Reply with quote

Bill,

123-1 and 123-2 lines belongs to one invoice but Invoice amount is available in 123-2. If Invoice amount is zero, then I want to skip 123-2 and its corresponding 123-1(that is the previous record in the file).

I gave the sample amount. The actual format is s9(14)v9(02) ebcdic.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Tue Jun 07, 2011 2:32 pm    Post subject:
Reply with quote

srinut123,

Can you answer the questions I asked?
Back to top
View user's profile Send private message
srinut123

New User


Joined: 11 Oct 2005
Posts: 62
Location: India

PostPosted: Tue Jun 07, 2011 2:46 pm    Post subject:
Reply with quote

Bill

See my answers below..
1. Do you really want to SORT the file (as in re-sequence it) or just use the SORT-MAGIC?

I dont want the file to be sorted. I want to use SORT for eliminating unwanted records. So the sequence in the file should be retained.

2. Here you mean only the specific 123-1 that is related to the zero 123-2?
Yes. When 123-2 has zero amount, both 123-1 and 123-2 should be omitted

3. "When I see 123-2 with 'AMT - 00000' I want to delete it and the 123-1 immediately in front of it. All other data is to remain, and remain in the same sequence" or something like that?

Yes

4. You talk of "invoice line" but there is only one 123-2 in any or your examples. Can there be multiple 123-2's

No. one 123-1 will have only one 123-2 record.

5. You have "country", you say, are all the amounts in one currency?
Yes, all amounts under one SAC will have same currency

6. Why no decimal places on the amount?

It will be in the format s9(14)v9(02).

Let me know if you need any other information..

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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Tue Jun 07, 2011 2:59 pm    Post subject:
Reply with quote

Code:

123-1 Invoice 2
123-2 AMT - 0000000000000000


"When I see 123-2 with 'AMT - 0000000000000000' I want to delete it and the 123-1 immediately in front of it. All other data is to remain, and remain in the same sequence".

Is the amount "display" or "packed" or even "binary"? Ie, when you look at the file does it show zeros like above? Is the position of the zeros correct? What are the RECFM and LRECL of your file? Or are you going to deal with all those after a basic example?

Then the "sort wizards" can let rip.
Back to top
View user's profile Send private message
srinut123

New User


Joined: 11 Oct 2005
Posts: 62
Location: India

PostPosted: Tue Jun 07, 2011 3:40 pm    Post subject:
Reply with quote

Bill,

Zero amount looks like '000000000000000{'. Some times it might come as '0000000000000000' as well.

RECFM=FB
LRECL=80
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1445
Location: Azeroth

PostPosted: Tue Jun 07, 2011 4:08 pm    Post subject:
Reply with quote

Hello,
You could try the below job for the shown input and output records. It uses two passes though.

Code:
//JS020    EXEC PGM=ICETOOL                                             
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//SORTIN   DD DISP=SHR,DSN=WELLS.SORTIN                               
//TEMP     DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)       
//SORTOUT  DD DSN=WELLS.SORTOUT,DISP=OLD                             
//SYSOUT   DD SYSOUT=*                                                 
//TOOLIN   DD *                                                         
  COPY FROM(SORTIN) TO(TEMP) USING(CTL1)                               
  COPY FROM(TEMP) TO(SORTOUT) USING(CTL2)                               
/*                                                                     
//CTL1CNTL   DD *                                                       
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'SAC'),                   
   PUSH=(81:5,4,90:SEQ=8)),                                             
  IFTHEN=(WHEN=(1,5,CH,EQ,C'123-2',AND,13,5,CH,EQ,C'00000'),           
   OVERLAY=(100:C'Y')),                                                 
  IFTHEN=(WHEN=(1,5,CH,EQ,C'123-2',AND,13,5,CH,NE,C'00000'),           
   OVERLAY=(100:C'N'))                                                 
   OUTREC IFTHEN=(WHEN=GROUP,RECORDS=2,BEGIN=(1,5,CH,EQ,C'123-2'),     
   PUSH=(105:100,1))                                                   
  SORT FIELDS=(81,4,CH,A,90,8,CH,D)                                     
/*                                                                     
//CTL2CNTL   DD *                                                       
  SORT FIELDS=(81,4,CH,A,90,8,CH,A)                                     
  INCLUDE COND=(105,1,CH,NE,C'Y')                                       
  OUTREC BUILD=(1,80)                                                   
//SYSUDUMP DD SYSOUT=*                                                 
/*                     


INPUT:
SAC 2222         
120 BATCH HEADER 
123-1 INVOICE1   
123-2 AMT - 99999
123-1 INVOICE 2   
123-2 AMT - 00000
123-1 INVOICE 3   
123-2 AMT - 68293
128               
SAC 1111         
120 BATCH HEADER 
123-1 INVOICE1   
123-2 AMT - 99999
123-1 INVOICE 2   
123-2 AMT - 00000
123-1 INVOICE 3   
123-2 AMT - 68293
128               

OUTPUT:
SAC 1111         
120 BATCH HEADER
123-1 INVOICE1   
123-2 AMT - 99999
123-1 INVOICE 3 
123-2 AMT - 68293
128             
SAC 2222         
120 BATCH HEADER
123-1 INVOICE1   
123-2 AMT - 99999
123-1 INVOICE 3 
123-2 AMT - 68293
128                                                           


Hope it helps.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue Jun 07, 2011 9:44 pm    Post subject: Reply to: SORT TO ELEMINATE ONE RECORD BASED ON THE OTHER RE
Reply with quote

vasanthz,

Please don't complicate a simple request with 2 passes of sorting when you just need 1 pass of data with a copy operation. Keep it Simple.

srinut123,

The following DFSORT JCL will give you the desired results. I assumed that your input is FB recfm and LRECL=80. The amount field on 123-2 record starts at pos 13 for 5 btyes and you want to eliminate if it is -0 or +0.

Code:

//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD *                                                     
SAC 2222                                                             
120 BATCH HEADER                                                     
123-1 INVOICE1                                                       
123-2 AMT - 99999                                                   
123-1 INVOICE 2                                                     
----+----1----+----2----+----3----+----4----+----5----+----6----+----
123-2 AMT - 00000                                                   
123-1 INVOICE 3                                                     
123-2 AMT - 68293                                                   
128                                                                 
SAC 1111                                                             
120 BATCH HEADER                                                     
123-1 INVOICE1                                                       
123-2 AMT - 99999                                                   
123-1 INVOICE 2                                                     
123-2 AMT - 0000{                                                   
123-1 INVOICE 3                                                     
123-2 AMT - 68293                                                   
128                                                                 
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  OPTION COPY,NOSZERO                                               
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,5,CH,EQ,C'123-1'),RECORDS=2,     
  PUSH=(81:1,80))                                                   
  OUTFIL IFOUTLEN=80,                                               
  OMIT=(1,5,CH,EQ,C'123-1',OR,(1,5,CH,EQ,C'123-2',AND,13,5,ZD,EQ,0)),
  IFTHEN=(WHEN=(1,5,CH,EQ,C'123-2'),BUILD=(81,80,/,1,80))           
//*
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Jun 07, 2011 11:30 pm    Post subject:
Reply with quote

Kolusu beat me to it. However, the OP said:

Quote:
It will be in the format s9(14)v9(02).
and
Quote:
Zero amount looks like '000000000000000{'. Some times it might come as '0000000000000000' as well.


So it appears the amount would be 13,16,ZD. Here's the DFSORT job I came up with (before I saw Kolusu's solution) with that assumption:

Code:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
SAC 2222
120 BATCH HEADER
123-1 INVOICE1
123-2 AMT - 0000000000099999
123-1 INVOICE 2
123-2 AMT - 0000000000000000
123-1 INVOICE 3
123-2 AMT - 0000000000068293
128
SAC 1111
120 BATCH HEADER
123-1 INVOICE1
123-2 AMT - 0000000000099999
123-1 INVOICE 2
123-2 AMT - 0000000000000000
123-1 INVOICE 3
123-2 AMT - 0000000000068293
128
/*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
  OPTION COPY,NOSZERO
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,5,CH,EQ,C'123-1'),
    RECORDS=2,PUSH=(81:1,80))
  OUTFIL OMIT=(1,5,CH,EQ,C'123-1',OR,
    (1,5,CH,EQ,C'123-2',AND,13,16,ZD,EQ,0)),
   IFOUTLEN=80,
   IFTHEN=(WHEN=(1,5,CH,EQ,C'123-2'),
     BUILD=(81,80,/,1,80))
/*
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jun 07, 2011 11:32 pm    Post subject: Reply to: SORT TO ELEMINATE ONE RECORD BASED ON THE OTHER RE
Reply with quote

Hi vasanthz,

This is way off-topic and can later be deleted. . .

2 new avatars today?

heh heh heh,

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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Wed Jun 08, 2011 4:07 am    Post subject: Reply to: SORT TO ELEMINATE ONE RECORD BASED ON THE OTHER RE
Reply with quote

Never thought I'd get the chance to query either Frank or Kolusu. Yet, not only on the same day, but the same topic :-)

Problem is, the zero is not a case of + and -, but + and unsigned. the "{" is positive zero (matching TS's s9(14)v9(02)), checked on dbz's STICKY in the Cobol forum, and just now on my trusty "yellow card".

From an eminent tome:
Quote:

NOSZERO
specifies that DFSORT should treat numeric zero values as unsigned. −0
and +0 are treated as the same value, that is, −0 and +0 are both treated as
positive values. NOSZERO affects DFSORT processing of numeric values
as follows:
For collation of SORT and MERGE fields, −0 collates equally with +0.
For comparisons of INCLUDE, OMIT and OUTFIL compare fields and
constants, −0 compares as equal to +0.
For editing and conversions of INREC, OUTREC, and OUTFIL
reformatting fields, decimal constants, and the results of arithmetic
expressions, −0 and +0 are treated as positive.
For minimums and maximums of OUTFIL TRAILERx fields, −0 and +0
are treated as positive.


Which doesn't, excplicitly, cover the TS's unusual situation. An unsigned zero (F0) and a positive zero (C0). I have searched the Application Programming Guide, but apart from something in a sideways table at the back (which is only a possible-maybe), there is nothing to indicate if +0 also covers the unsigned zero, but if so it would be nice if it were mentioned in the above text for clarity.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Jun 08, 2011 4:29 am    Post subject: Reply to: SORT TO ELEMINATE ONE RECORD BASED ON THE OTHER RE
Reply with quote

Bill Woodger,

Read the notes under DFSORT Data Formats for ZD Format
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1445
Location: Azeroth

PostPosted: Wed Jun 08, 2011 10:54 am    Post subject:
Reply with quote

Quote:
Please don't complicate a simple request with 2 passes of sorting

True I overkilled it. Thanks for educating with the optimum/best solution. Someone told that "there can't be interesting thoughts, if there were no stupid thoughts", my solution belonged to the latter category (:

Quote:
2 new avatars today?
Ya D, He is Rajnikanth a great actor, he is on bad health icon_sad.gif
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Wed Jun 08, 2011 12:14 pm    Post subject: Re: Reply to: SORT TO ELEMINATE ONE RECORD BASED ON THE OTHE
Reply with quote

Skolusu wrote:
Bill Woodger,

Read the notes under DFSORT Data Formats for ZD Format


Got it. Will work a treat.

What took me away was the specific focus on +0, -0 without reference to the deeper meaning of these in the ZD description. I searched every reference to "unsigned" thinking that might get me somewhere.

I got my query answered anyway.
Back to top
View user's profile Send private message
srinut123

New User


Joined: 11 Oct 2005
Posts: 62
Location: India

PostPosted: Wed Jun 08, 2011 12:26 pm    Post subject:
Reply with quote

Thanks for every one. I tried the one pass Sort JCL and it gave me the results as expected. It worked for both numbers ending with an EBCDIC and non EBCDIC value.

Many thanks ..

Regards,
Sri
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Wed Jun 08, 2011 12:30 pm    Post subject: Reply to: SORT to eliminate one record based on other record
Reply with quote

srinut123,

Don't forget about the "problem" with the file. A mix of signed and unsigned data in a field defined in a Cobol program may not work and is not good practice.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Jun 08, 2011 9:00 pm    Post subject:
Reply with quote

As a side note, Kolusu and I discussed the +0, -0 situation offline. I felt there was no indication that -0 could be present. He suggested covering it anyway, so we threw in NOSZERO just in case. If only +0 has to be covered, then ',NOSZERO' can be removed.
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
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 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


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