Sortsum Records on a condition or else copy.



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Sortsum Records on a condition or else copy.

Postby DNair7 » Tue Jul 14, 2009 12:00 pm

Hi,
I have a problem here....

i have one input file and one output file.

I have written a sort to sortsum the amounts for a account number (if any duplicates).
Now i also have a special case where the account number will be zero and there may be n number of records of this type.

can you please suggest a sort, where i can sortsum the amount of all the non-zero account number and copy the Zero account number records as it is in the output file.

Many Thanks,
Deepak
DNair7
 
Posts: 7
Joined: Tue Jul 14, 2009 11:48 am
Has thanked: 0 time
Been thanked: 0 time

Re: Sortsum Records on a condition or else copy.

 

Re: Sortsum Records on a condition or else copy.

Postby DNair7 » Tue Jul 14, 2009 12:01 pm

Hi,

Below is the sort i have written,,,

  SORT FIELDS=(10,6,CH,A,16,3,CH,A)   
  INCLUDE COND=(1,3,CH,EQ,C'073')     
  SUM FIELDS=(69,16,ZD)
DNair7
 
Posts: 7
Joined: Tue Jul 14, 2009 11:48 am
Has thanked: 0 time
Been thanked: 0 time

Re: Sortsum Records on a condition or else copy.

Postby Mahalakshmi R » Tue Jul 14, 2009 6:11 pm

Hi,

Use the below code....

//STEP01        EXEC PGM=SORT                                 
//SYSOUT      DD SYSOUT=*                                   
//SORTIN        DD DSN=TEST.FILE1,DISP=SHR                   
//SORTOUT  DD = *                                   
//SYSIN    DD *                                           
  INREC IFTHEN=(WHEN=(1,1,CH,EQ,C'0'),                   
  OVERLAY=(81:SEQNUM,1,ZD),HIT=NEXT),                     
  IFTHEN=(WHEN=(1,1,CH,NE,C'0'),OVERLAY=(81:C' '))       
                                                         
  SORT FIELDS=(1,1,CH,A,81,1,CH,A)                       
  SUM FIELDS=NONE                                         
  OUTREC FIELDS=(1,80)                                   
