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
 

 

CAN WE ROUND UP USING JCL SORT

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

Active User


Joined: 05 Oct 2006
Posts: 141

PostPosted: Fri Apr 13, 2012 11:51 am    Post subject: CAN WE ROUND UP USING JCL SORT
Reply with quote

H,
i have a tricky problem which i want to achieve it through dfsort. can this be achieved through sort?? please me help me regarding the same

i have two files FILE1 is of record lenght 8.And FILE2 is of record length7.


input file1:
01 DETAIL.
03 STORENAME PIC X(5).
03 QTY PIC 9(2).
03 UNIT PIC 9.


output file1:
01 DETAIL.
03 STORENAME PIC X(5).
03 QTY PIC 9(2).


input file1 is as below


STOREA106
STOREA153
STOREA204

STOREA,qty1 –> 10, unit -> 6,
STOREA,qty2 –> 15, unit -> 3
STOREA,qty3 –> 20, unit -> 4


then i want to recalculate the qty as 10+(15*3)/6+(20*4)/6
10+8+14=32

after recalculating bunch up all the recalculated quantities and assign it to first qty field.

i.e



qty1 –> 32,
qty2 –> 8,
qty3 –> 14,

OUTPUT SHOULD BE:
STOREA32
STOREA8
STOREA14

please help ne regrading the same.
regds,
useit
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Apr 13, 2012 12:46 pm    Post subject: Reply to: CAN WE ROUND UP USING JCL SORT
Reply with quote

I've looked at the calculations you have provided.

I have not the slightest idea what you mean.

I've already wasted one minute, so can you fully explain how you want the calcultion to work, please.
Back to top
View user's profile Send private message
useit

Active User


Joined: 05 Oct 2006
Posts: 141

PostPosted: Fri Apr 13, 2012 2:19 pm    Post subject:
Reply with quote

bill,

when STOREA has multiple Qty’s and differnt unit size in that case we need to recalculate the qty.


say for example:

STOREA,qty1 –> 10, unit -> 6,
STOREA,qty2 –> 15, unit -> 3
STOREA,qty3 –> 20, unit -> 4


step1:

qty1=keep as it is

qty2=(15*3)/6-------------->unit size of qty1


qty3=(20*4)/6-------------->unit size of qty1


so my output will be

STOREA,qty1 –> 10
STOREA,qty2 –> 8------------(45/6=7.5=8)
STOREA,qty3 –> 14-----------(80/6=13.3=14)

-----------------------------------------------------
STOREA10
STOREA8
STOREA14
-----------------------------------------------------

step 2:

then bunch up all the recalculated qty's and assign it to qty1

so my final output should be


STOREA,qty1 –> 32 ----->(10+8+14)
STOREA,qty2 –> 8
STOREA,qty3 –> 14

final output file:
------------------------
STOREA32
STOREA8
STOREA14
-------------------

Regds,
useit
_____________
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Apr 13, 2012 2:34 pm    Post subject: Reply to: CAN WE ROUND UP USING JCL SORT
Reply with quote

OK, I can see what you mean now, thanks.

I never like data that is difficult to recreate in the case of an error. You will entirely loose your "10" value, and it can only be recovered through calculation. If you are creating some type of "store header", why don't you keep the first detail record anyway?

How about using the Code tags? Six years you've been here. Do you want the output in smudgy. messed-about-number format (like "8") or would it be OK as "08"?
Back to top
View user's profile Send private message
useit

Active User


Joined: 05 Oct 2006
Posts: 141

PostPosted: Fri Apr 13, 2012 2:46 pm    Post subject:
Reply with quote

hi,

it should be 08.

STOREA32
STOREA08
STOREA14

REgds,
useit
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Fri Apr 13, 2012 9:11 pm    Post subject:
Reply with quote

Useit,

Use the following DFSORT JCL which will give you the desired results. I assumed that your data is already sorted on the storename.

Code:

//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//INA      DD DSN=Your Input FB LRECL 8 byte file,DISP=SHR
//INB      DD DSN=Same Input FB LRECL 8 byte file,DISP=SHR
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  OPTION COPY                                                       
  JOINKEYS F1=INA,FIELDS=(1,5,A),SORTED,NOSEQCK                     
  JOINKEYS F2=INB,FIELDS=(1,5,A)                                     
  REFORMAT FIELDS=(F1:1,7,9,2,F2:11,2)                               
  INREC IFOUTLEN=7,                                                 
  IFTHEN=(WHEN=(8,2,ZD,EQ,1),OVERLAY=(6:10,2))                       
