× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.