Reporting anomaly for decimal data errors in SQL JOINs

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

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.

Joe