//*                                                                 
//JNF1CNTL DD *                                                     
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(11:6,2)),                         
  IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,5),PUSH=(9:SEQ=2)),                 
  IFTHEN=(WHEN=(9,2,ZD,GT,1),                                       
  OVERLAY=(11:(11,2,ZD,MUL,8,1,ZD),DIV,+6,EDIT=(TT),X,               
              (11,2,ZD,MUL,8,1,ZD),MOD,+6,EDIT=(TT)),HIT=NEXT),     
  IFTHEN=(WHEN=(14,2,ZD,GT,0),OVERLAY=(06:11,2,ZD,ADD,+1,EDIT=(TT)))
//*                                                                     
//JNF2CNTL DD *                                                     
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(11:6,2)),                         
  IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,5),PUSH=(9:SEQ=2)),                 
  IFTHEN=(WHEN=(9,2,ZD,GT,1),                                       
  OVERLAY=(11:(11,2,ZD,MUL,8,1,ZD),DIV,+6,EDIT=(TT),X,               
              (11,2,ZD,MUL,8,1,ZD),MOD,+6,EDIT=(TT)),HIT=NEXT),     
  IFTHEN=(WHEN=(14,2,ZD,GT,0),OVERLAY=(11:11,2,ZD,ADD,+1,EDIT=(TT)))
                                                                     
  SUM FIELDS=(11,2,ZD)                                               
//*                                                       
Back to top
View user's profile Send private message
useit

Active User


Joined: 05 Oct 2006
Posts: 141

PostPosted: Mon Apr 23, 2012 12:13 pm    Post subject:
Reply with quote

skolosu,
i ran the jcl provided by you but did not get the correct output.in your jcl you have used two input files of record length 8.but


i have 2 files. one input. one output file.

input file1:(LRECL=8)

input file1:
01 DETAIL.
03 STORENAME PIC X(5).
03 QTY PIC 9(2).
03 UNIT PIC 9.
--------------------
STORE106
STORE153
STORE204
----------------------

Output file1:(LRECL=7)

output file1:
01 DETAIL.
03 STORENAME PIC X(5).
03 QTY PIC 9(2).

Final output should be
.........................
STOREA32
STOREA8
STOREA14
...........................



regds,
useit
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Mon Apr 23, 2012 12:33 pm    Post subject: Reply to: CAN WE ROUND UP USING JCL SORT
Reply with quote

just take a decision!

in one post You say
... it should be 08.
Code:
STOREA32
STOREA08
STOREA14

now You say
Code:
STOREA32
STOREA8
STOREA14


icon_rolleyes.gif
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Mon Apr 23, 2012 12:54 pm    Post subject: Reply to: CAN WE ROUND UP USING JCL SORT
Reply with quote

Useit,

It is not at all clear what you are saying.

Is the only problem you have the "8" instead of "08"? You were specifically asked about this before, and said to go with "08".

If that is your problem, try a bit of code yourself. You would want something "left justified with leading zeros removed". See if you can find something similar (like with "space" being removed) and find out how to remove zeros instead of space.
Back to top
View user's profile Send private message
useit

Active User


Joined: 05 Oct 2006
Posts: 141

PostPosted: Mon Apr 23, 2012 2:24 pm    Post subject:
Reply with quote

sorry for the typo, ... it should be 08.

Code:
STOREA32
STOREA08
STOREA14

i ran the jcl again and got the correct output. but i did not understand the below sysin cards.

//SYSIN DD *
OPTION COPY
JOINKEYS F1=INA,FIELDS=(1,5,A),SORTED,NOSEQCK
JOINKEYS F2=INB,FIELDS=(1,5,A)
REFORMAT FIELDS=(F1:1,7,9,2,F2:11,2)
INREC IFOUTLEN=7,
IFTHEN=(WHEN=(8,2,ZD,EQ,1),OVERLAY=(6:10,2))

both F1 and F2 is of record length 8. but in the reformat fields wat would be the value in (9,2 and F2:9,2)?

Also i dont want to hard code the +6 value in the below control statement.it can change so i just want to mention the position of the field.

