Re: Need some assistance with an SQL query

On 07 Dec 2012 12:52, RPGLIST wrote:

I'm still running into an issue try to figure out why there are 8
records that didn't update, well 5 because three are not in the file,
that much I have determined.

Or 8 out of 11 records [per the "work file with 991 records" for which the UPDATE statement updated only "980 records"] after three were found not to be in the production file? Hmm, apparently a new message says there are 988 records in the work-file and only 980 updates... so that still seems like 8 rows.? Regardless...

I have two records that exists in RouteMaxL1 with the following
values:
mxplan='BTXTW'
mxdiv=01
mxins='F'
mxben='OCP'
mxppo=''
Mxtfrm='V'
mxtdt=0

and a record that has mxtdt=20121015

It does not update either record, skips them completely. I have no
idea why... field lengths are an exact match as is the data, I would
expect the one with the mxtdt=0 to be updated but <ed: ¿ *not* ?> the
one with the date value.

Suggestions?

Seeing "mxppo=''", I have to ask, is that column possibly VARCHAR? I recall issues whereby non-trimmed /blank/ data in VARCHAR may cause mis-compares against blanks or empty-string; possible differences for when the comparison might be made via an access path index-entry versus the column data.

I would verify that LENGTH(rtrim(mxppo))=LENGTH(mxppo) if that column MXPPO is VARCHAR.