On 16-Mar-2018 15:03 -0600, Kendall Kinnear wrote:
I have this as part of a SQL select statement:
" timestamp(
CHAR( T01.MIDTSY ) || '-' ||
substr( digits( T01.MIMTIM ), 1, 2) || '.' ||
substr( digits( t01.MIMTIM ), 3, 2) || '.00'
) as Message_Timestamp
"
The DDL is not given, but presumably, the column named MIDTSY is
data-type of DATE and MIMTIM is either NUMERIC(4) or DECIMAL(4).?
When I run the select statement using Run SQL Scripts out of IBM
Access Client Solutions I get back the row I expected with a
timestamp that looks like this: "2018-03-16 15:35:00.000000"
[IBM i 7.3->…->Datetime values->String representations of datetime
values](
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/db2/rbafzdtstrng.htm)
…
The default date and time formats are set based on the date format
(DATFMT), the date separator (DATSEP), the time format (TIMFMT), and the
time separator (TIMSEP) parameters.
…
[IBM i 7.3->…->Datetime values->String representations of datetime
values->Timestamp
strings](
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/db2/rbafztsstrings.htm)
…
Table 1. Formats for String Representations of Timestamps
•Format Name :'Time Format'
----> :'Example'
-----------------------------------------------------------
•ISO timestamp :'yyyy-mm-dd hh:mm:ss.nnnnnnnnnnnn'
----> :'1990-03-02 08:30:00.010000000000'
•IBM® SQL :'yyyy-mm-dd-hh.mm.ss.nnnnnnnnnnnn'
----> :'1990-03-02-08.30.00.010000000000'
•14-26 char form:'yyyymmddhhmmssnnnnnnnnnnnn'
----> :'19900302083000'
…
When I embed the same select into an RPG ILE program, I get this
error and I can't see why:
"Select or omit error on field
Cast( Concat( Cast( RBAMI_1.MIDTSY AS Char(8) CCSID 37),'-'
, Substr((Cast(ABS(RBAMI_1.MIMTIM) AS Numeric(4, 0))), 1, 2),':'
, Substr((Cast(ABS(RBAMI_1.MIMTIM) AS Numeric(4, 0))), 3, 2),':00')
AS TimeStamp)
member RBAMI."
Seems possible, that the given expression, is not consistent with the
given error; perhaps, that a revision had been made to the SQL
expression [¿using colons vs periods as the literal/constants specified
in the concatenation?], and that the error details were collected for an
invocation with the modified expression, rather than collected for the
originally failing invocation/expression? And that would explain why
the suggestion from Charles would not assist -- if the colons were not
changed back to periods. Probably no matter; see my further comment.
The second level text says:
Cause . . . . . : A select or omit error occurred in record 1,
record format *FIRST, member number 1 of file RBAMI in library
RBTALRLIB, because of condition 6 of the following conditions:
… 6 - A data mapping error occurred on the field before the select
or omit operation was attempted."
I know I probably have a setting wrong in my RPG LE program but I am
at a total loss as to where to look. Why would RPG LE be
interpreting the statement differently than Run SQL Script?
Seems probable, the DATFMT defaults differ betwixt; the CHAR(8)
casting shown in the error as the effect, is conspicuously incorrect,
per an inability of that result-type to contain the required
10-characters in the form of 'YYYY-MM-DD' (as shown, delimited with
apostrophes) without any truncation, and thus is invalid as a
representation of the date portion of a TIMESTAMP string (examples shown
above, from doc reference). The RPG SQL seems to have been using a
two-digit-year format when that error was issued; that is when a DATE
cast to CHAR would be CHAR(8) vs CHAR(10).
Clearest IMO, is to overcome the issue by being explicit in the
expression. Thus, irrespective any *default* setting, the desired CHAR
casting will be effected properly; i.e. effected with the desired
10-character Date-string, preceding the first dash as constant in the
expression:
" timestamp(
CHAR( T01.MIDTSY , ISO ) || '-' ||
substr( digits( T01.MIMTIM ), 1, 2) || '.' ||
substr( digits( t01.MIMTIM ), 3, 2) || '.00'
) as Message_Timestamp
"
Similarly, the option exists [¿more easily since IBM i 7.3; using the
DECIMAL scalar
(
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/db2/rbafzscadec.htm)
for its "Datetime to Decimal" effect?] to eliminate any separators, and
instead generate the 14-character non-delimited string representation of
a TIMESTAMP; e.g. the untested expression:
" timestamp(
digits( decimal( T01.MIDTSY ) ) concat
digits( T01.MIMTIM ) concat '00'
) as Message_Timestamp
"
There is the less explicit option of ensuring that the RPG SQL
establishes the proper Date Format (DATFMT) [note: in other scenarios,
additionally, the Date Separator (DATSEP)]. That is by using the SET
OPTION as Charles suggested in
(
https://archive.midrange.com/midrange-l/201803/msg00686.html) as a
reply to the OP. While a solution for the program, the less explicit
expression, if copied to a STRSQL session or to some other SQL scripting
feature for which the Date Format is not matching the requirements, the
error could reappear.
midrange.com