OVERLAY=(11:(11,2,ZD,MUL,8,1,ZD),DIV,+6,EDIT=(TT),X,
(11,2,ZD,MUL,8,1,ZD),MOD,+6,EDIT=(TT)),HIT=NEXT),

please help me in this regard

regds,
useit
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Mon Apr 23, 2012 2:54 pm    Post subject: Reply to: CAN WE ROUND UP USING JCL SORT
Reply with quote

When defining your requirement, the more exactitude, the better the solution. You showed three records and always mentioned specific values, without describing that this was representative of data which could contain different values.

The Sort is reading your same input file twice. It is a common technique. You need to update a value on a record which in normal circumstances has already been written. This is a way to allow you to have the total before the record is written, so the update can work.

If you look at the JNF1CNTL and JNF2CNTL they are doing some processing. In the course of that processing the file is being extended within the processing so that the required information is available at the required time.

In isolation:

Code:
OPTION COPY
JOINKEYS F1=INA,FIELDS=(1,5,A),SORTED,NOSEQCK
JOINKEYS F2=INB,FIELDS=(1,5,A)
REFORMAT FIELDS=(F1:1,7,9,2,F2:11,2)
INREC IFOUTLEN=7,
IFTHEN=(WHEN=(8,2,ZD,EQ,1),OVERLAY=(6:10,2))


A JOINKEYS is being used on two input files. The first is to be treated as in sequence already and not to have sequence-checking done as it is processed.

The second file will be sorted as part of the JOINKEYS processing.

Positions 1 for a length of 7 and 9 for a length of 2 are required for your processing from the first JOINKEYS file, and 11 for a length of 2 is required from the second (it contains the total you want).

The output record-length is set to 7 (which is what you want). A particular value identifies which record needs to be updated with the total, and the update is made.

The output file is thren written with no further SORT (it is COPYd).

So, the "joinkeys" bit is about taking the total and putting it where you want.

The JNF2CNTL is the same as the JNF1CNTL except for the SUM. The purpose of the SUM is to get the total you want, which is then put in the position you want as described above.

Code:
//JNF1CNTL DD *                                                     
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(11:6,2)),                         
  IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,5),PUSH=(9:SEQ=2)),                 
  IFTHEN=(WHEN=(9,2,ZD,GT,1),                                       
  OVERLAY=(11:(11,2,ZD,MUL,8,1,ZD),DIV,+6,EDIT=(TT),X,               
              (11,2,ZD,MUL,8,1,ZD),MOD,+6,EDIT=(TT)),HIT=NEXT),     
  IFTHEN=(WHEN=(14,2,ZD,GT,0),OVERLAY=(06:11,2,ZD,ADD,+1,EDIT=(TT)))


This is the code doing the processing that you want to recalculate the values.

For each record the value at 6 for a length of 2 is copied to start position 11.

A GROUP is established on your key, and a sequence number (length of 2) is added at position 9 for each record in the group.

For records other than the first one, the value is recalcuated and then returned to the position of the value, taking into account your "next highest integer".

If you want to use the value from the record instead of the +6, you need to include a bit of extra code. The value you desire is on the record which is used when establishing the GROUP, so you can add to the PUSH a copy of that value to a new location which will then be available for the subsequent records.

Then, in the places where +6 is used, you use that PUSHed value instead.
Back to top
View user's profile Send private message
useit

Active User


Joined: 05 Oct 2006
Posts: 141

PostPosted: Tue Apr 24, 2012 4:54 pm    Post subject:
Reply with quote

thanks a lot for your help.

i have a similar problem which i tried to do in the same manner but it failed with RC=16.please help in this regard.

Below is the input file structure

lrecl=54,

01 DETAIL.
03 KEY.
05 STORE PIC 9(5).
05 PRODA PIC 9(9).
05 PRODB PIC 9(9).
03 DATA.
05 STocK PIC S9(9) COMP-3.
05 QTY1 PIC S9(9) COMP-3.
05 QTY2 PIC S9(9) COMP-3.
05 IND PIC X.
05 SIZE PIC 9(5)V99.
05 WEIGHT PIC X.
05 WGT PIC 9(5)V99.

input file is as below.

(1-5) (6-14) (15-23) (24-28) (29-33) (34-38) (39-39) (40-46) (47-47) (48-54)

00010 050009698 008774500 +100 +222222222 +111111111 N 00006.00 I 00008.00

00010 050009698 008774600 +100 +222222222 +111111111 N 00006.00 I 00008.00

