Re: Reporting anomaly for decimal data errors in SQL JOINs

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.

The query engine formulates various names which might generally assist to track back to an original field, but the expression ORDER*AMOUNT might identify ORDER when the actual data problem is in the AMOUNT field; only by a quirk of parsing is the expression denoted with the name ORDER versus AMOUNT in the "record format" of the query definition template [QDT] or in the RcdFmt of an actual temporary file that implements the query.

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. 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.

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.