Re: SQL query results in CPI432F

[Luis Rodriguez <luisro58@xxxxxxxxx>]

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.