Re: Stuck with an SQL error

[Charles Wilt <charles.wilt@xxxxxxxxx>]

exec SQL
  set option datfmt= *iso, timfmt = *iso;

Charles


On Fri, Mar 16, 2018 at 3:03 PM, Kendall Kinnear <
Kendall.Kinnear@xxxxxxxxxxx> wrote:

> I'm stuck and I hope someone on the list can help me.
>
> 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"
>
> 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"
>
> 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."
>
> 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?
>
> Respectfully,
> Kendall Kinnear
> System Analyst
> Standard Motor Products, Inc.
> Mobile: 940-293-7541
>
> ________________________________
> Please consider the environment before printing this email.
>
> The content of this e-mail (including any attached files) is confidential
> and may be privileged and protected by law. It is intended solely for the
> purpose of the person to whom it is addressed. If you are not the intended
> recipient of this message, please notify the sender immediately and delete
> the message (inclusive of any attached files). In addition, if you are not
> the intended recipient of this message, any disclosure, copying,
> distribution or taking any action in reliance of the contents of this email
> is strictly prohibited.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: http://amzn.to/2dEadiD