Re: How do I track the field number from a SQL script?

On 06-Sep-2016 20:57 -0600, Darryl Freinkel wrote:

I am reading 1 table and writing to another.
I have a bunch of date fields in the record and 1 of them is in
error. The error message points to field 25.
Field 25 is not the 25th field starting on the left to the right.
The 25th field is a char field.

How is the system determining the field number?

-------
Message ID . . . . . . : SQL0180 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic
Date sent . . . . . . : 09/06/16 Time sent . . . . . . : 21:51:47

Message . . . . : Syntax of date, time, or timestamp value not
valid.
Cause . . . . . : The string representation of a date, time,
or timestamp value does not conform to the syntax for the specified
or implied data type and format. *N is either the character string
constant that is not valid or the column or host variable that
contained the string. If the name is *N, then the value is an
expression specified in the statement. If the string was found in a
host variable, the host variable number is 25.

Recovery . . . : Ensure that the date, time, or timestamp value
conforms to the syntax for the data type it represents. Try the
request again.

The spooled [debug] joblog taken with LOG(4 0 *SECLVL) in effect might be more helpful to determine the origin. The numeric value of 25 is presumably being obtained from a prior message CPDxxxx that was issued by the database [query] feature. The context of that message [from\to programs and procedures] and the second level message text together, should also reveal if the error is in the read or the write of the data, and [also thus] which file member and possibly the record format name that defines what is the 25th.

IIRC, the CQE would have referred to the first column named in an expression as the /cause/ of such a mapping error, and a position identified would be within the record format of the queried file or the query record format. The SQE improved that greatly by logging some really nice messages which show the actual casting expression that is failing [including the qualified column names], but those details would be logged in [and thus available only from] the joblog; seen prior to the sqlcode=-180 condition being logged.