00010 050009698 008774700 +100 +222222222 +111111111 N 00006.00 I 00008.00

00010 050009700 008777500 +100 +222222222 +111111111 N 00005.00 I 00008.00

00010 050009700 008777600 +100 +222222222 +111111111 N 00006.10 I 00008.00

00010 050009700 008777700 +100 +222222222 +111111111 N 00007.20 I 00008.00

00010 050009700 008777800 +100 +222222222 +111111111 N 00008.00 I 00008.00



WHEN PRODA has multiple PRODB in STORE (when ALL PRODB has same size ):

then calculate the stock as 100+100+100=300


WHEN PRODA has multiple PRODB in STORE (when ALL PRODB has different size ):

then recalculate stock=100+(100*00006.10)/00005.00+(100*000007.20)/00005.00+(100*00008.00)/00005.00

=100+120+140+160=520


00010 050009698 008774500 +300 +222222222 +111111111 N 00006.00 I 00008.00

00010 050009698 008774600 +100 +222222222 +111111111 N 00006.00 I 00008.00

00010 050009698 008774700 +100 +222222222 +111111111 N 00006.00 I 00008.00

00010 050009700 008777500 +520 +222222222 +111111111 N 00005.00 I 00008.00

00010 050009700 008777600 +100 +222222222 +111111111 N 00006.10 I 00008.00

00010 050009700 008777700 +100 +222222222 +111111111 N 00007.20 I 00008.00

00010 050009700 008777800 +100 +222222222 +111111111 N 00008.00 I 00008.00

Regds,
useit
Back to top
View user's profile Send private message
useit

Active User


Joined: 05 Oct 2006
Posts: 141

PostPosted: Tue Apr 24, 2012 5:00 pm    Post subject:
Reply with quote

i ran the below jcl . but it failed with rc=6. please correct me in case if anything wrong.

//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//INA DD DSN=MY INPUT FILE,DISP=SHR
//INB DD DSN=SAME INPUT FILE,DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
JOINKEYS F1=INA,FIELDS=(1,14,A),SORTED,NOSEQCK
JOINKEYS F2=INB,FIELDS=(1,14,A)
REFORMAT FIELDS=(F1:1,54,55,2,F2:57,4)
INREC IFOUTLEN=54,
IFTHEN=(WHEN=(55,2,ZD,EQ,1),OVERLAY=(24:57,4))
//*
//JNF1CNTL DD *
INREC IFTHEN=(WHEN=INIT,OVERLAY=(57:24,4)),
IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,14),PUSH=(55:SEQ=2)),
IFTHEN=(WHEN=(55,2,ZD,GT,1),
OVERLAY=(57:(57,4,PD,MUL,40,7,ZD),DIV,+5,EDIT=(TT),X,
(57,4,PD,MUL,40,7,ZD),MOD,+5,EDIT=(TT)),HIT=NEXT),
IFTHEN=(WHEN=(61,2,ZD,GT,0),OVERLAY=(24:57,4,PD,ADD,+1,EDIT=(TT)))
//*
//JNF2CNTL DD *
INREC IFTHEN=(WHEN=INIT,OVERLAY=(57:24,4)),
IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,14),PUSH=(55:SEQ=2)),
IFTHEN=(WHEN=(55,2,ZD,GT,1),
OVERLAY=(57:(57,4,PD,MUL,40,7,ZD),DIV,+5,EDIT=(TT),X,
(57,4,PD,MUL,40,7,ZD),MOD,+5,EDIT=(TT)),HIT=NEXT),
IFTHEN=(WHEN=(61,2,ZD,GT,0),OVERLAY=(24:57,4,PD,ADD,+1,EDIT=(TT)))

SUM FIELDS=(57,4,PD)
//*

regds,
useit
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Tue Apr 24, 2012 5:04 pm    Post subject: Reply to: CAN WE ROUND UP USING JCL SORT
Reply with quote

Have you noticed everyone using the Code tags to preserve formatting?

Why didn't you start this as a new topic?

How does your calculation work, as it seems to ignore the "units" of the intermediate results?
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Tue Apr 24, 2012 5:40 pm    Post subject: Reply to: CAN WE ROUND UP USING JCL SORT
Reply with quote

Belatedly noticed you posted the sort cards. Can you post all the step output so we can see what you got a 16 for?
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 Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm


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