|
midrange.com
Hello,
I have an RPG ILE program in which I embedded a cursor. I created a
index on date (dtmv95) for the cursor to hit when I open the file. It worked
like a charm. But then I changed the date range in the cursor from being
static to being brought in by a prompt. Now my program will not use my index
when it opens the cursor, it fails with reason code 6 which means that "The
keys of the access path did not match the fields specified for the join
criteria." I think that the optimizer is not 'seeing' the date criteria in
my select because they depend on variables, but I have no idea what to do
about it. Does anyone out there have any experience with this problem?
Below is the code
H dftactgrp(*no) actgrp(*caller)
dmain pr extpgm('labor')
d startdate 15p 5
d enddate 15p 5
dmain pi
d startdate 15p 5
d enddate 15p 5
d thearray ds
d cono 2a
d item 16a
d pdate 7p 0
d labor_$ 6p 0
d shift 2p 0
d pgmn 1p 0
D dclcursorC1 pr
D opencursorC1 pr
D fetchcursorC1 pr n
D UpdateDirect pr n
D UpdateIndirect pr n
D UpdateTemp pr n
D AddRecord pr
D closecursorC1 pr
/free
dclcursorC1();
opencursorC1();
dow fetchcursorC1();
if (item = '* LABOR-DIRECT' or item = '*LABOR-DIR PC1' or item =
'Labor-DIR PC2' or item = '* LABOR CPR DRT' or item =
'* LABOR-DIR WHS' or item = '* LABOR-DIR SEQ' or item =
'* LABOR-DIR PNT');
if not(UpdateDirect());
AddRecord();
UpdateDirect();
endif;
elseif (item = '* LABOR-INDIR' or item = '*LABOR-IND PC1' or
item = '*LABOR-IND PC2' or item = '* LABOR-IND WHS' or
item = '* LABOR-IND SEQ' or item = '* LABOR-IND PNT');
if not(Updateindirect());
AddRecord();
UpdateIndirect();
endif;
elseif (item = '* LABOR-TEMP' or item = '*LABOR-TEMP PC1' or
item = '*LABOR-TEMP PC2' or item = '* LABOR-TEMP WHS' or
item = '* LABOR-TEMP SEQ' or item = '* LABOR-TEMP PNT');
if not(UpdateTemp());
AddRecord();
UpdateTemp();
endif;
else;
//disaster
endif;
enddo;
closecursorC1();
*inlr = *on;
/end-free
*Function definitions
p dclcursorC1 b
C/Exec SQL
C+ DECLARE labor CURSOR FOR
C+ SELECT
C+ cono95,
C+ pnum95,
C+ dtmv95,
C+ mqty95,
C+ nshf62,
C+ decimal(substr(pgmn35,1,1),1)
C+ FROM
C+ OSLD1F3/INP95 T01,
C+ OSLPDF3/PCP62 T02,
C+ OSLD1F3/INP35 T03
C+ WHERE
C+ CONO95 = CONO35 AND
C+ PNUM95 = PNUM35 AND
C+ CONO95 = CONO62 AND
C+ PNUM95 = COMP62 AND
C+ TMMV95 >= REFT62-1 AND
C+ TMMV95 <= REFT62 AND
C+ REFD95 = REFD62 AND
C+ CONO95 >= '02' AND
C+ CONO95 <= '10' AND
C+ (PNUM95 LIKE '* LABOR%' OR
C+ PNUM95 like '*LABOR%') and
C+ DTMV95 >= :startdate and
C+ dtmv95 <= :enddate
C+ FOR READ ONLY WITH NC
C/End-Exec
p dclcursorC1 e
p opencursorC1 b
C/Exec SQL
C+ OPEN labor
C/End-Exec
p opencursorC1 e
p fetchcursorC1 b
D pi n
d continueread s n
C/Exec SQL
C+ FETCH NEXT FROM LABOR INTO :cono, :item, :pdate, :labor_$,
C+ :shift, :pgmn
C/End-Exec
/free
continueread = *on;
if (sqlstt = '02000');
continueread = *off;
endif;
return continueread;
/end-free
p fetchcursorC1 e
p UpdateDirect b
D pi n
d record s n
C/Exec SQL
C+ UPDATE CPAYNE/PACKOFF SET Direct_labor_$ = :labor_$ WHERE COMPANY
C+ = :cono and PDATE = :pdate and SHIFT=:shift and PROFIT_CENTER =
C+ :pgmn
C/End-Exec
/free
record = *on;
if (sqlstt = '02000');
record = *off;
endif;
return record;
/end-free
p UpdateDirect e
p UpdateIndirect b
D pi n
d record s n
C/Exec SQL
C+ UPDATE CPAYNE/PACKOFF SET Indirect_labor_$ = :labor_$ WHERE COMPANY
C+ = :cono and PDATE = :pdate and SHIFT=:shift and PROFIT_CENTER =
C+ :pgmn
C/End-Exec
/free
record = *on;
if (sqlstt = '02000');
record = *off;
endif;
return record;
/end-free
p UpdateIndirect e
p UpdateTemp b
D pi n
d record s n
C/Exec SQL
C+ UPDATE CPAYNE/PACKOFF SET Temp_labor = :labor_$ WHERE COMPANY
C+ = :cono and PDATE = :pdate and SHIFT=:shift and PROFIT_CENTER =
C+ :pgmn
C/End-Exec
/free
record = *on;
if (sqlstt = '02000');
record = *off;
endif;
return record;
/end-free
p UpdateTemp e
p closecursorC1 b
C/exec sql
C+ close labor
C/end-exec
p closecursorC1 e
p AddRecord b
C/Exec SQL
C+ INSERT INTO CPAYNE/PACKOFF
C+ (COMPANY, PDATE, SHIFT, PROFIT_CENTER)
C+ VALUES(:cono,:pdate,:shift,:pgmn)
C/End-Exec
p AddRecord e
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
