Re: Reporting anomaly for decimal data errors in SQL JOINs

[CRPence <CRPbottle@xxxxxxxxx>]

  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.