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