Re: Reporting anomaly for decimal data errors in SQL JOINs

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

On 2/16/2011 3:21 PM, CRPence wrote:
>     The "field" named is an internally generated name from a correlation
> name concatenated with an original column identifier, which may refer to
> a derivation [involving the field] rather than the field itself.  AFaIK
> the CQE does not generate correlation names; I am unsure if the SQE
> copies user-defined correlation names into the internal record format of
> the query.  So anyhow, even the field name ORDER may not definitively
> identify the field with "the problem" being diagnosed.  The Query/400
> even reworded the QRY#### messages, issued in response to such
> [confusing] mapping errors, to clarify that the field name recorded in
> the message may reflect a derived [key] expression rather than the
> column by that name.  See for example the messages QRY2283 and QRY2287
> in QQRYMSG which point to the ambiguity of a "field name" that is diagnosed.

I can understand that.  But in this case, the field name *is* correct, 
and so is not the source of the confusion.

>     IIRC the file named in the message for a join, implemented using the
> mostly-standard implementation, will always reflect the "primary file"
> of the join as the source of the problem.

This I cannot understand.  The engine knows the name of the table that 
really caused the error.  Saying the problem is in the primary file is 
worse than no information - it is flatly incorrect information.

>    In the case of a select\omit
> error, its origin is most likely from a temporary index over a file by
> the name presented; as the primary.  A reordered join may see the
> primary different than what appears in [as inferred from] the source
> statement.  Thus an error diagnosed in a join may refer to TABLE1
> because that is the name [taken from the primary file] applied to the
> "internal file" defined by the QDT.  I am not sure how much better the
> SQE is than the CQE at pinpointing the actual origin of bad data; things
> like divide-by-zero and overflow\underflow will similarly identify
> fields and origins, but intuitively these must be somewhat ambiguous as
> compared to bad decimal data in the column\row entity.

The error has all of the information: the actual physical record number, 
the format, the member.  The only thing it does wrong is idenfity the 
primary file rather than the real file - which clearly it knows, because 
it got the record number correct!

I'm going to open a PMR for this particular issue.  There's no excuse 
for giving me the information on the record number and so on and not 
present the actual table rather than the primary.

> Regards, Chuck
>
> On 2/15/11 6:40 AM, Joe Pluta wrote:
> > For informational purposes, here is the CPD4019 error that occurs in
> > an embedded SQL using a relatively complex JOIN. The error (with file
> > names changed to protect the innocent) is below:
> >
> > Message . . . . : Select or omit error on field TABLE2_7.ORDER
> > member TABLE1.
> >
> > Cause . . . . . : A select or omit error occurred in record 10659,
> > record format *FIRST, member number 1 of file TABLE1 in library
> > CCFILES, because of condition 1 of the following conditions: 1 - The
> > data was not valid in a decimal field.
> >
> >
> > The SQL SELECT statement joins TABLE1, TABLE2 and a number of other
> > tables, including a CTE.
> >
> > Here's the anomaly: note that the error identifies TABLE2_7, member
> > TABLE1. The _7 is evidently a internal identifier but the weird part
> > is that it also identifies member TABLE1. TABLE2 has no member
> > TABLE1, only member TABLE2 (these aren't multi-member files or
> > anything, just legacy files with control records that don't have
> > valid decimal data).
> >
> > Anyway, the error did not occur in TABLE1. Even though the low-level
> >   message insists that the error occurred in record 10659 of TABLE1, I
> >   assure you it did not: TABLE1 has only 313 records. The error is in
> > fact in record 10659 of TABLE2. So, until I figured this out, I was
> > confused and looking in the wrong place. I had a similar error on two
> > files of roughly the same size, so it wasn't as immediately apparent
> > that the file name under the Cause section was simply wrong.
> >
> > So - caveat SQLer. If you get an CPD4019, the most important file
> > name is the one mentioned in the Message section.