divide record into multiple records based upon conditions



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

divide record into multiple records based upon conditions

Postby k singh » Wed Jun 08, 2016 8:12 pm

Hi

I need to break a record into multiple records based upon conditions.
My input data looks like this
key1    key2    amt_a   amt_b   amt_c   amt_d
1-10    11-20   21-30   31-40   41-50   51-60
=============================================
11      2       0       0       0       500
11      3       100     200     450     900
12      1       0       0       0       0
20      11      0       110     0       0
30      9       0       0       80      0
 


I want my output in following format.
key1    key2    Amt     code
11,2,500,d
11,3,100,a
11,3,200,b
11,3,450,c
11,3,900,d
20,11,110,b
30,9,80,c


I am trying to do the same in the jcl using sort with below logic but it does not works.
//SYSIN    DD *          
  INREC  IFTHEN=(WHEN=(21,10,ZD,GT,0),BUILD=(1,10,C',',11,10,C',',21,10,C',',C'A'),
         IFTHEN=(WHEN=(31,10,ZD,GT,0),BUILD=(1,10,C',',11,10,C',',31,10,C',',C'B'),
         IFTHEN=(WHEN=(41,10,ZD,GT,0),BUILD=(1,10,C',',11,10,C',',41,10,C',',C'C'),
         IFTHEN=(WHEN=(51,10,ZD,GT,0),BUILD=(1,10,C',',11,10,C',',51,10,C',',C'D'))
         
  OPTION COPY

I do have the code to create multiple output files but I need only one file.
please advice
k singh
 
Posts: 22
Joined: Fri Nov 21, 2014 8:38 pm
Has thanked: 9 times
Been thanked: 0 time

Re: divide record into multiple records based upon conditions

 

Re: divide record into multiple records based upon condition

Postby Robert Sample » Wed Jun 08, 2016 10:16 pm

I am trying to do the same in the jcl using sort with below logic but it does not works.
Posting something "does not work" or "does not works (sic)" is completely and utterly useless. Saying it did not work could mean (among other interpretations):
- code failed completely causing SORT abend
- code failed but SORT did not abend
- code generated output but not what you expected
- code did not generate output but SORT completed
- code generated output in incorrect locations
- code generated output in correct locations but in multiple data sets
- code started generating output but then SORT had an issue and stopped processing
etc
etc
etc

If you had posted something like "code is generating 4 output records per input record when I only expected the non-zero records to be generated" (or whatever actually happened) -- that would tell us something.
Robert Sample
Global moderator
 
Posts: 3202
Joined: Sat Dec 19, 2009 8:32 pm
Location: East Dubuque, Illinois
Has thanked: 1 time
Been thanked: 196 times

Re: divide record into multiple records based upon condition

Postby Terry Heinze » Wed Jun 08, 2016 10:56 pm

k singh,
Please post in the appropriate forum.
.... Terry
Terry Heinze
 
Posts: 178
Joined: Wed Dec 04, 2013 11:08 pm
Location: Richfield, MN, USA
Has thanked: 10 times
Been thanked: 11 times

Re: divide record into multiple records based upon condition

Postby BillyBoyo » Thu Jun 09, 2016 1:21 am

If your input is fixed-length, use ICETOOL's RESIZE operator. Use a USING and in that dataset, use INREC to format five consecutive pieces of data each prefixed with the key. The length of those gives you your size to RESIZE to, and the total is the RESIZE from. Use OMIT= on OUTFIL to drop the zero-value records.

These users thanked the author BillyBoyo for the post:
k singh (Tue Jun 14, 2016 9:03 pm)
BillyBoyo
Global moderator
 
Posts: 3795
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 259 times

Re: divide record into multiple records based upon condition

Postby k singh » Tue Jun 14, 2016 9:07 pm

Hi BillyBoyo
we don't have icetool in our shop. My above mentioned jcl gives me an output like this. I have added include in my jcl
    11,2,500,d
    11,3,100,a
    20,11,110,b
    30,9,80,c


whenever a condition is met, it jumps to the next record , does not goes to next condition.
k singh
 
Posts: 22
Joined: Fri Nov 21, 2014 8:38 pm
Has thanked: 9 times
Been thanked: 0 time

Re: divide record into multiple records based upon condition

Postby BillyBoyo » Tue Jun 14, 2016 11:02 pm

Do you have SyncSORT? If so, you could try PGM=SYNCTOOL on the EXEC.

If you have DFSORT, you have ICETOOL. I suppose it is possible that your site has prevented access to it. Some places are backward like that.

You have a couple of problems with what you have tried. Firstly, each time you do a BUILD it just makes a new "current record". Secondly once an IFTHEN=(WHEN=(logicalexpression) is true, that is the end of IFTHEN=(WHEN= processing, unless you specify HIT=NEXT. That's the explanation of ignoring the conditions, it's working how it is supposed to. Consider IFTHEN=(WHEN=(logicalexpression) as an EVALUATE in COBOL, if you know COBOL.

If you do have SyncSORT I think (but can't guarantee) you may be in luck. I think the "slash operator" (/) is available on OUTREC. You can use / to create multiple records with a BUILD statement. Then use OUTFIL OMIT= to get rid of the zeros you don't want.

With DFSORT the / is only available in OUTFIL, so I'd suggest a two-step operation. Firstly use OUTFIL BUILD with the slash operator to split the records, and then a second step with a simple OMIT COND= to get rid of the zero records.

The alternative, which you may want to consider for high volumes, is to relocate your WHEN=(logicalexpression) processing to OUTFIL, where you can use the slash operator, and remember the HIT=NEXT. Bear in mind you need quite a lot of IF statements to cover all eventualities. You could write some code (in SORT or something else) to generate them.
BillyBoyo
Global moderator
 
Posts: 3795
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 259 times

Re: divide record into multiple records based upon condition

Postby k singh » Wed Jun 15, 2016 3:09 am

Hi Billy

I cant use cobol. I tried using hit=next but for some odd reason it still gives same output.
I also tried using outfil but same output. if I use "/" and hit=next at the same time in outfil, it gives syntax error at hit=next.

I tried using simple input with one key(1-3) and 2 amounts (5-9,10-14)
input:
111 0000010000
222 1100020000
333 3000000000


jcl:
//STEP01   EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTMSG DD SYSOUT=*
//SORTIN DD DSN=aa.bb,DISP=OLD
//SORTOUT  DD  DSN=aa.b1,DISP=OLD
//*
//SYSIN    DD *
OPTION COPY
INREC IFTHEN=(WHEN=(5,5,ZD,GT,0),
BUILD=(1,3,C',A,',5,5),HIT=NEXT),
IFTHEN=(WHEN=(10,5,ZD,GT,0),
BUILD=(1,3,C',B,',10,5))


output:
111,B,10000
222,A,11000
333,A,30000

desired output:
111,B,10000
222,A,11000
222,B,20000
333,A,30000
.

please help on this
k singh
 
Posts: 22
Joined: Fri Nov 21, 2014 8:38 pm
Has thanked: 9 times
Been thanked: 0 time

Re: divide record into multiple records based upon condition

Postby BillyBoyo » Wed Jun 15, 2016 4:13 am

Each time you BUILD a record in INREC or OUTREC (with DFSORT, anyway) it just replaces the current record. So if you BUILD on one IFTHEN and then HIT=NEXT to another, the second BUILD will just overwrite what you just BUILDed recently. BUILD does not write output records, the SORT program does that itself, how it wants to, to rules that you have to follow.

The only modification of this behaviour is with the slash-operator. Which, for DFSORT, can only be used in OUTFIL (BUILDs in OUTFIL without the slash-operator act just like BUILDs in INREC or OUTREC).

 OUTFIL BUILD=(1,10,
               /,
               11,10)


That would create two 10-byte records which, assuming no more BUILDs, would both be written out from the OUTFIL group.

The easiest thing for you is to do it in two steps. Unconditionally, in OUTFIL, use the slash-operator to create five records with BUILD (or however many). Then, in a second step, use OMIT COND= to drop the zero-value records.

Get that going, then read my previous message.

These users thanked the author BillyBoyo for the post:
k singh (Tue Jun 21, 2016 12:05 am)
BillyBoyo
Global moderator
 
Posts: 3795
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 259 times

Re: divide record into multiple records based upon condition

Postby k singh » Tue Jun 21, 2016 12:05 am

thanks a lot.
it worked
k singh
 
Posts: 22
Joined: Fri Nov 21, 2014 8:38 pm
Has thanked: 9 times
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post