It's the # in the column names, use "DERRICK#" and "BLOCK#".
Coy Krill
Core Processing Administrator/Analyst
Washington Trust Bank
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Bill Reed
Sent: 2017 July 27 11:20
To: 'Midrange Systems Technical Discussion'
Subject: RE: Syntax error in ACS Run SQL Scripts but not in STRSQL
Importance: Low
It was all on one line in Run SQL Scripts, and I did that intentionally to make sure there were no line break issues. Typically however, I would break to a new line on the scripts version, probably at the "where" for readability. I have tried it both ways with no difference.
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Thursday, July 27, 2017 1:42 PM
To: Midrange Systems Technical Discussion
Subject: Re: Syntax error in ACS Run SQL Scripts but not in STRSQL
I know that doesn't all fit on one line in STRSQL. Perhaps the line break made a difference? Did you have it as one line in Run SQL Scripts?
The only error I got was that the file did not exist.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: Bill Reed <breed@xxxxxxxxxxxxxx>
To: MIDRANGE-L@xxxxxxxxxxxx
Date: 07/27/2017 01:22 PM
Subject: Syntax error in ACS Run SQL Scripts but not in STRSQL
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Occasionally I find that when trying to do an UPDATE or a DELETE using ACS
Run SQL Scripts, I will get a syntax error which prevents the operation,
but if I copy and paste the statement string into a green-screen STRSQL
session, it works just fine. (This only happens with UPDATE and DELETE,
not with SELECT.)
Here is an example:
------------------------------------------------------------------------------------------------------------------------
update rsprdlog set nonprod = 'Y' where derrick# = 'PAG' and block# =
17513;
------------------------------------------------------------------------------------------------------------------------
This is met with the following result (there should have been two rows
updated):
----------------------------------------------------------------------------------
SQL State: 0168I
Vendor Code: 362
Message: [SQL0362] Flagging detected an error in the current SQL
statement. Cause . . . . . : The SQL statement contains syntax that is
not standard at position 41. Recovery . . . : See previous messages in
the job log for a more complete description of the error. If conformance
to the standard is needed, change the statement to conform and try the
request again.
Statement ran successfully, with warnings (3 ms)
0 rows were affected by the statement
----------------------------------------------------------------------------------
Position 41 is the first letter of the conditioning column name
"derrick#". And yes, there are spaces between all the words even if
they're hard to see.
Job log shows:
----------------------------------------------------------------------------------
At token derrick#, the syntax of the SQL statement deviates from the ANS
standard. The previous two tokens were 'Y' and WHERE. One of the tokens
may be a reserved word.
----------------------------------------------------------------------------------
Well, yes, WHERE is a reserved word, but what's wrong with it here?
So (a) what am I doing wrong, and (b) if there is an error, why is STRSQL
more forgiving?
System is 7.1
ACS is 1.1.7.1 (just updated, but this happened with 1.1.6 as well).
Thanks,
Bill Reed
midrange.com