/*     


The Input file TEST.FILE1 like this
0
1
5
0
4
4
2
3
6
2
7
8
5

The out put file TEST.FILE2 will be
0
0
1
2
3
4
5
6
7
8

Thanks,
Mahalakshmi.
Thanks,
Mahalakshmi Rajendran
Mahalakshmi R
 
Posts: 13
Joined: Mon Jul 13, 2009 4:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Sortsum Records on a condition or else copy.

Postby Frank Yaeger » Tue Jul 14, 2009 8:20 pm

Deepak,

It's not clear to me what you want to do exactly. If you need more help with this, please show an example of the records in your input file (relevant fields only) and what you expect for output. Explain the "rules" for getting from input to output. Give the RECFM and LRECL of the input file. Give the starting position, length and format of each relevant field.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: Sortsum Records on a condition or else copy.

Postby DNair7 » Wed Jul 15, 2009 11:40 am

Hi Mahalakshmi/ frank,
First of all Many Thanks for your response, i tried the sort but has not worked for me. Could you please explain me the use of overlay here, and why the 81:SEQNUM was used and for waht, may i try then.

below is my requirement,

I have got a input file 600 Record length in FB format. This input file has below field:-

01  QIOA-WLKR-ACCT-DTL-REC.                               
    03 QIOA-ACCT-KY.                                       
        05 QIOA-UNIT-NO                   PIC XXX.         
        05 QIOA-CTR-CD                    PIC X(6).       
        05 QIOA-WLKR-ACCT.                                 
            07 QIOA-WLKR-ACCT-NO          PIC X(6).       
            07 QIOA-WLKR-SUB-ACCT-NO      PIC XXX.         
    03 QIOA-HI-LVL-ACCT-DESC              PIC X(50).       
    03 QIOA-ACCT-BAL-AMT                  PIC S9(14)V99.   
    03 QIOA-CLASN-LVL                     OCCURS 5 TIMES. 
        05 QIOA-CLASN-LVL-NO              PIC XX.         
        05 QIOA-CLASN-LVL-NM              PIC X(50).       
    03 QIOA-LGL-INVT-VOCE-NO              PIC X(5).       
    03 QIOA-LGL-INVT-SUB-VOCE-NO          PIC X(5).       
    03 QIOA-BNK-ITAL-VOCE-NO              PIC X(5).       
    03 QIOA-BNK-ITAL-SUB-VOCE-NO          PIC XX.         
    03 QIOA-SECT-NM                       PIC XX.         
    03 QIOA-LGL-INVT-SEQ-NO               PIC X(4).       
    03 QIOA-LGL-INVT-DESC                 PIC X(50).       
    03 QIOA-SPE-SEQ-NO                    PIC XX.         
    03 QIOA-SPE-DESC                      PIC X(50).       
    03 QIOA-BNK-ITAL-DESC                 PIC X(50).       
    03 QIOA-FIN-STMT-TYP-NM               PIC X(17).       
    03 QIOA-FIN-STMT-CD                   PIC X(4).       
    03 QIOA-FIN-STMT-DESC                 PIC X(50).       
    03 FILLER                             PIC X(10).       


Now the above file can have N number of QIOA-WLKR-ACCT's. Here there may be duplicate QIOA-WLKR-ACCT's.So my requirement here is if there are any duplicate QIOA-WLKR-ACCTs then it should Sortsum the QIOA-ACCT-BAL-AMT field for those account and create a single record for every account in the output.

But here there is one more scenario i.e, i may get QIOA-WLKR-ACCT as zeros (Character), In this case even if there are 'N' number of Zero account, it should not sortsum the QIOA-ACCT-BAL-AMT field for this account.

Altogether there can be duplicate Zeros account number records in the output file.

The problem is i need to do this using one sort only,, can you please help me on this.
Hope i am clear with the need...

Thanks in Advance,
Deepak
DNair7
 
Posts: 7
Joined: Tue Jul 14, 2009 11:48 am
Has thanked: 0 time
Been thanked: 0 time

Re: Sortsum Records on a condition or else copy.

Postby Mahalakshmi R » Wed Jul 15, 2009 12:14 pm

Hi Deepak,

The example which i shown you has the input file with the record length of 80. the 1st column which i have taken as account no.

I have set the 81:SEQNUM as '0' when the account no is '0' and set SPACE for non zero accounts.
(i think you can use 601th coloumn for this).
this 81th column specified for the temporary file(internal) which is used by the sort utility.

finally while sorting i have sorted the file using account no as well as the 81th column.

In the out put file ill get only the rec length 80 because of the statement

OUTREC FIELDS=(1,80).

//SORTOUT DD = * here, you can give you output file.

Thanks,
Mahalakshmi.
Thanks,
Mahalakshmi Rajendran
Mahalakshmi R
 
Posts: 13
Joined: Mon Jul 13, 2009 4:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Sortsum Records on a condition or else copy.

Postby Mahalakshmi R » Wed Jul 15, 2009 12:50 pm

Hi Deepak,

find the below code.

//STEP01 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=TEST.FILE3,DISP=SHR
//SORTOUT DD DSN=TEST.FILE4,
// DISP=(OLD,CATLG,DELETE),
// DCB=(DSORG=PS,RECFM=FB,LRECL=600,BLKSIZE=0),
// SPACE=(CYL,(5,1)),
// UNIT=DISK
//SYSIN DD *
INREC IFTHEN=(WHEN=(1,1,CH,EQ,C'0'),
OVERLAY=(601:SEQNUM,1,ZD),HIT=NEXT),
IFTHEN=(WHEN=(1,1,CH,NE,C'0'),OVERLAY=(601:C' '))

SORT FIELDS=(1,1,CH,A,601,1,CH,A)
SUM FIELDS=NONE
OUTREC FIELDS=(1,600)
/*

Try this...
Thanks,
Mahalakshmi Rajendran
Mahalakshmi R
 
Posts: 13
Joined: Mon Jul 13, 2009 4:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Sortsum Records on a condition or else copy.

Postby Frank Yaeger » Wed Jul 15, 2009 9:36 pm

Dnair1,

It really would have helped if you had supplied all of the information I asked for including an example of your input records and expected output. Since you didn't, I have to guess. It appears that your account field is 10,9,CH and your amount field is 69,16,ZD. So the relevant fields in your input records might look like this:

         QIOA-WLKR-ACCT                                             QIOA-ACCT-BAL-AMT
R1       123456123                                                  1111111111111111
R2       123456123                                                  2222222222222222
R3       123456123                                                  3333333333333333
R4       000000000                                                  1000000000000010
R5       333333333                                                  1111111111111111
R6       000000000                                                  1000000000000020
R7       222222222                                                  1000000000000005
R8       000000000                                                  1000000000000030
R9       333333333                                                  2222222222222222


Based on what you said, I assume your expected output would be:

R4       000000000                                                  1000000000000010
R6       000000000                                                  1000000000000020
R8       000000000                                                  1000000000000030
R1       123456123                                                  6666666666666666
R7       222222222                                                  1000000000000005
R5       333333333                                                  3333333333333333


If my assumptions are correct, then you can use a DFSORT job like the following to do what you asked for. If my assumptions are not correct, you need to give me the correct information.

//S1    EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=...  input file (FB/600)
//SORTOUT DD DSN=...  output file (FB/600)
//SYSIN    DD    *
  OPTION EQUALS
  INREC IFTHEN=(WHEN=(10,9,ZD,EQ,0),OVERLAY=(601:SEQNUM,8,ZD))
  SORT FIELDS=(10,9,CH,A,601,8,ZD,A)     
  SUM FIELDS=(69,16,ZD)
  OUTREC BUILD=(1,600)
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: Sortsum Records on a condition or else copy.

Postby Frank Yaeger » Wed Jul 15, 2009 9:37 pm

Mahalakshmi Rajendran,

If you must post "solutions" in this Forum, please use ubb tags and pay attention to the information given or ask for clarification. The account number here is not in position 1 and an amount needs to be summed, so SUM FIELDS=NONE won't do it.

We have two DFSORT developers answering questions in this Forum, so we really don't need your "help".
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: Sortsum Records on a condition or else copy.

Postby Mahalakshmi R » Thu Jul 16, 2009 11:34 am

Frank,

Thanks for giving the exact solution and for the modern syntaxes.

The account number here is not in position 1

Of course, the account no is not in position 1 at the query asked by Deepak.
But it is in first position at the example which i have shown in earlier posts.

an amount needs to be summed, so SUM FIELDS=NONE won't do it.

Of course it will not do it. But my example is to eliminate the duplicates and not summing any field.

Here,I would like to share the knowledge which i have learnt by solving the similar kind of issues. And that may help the others to learn and explore.

Please correct me if i'm wrong.
Thanks,
Mahalakshmi Rajendran
Mahalakshmi R
 
Posts: 13
Joined: Mon Jul 13, 2009 4:50 pm
Has thanked: 0 time
Been thanked: 0 time

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post