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

On 06-Sep-2016 21:09 -0600, Darryl Freinkel wrote:

On 06-Sep-2016 21:03 -0600, Glenn Gundermann wrote:

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. […]

It would help to see your SQL statement.

You asked.

-----
Insert into <<SNIPped huge insert select-from query>>

From the given query, I suspect the following script would recreate the problem seen with the insert+query [that was snipped from the above message]:

create table qtemp.testing as
( select
decimal(rrn(D), 9) as D9
, /* 20 */ LAST_FIXED_COST_ROLLUP_DATE
, /* 21 */ LAST_CURRENT_COST_ROLLUP_DATE
, /* 22 */ LAST_CURRENT_TO_FIXED_ROLLOVER
from kbm_ada.db2_LND_MasterItem as D
) with no data
;

insert into qtemp.testing
( select
decimal(rrn(F), 9) as D9
, /* 19 LAST_COUNT_DATE */
case when IMDNCC != 0
then SUBSTR(DIGITS(IMDNCC), 1, 2)
|| '/' || SUBSTR(DIGITS(IMDNCC), 3, 2)
|| '/20' || SUBSTR(DIGITS(IMDNCC), 5, 2)
else '01/01/1901'
end as LCD
, /* 20 LAST_FIXED_COST_ROLLUP_DATE */
case when month(IMDSTR) between 1 and 12
then substr(char(IMDSTR), 6, 2 )
|| '/' || substr(char(IMDSTR), 9, 2 )
|| '/' || substr(char(IMDSTR), 1, 4 )
else '01/02/1901'
end as LFCRD
, /* 21 LAST_CURRENT_COST_ROLLUP_DATE */
case when month(IMDSTR) between 1 and 12
then substr(char(IMDSTR), 6, 2)
|| '/' || substr(char(IMDSTR), 9, 2)
|| '/' || substr(char(IMDSTR), 1, 4)
else '01/03/1901'
end as LCCRD
from kbm400mfg.fkitmstr as F
)
;

I suspect the preceding error will likely be msg CPF5035 RC17 "Data mapping error on member FKITMSTR. Cause . . . . . : A data mapping error occurred on field Cast(FKITMSTR_1.IMDSTR AS Date) in record number __, record format *FIRST, member number 1, in member FKITMSTR file FKITMSTR in library KBM400MFG, because of error code 17. …" F/QDBIOERRQO FM/QDBIOERRQO FP/SEND_MESSAGE T/QSQRUN3 TM/QSQINS TP/SQL_Insert

That would be the expected failure given the IMDSTR was a character string [e.g. VARCHAR(10)] and had value(s) that can not be implicitly cast to a DATE because the value is not formatted properly as one of the /standard/ date formats or the LOCAL format; i.e. for which the evaluation of the MONTH(IMDSTR) expression used in the WHEN of the CASE expression necessarily fails. If indeed that presumed DDL is accurate, then depending on what that strangely coded expression along with that date string reformatting expression are attempting to effect, then a possible resolution might be as simple as coding those duplicate CASE expressions with the following instead:

case when substr(IMDSTR, 6, 2) between '01' and '12'

Note: I suggest /strange/, because the *only* way the expression MONTH(IMDSTR) can function without error, is if the character-string represents a valid date, for which the only possible values *must* be BETWEEN 01 AND 12, and the CHAR() seems redundant. Also, the apparent formatting of that value is YYYY-MM-DD [as inferred from the SUBSTR() expressions and ordering, so if the target columns are actual dates, there would be no reason to reformat -- but perhaps they are not valid *ISO and instead are stored as YYYY/MM/DD, for which a much simpler reformatting expression would be REPLACE(IMDSTR,'/','-') or perhaps the target is also a string and *USA is required, but CHAR(IMDSTR,USA) would be sufficient if the string values can be presumed to be valid.