Rule_Num MAN_NUM ACCOUNT_NUM LINE_CD SKU COMM_PCT Y_N_F Effective Start Date Effective End Date
1 1001 901122 8 123 15 Y 1/15/2015 2/15/2015
2 1001 901122 8 12 Y 6/16/2016 9/15/2016
3 1001 901122 8 10 Y 6/16/2016 9/15/2016
4 1001 901122 8 123 11 Y 4/15/2016 5/15/2016
5 1002 805566 8 16 Y 4/15/2016 5/15/2016
6 1003 8 17 Y 4/15/2016 5/15/2016
Read MAN_NUM, ACCT_NUM, LINE_CD, SKU from input and check the lookup table and fetch COMM_PCT for the current date.
Effective start and end date is used to validate the rule against current date.
Rules to fetch the COMM_PCT.
If there is a hit for the below iin lookup table
MAN_NUM + Account_NUM + PROD_LINE_CD + SKU combination --> fetch COMM_PCT
If not found check
MAN_NUM + Account_NUM + PROD_LINE_CD --> Fetch COMM_PCT
If not found check
MAN_NUM + PROD_LINE_CD --> Fetch COMM_PCT
If not found
<Write the input record to Error file>
Expected output.
Code:
MAN_NUM ACCT_NUM LINE_CD SKU COMM_PCT RULE_Num Y-N-F
1001 901122 8 123 11 4 Y
1002 805566 8 667 16 5 Y
1003 122996 8 225 17 6 Y
I know we can do this in cobol. since the lookup table will be hit multiple times to fetch the rate for one input read.
I am thinking if the lookup table could be extracted to a file and using a DFSORT utility, combine the source file and lookup file to get COMM_PCT in the output file will be a better way. please advise.[/code]
MAN_NUM 9(4)
ACCT_NUM 9(5)
LINE_CD 9(1)
SKU x(11) -- above I gave only three digits for reference
Lookup table: this will be a new table, DCLGEN is not yet defined. below the field definitions.
Rule_Num - this will be sequence number - Smallint
MAN_NUM decimal(4,0)
ACCOUNT_NUM decimal(6,0)
LINE_CD SKU varchar(11)
COMM_PCT decimal(5,2)
Y_N_F varchar(1)
Effective Start Date
Effective End Date
Joined: 26 Nov 2002 Posts: 12369 Topics: 75 Location: San Jose
Posted: Thu May 12, 2016 12:33 pm Post subject:
vak255 wrote:
LINE_CD SKU varchar(11)
Line_cd is varchar(11)? or is SKU 11?
vak255 wrote:
Y_N_F varchar(1)
Why would anyone define a column with VARCHAR(1)? what is that you are trying to save. with VARCHAR you do realize that there is a 2 byte length field.
If it is only 1 byte what is the point of having it as varchar?
either way I assumed the following for the table.
Code:
(
RULE_NUM SMALLINT NOT NULL
,MAN_NUM DECIMAL(4,0) NOT NULL
,ACCOUNT_NUM DECIMAL(6,0)
,LINE_CD CHAR(1) NOT NULL
,SKU CHAR(11)
,COMM_PCT DECIMAL(5,2) NOT NULL
,Y_N_F CHAR(1) NOT NULL
,EFFECTIVE_START_DATE DATE NOT NULL
,EFFECTIVE_END_DATE DATE NOT NULL
)
Use this JCL to unload the table to a sequential file.
Code:
//STEP0100 EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=121
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB2P)
RUN PROGRAM(DSNTIAUL) -
PLAN(DSNTIAUL) -
PARMS('SQL') -
LIB('DB2P.RUNLIB.LOAD')
//SYSREC00 DD DSN=&&UNLD,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)
//SYSPUNCH DD SYSOUT=*
//SYSIN DD *
SELECT *
FROM table
WHERE CURRENT DATE BETWEEN EFFECTIVE_START_DATE AND
EFFECTIVE_END_DATE
;
//*
You should look at the syspunch dataset to see how the data is unloaded as it shows the positions of columns unloaded. watch out for the null indicators.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum