I wonder if that could be a character set issue? Could it be possible that
the # is getting translated to another symbol?
Regards,
Luis
Sent from my Moto G phone. Please excuse my brevity.
On Jul 27, 2017 15:14, "Bill Reed" <breed@xxxxxxxxxxxxxx> wrote:
That was it. I was not using packages, but did just uncheck "Check SQL
Portability" under options, and now it will work with double quotes and
upper case. And by double quotes I mean the single character version, not
two separate single quotes.
Thank you!
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Sue
Romano
Sent: Thursday, July 27, 2017 3:10 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Syntax error in ACS Run SQL Scripts but not in STRSQL
In ACS, one of the options in the Options tab is Check SQL Portability.
You must have that selected. When you have this option checked, an
additional syntax check is made against every statement that is run to see
if it contains anything that is not part of the SQL standard. When you get
the SQL0362 warning, there is a prior message in the joblog that indicates
exactly what was flagged as non-standard. In your situation, it is telling
you it doesn't like the column name that contains a #; this is not a valid
character in names in the SQL standard.
If you are not interested in this information, uncheck the box and the
warnings will no longer be issued. STRSQL does not have an option to
provide this information, so you will never see this message through that
interface.
Sue Romano
IBM i SQL Development
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
--
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:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://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
--
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:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://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
midrange.com
