Re: SQL query results in CPI432F

Dennis,

Don't know if this applies to your sentence, but I just remembered a note I
once read about being sure that in a WHERE clause as in, say, WHERE tcer =
:comp, the value of the right side of the expression (:COMP) should be equal
or less in length to the one of the left side (TCER). If not, a table scan
would ensue.

Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Mon, Feb 1, 2010 at 12:00 PM, Dennis Lovelady <iseries@xxxxxxxxxxxx>wrote:

3. Created another table, this time with SRTSEQ(*LANGIDSHR). Ran the
same
query. System suggested (again) to create an index for field F1.
4. Created suggested index. Ran query again. This time, the system told
me
that it had examined available indexes, but it couldn't use it due to
reason
18 (msgid CPI432D, which states:

<QUOTE/>
18 - The left-most key of the access path matched a field specified for
the
selection criteria. However, the sequence table associated with the
access
path did not match the sequence table associated with the query.

Thanks, Luis. You put a lot of work into that. You didn't ask for it, but
here's the SQL in question, in hopes that it might shed some light. As a
reminder, SQL wanted an index by TCER and TCSTGE.
Exec SQL
DECLARE tcCursor CURSOR FOR
SELECT tcer, tcl01, tcl02, tcl03, MIN(tcdth)
FROM taptc
EXCEPTION JOIN taptk ON tker = tcer
AND tkl01 = tcl01
AND tkl02 = tcl02
AND tkl03 = tcl03
AND tkeffh <= tcdth
AND (tken = ' '
OR (tken = tcen AND
:empLevelOK = '1')
)
WHERE tcer = :comp
AND tcstge < 8
AND SUBSTR(tcl03,5,1) = 'S'
GROUP BY tcer, tcl01, tcl02, tcl03
;

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"The closest a person ever comes to perfection is when they fill out a job
application form."
-- Stanley J. Randall



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.