I am using OUTFIL to build 161 byte record by checking column on position 50 with length 2 bytes- data type- binary along with i am doing the sum on values available at positions specified by 'TOT' statment .All columns stands by 'TOT' are sales amount and customer count .
I am facing the issue since build is happening by default .Can you please advise how to use "IF THEN" here to control the build instead of INCLUDE here. since i need an empty output file when there is a value other
than 11 and 14 in column 50 .
TOT purpose is mainly used to have sum - customer count which is in BI format , in some cases will receive in -ve . SUM is not properly work on when we try to sum it up . Hence we went with TOT=. Also i overcome the issue when i try to use it in ICETOOL with 3 control cards and with temp file in between . however Still i am expecting /working on optimized code for this ..
TOT purpose is mainly used to have sum - customer count which is in BI format , in some cases will receive in -ve . SUM is not properly work on when we try to sum it up . Hence we went with TOT=. Also i overcome the issue when i try to use it in ICETOOL with 3 control cards and with temp file in between . however Still i am expecting /working on optimized code for this ..
Can you try this and let us know the results.
UNTESTED
Code:
SORT FIELDS=(45,5,ZD,A)
SUM FIELDS=(081,8,PD,
089,8,PD,
097,4,BI,
101,4,BI,
105,8,PD,
113,8,PD,
121,4,BI,
125,4,BI,
129,8,PD)
TOT purpose is mainly used to have sum - customer count which is in BI format , in some cases will receive in -ve . SUM is not properly work on when we try to sum it up . Hence we went with TOT=. Also i overcome the issue when i try to use it in ICETOOL with 3 control cards and with temp file in between . however Still i am expecting /working on optimized code for this ..
Can you try this and let us know the results.
UNTESTED
Code:
SORT FIELDS=(45,5,ZD,A)
SUM FIELDS=(081,8,PD,
089,8,PD,
097,4,BI,
101,4,BI,
105,8,PD,
113,8,PD,
121,4,BI,
125,4,BI,
129,8,PD)
Hi Magesh,
Sure. Before that i will see what was the issue i have faced when i use sum, it should work fine whatever may be the value +ve or -ve . I come up with two solutions with /without ICETOOL .ICETOOL used with the same code with little changes .
CTL1:-
COPY FROM (IN1) TO (TEMP01) USING (CTL1) - CTL1 will have that above sort card
COPY FROM(TEMP01) TO(OUT1) USING(CTL2)
CTL2:
SORT FIELDS=COPY
INCLUDE COND=(50,2,BI,EQ,X'000B',OR,
50,2,BI,EQ,X'000E')
After that i have one more sort card to replace 50th poistion to 90.To indicate this as totals of '11' & '14'.
SOLUTION WITHOUT ICETOOL
Results achieved with empty file but need to test for the sum if i have negative values in BI Field. This is with one SORT CARD.
Code:
SORT FIELDS=(45,5,ZD,A) - DEPT NUMBER FIELD
INCLUDE COND=(50,2,BI,EQ,X'000B',OR,
50,2,BI,EQ,X'000E')
INREC OVERLAY=(1:C'TOTAL ',
21:C'11&14TOTAL ',
50:+90,TO=BI,LENGTH=2)
SUM FIELDS=(081,8,PD,
089,8,PD,
097,4,BI,
101,4,BI,
105,8,PD,
113,8,PD,
121,4,BI,
125,4,BI,
129,8,PD)
OUTREC IFTHEN=(WHEN=(50,2,BI,EQ,X'005A'),
BUILD=(1,161))
conditional build (WHEN) clause and INCLUDE COND IN SORT helps me to build empty file .However i need to see on the sum for negative .
TOT purpose is mainly used to have sum - customer count which is in BI format , in some cases will receive in -ve . SUM is not properly work on when we try to sum it up . Hence we went with TOT=. Also i overcome the issue when i try to use it in ICETOOL with 3 control cards and with temp file in between . however Still i am expecting /working on optimized code for this ..
Can you try this and let us know the results.
UNTESTED
Code:
SORT FIELDS=(45,5,ZD,A)
SUM FIELDS=(081,8,PD,
089,8,PD,
097,4,BI,
101,4,BI,
105,8,PD,
113,8,PD,
121,4,BI,
125,4,BI,
129,8,PD)
I am going with solution without ICETOOL . But when there is a negative value SUM on BI format is not working. I found out from other related forums - I need to use 'FI' format for BI fields with respect to SUM.Please advise it is recommend to use 'FI' format to have SUM on
BI data type fields which will have values of both -ve and +ve in my user cases.
I have tested and it is working fine .
Code:
SORT FIELDS=(45,5,ZD,A) - DEPT NUMBER FIELD
INCLUDE COND=(50,2,BI,EQ,X'000B',OR,
50,2,BI,EQ,X'000E')
INREC OVERLAY=(1:C'TOTAL ',
21:C'11&14TOTAL ',
50:+90,TO=BI,LENGTH=2)
SUM FIELDS=(081,8,PD,
089,8,PD,
097,4,FI,
101,4,FI,
105,8,PD,
113,8,PD,
121,4,FI,
125,4,FI,
129,8,PD)
OUTREC IFTHEN=(WHEN=(50,2,BI,EQ,X'005A'),
BUILD=(1,161))
conditional build (WHEN) clause and INCLUDE COND IN SORT helps me to build empty file .However i need to see on the sum for negative .
I couldnt understand, why do you need an empty file ?
Hi Magesh,
Will test without OUTREC , before that i need an empty file since i am writing common JCL for 2 different feeds (sales input file) out of which one sales file will not have certain combo (sales channel) (i,e) 11 & 14 . Also i need to know will it be an issue when i try to insert the sum totals to DB2 table because the SUM is done with 'FI' format and those values will get updated/inserted in db2 table columns declared in INTEGER format . Please do let me know is it advisable/must to convert to 'BI' before inserting in db2 table .