Re: Syntax error in ACS Run SQL Scripts but not in STRSQL

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
--
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

--
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 electronic mail message and any attachments may contain confidential
or privileged information and is intended for use solely by the
above-referenced recipient. Any review, copying, printing, disclosure,
distribution, or other use by any other person or entity is strictly
prohibited under applicable law. If you are not the named recipient, or
believe you have received this message in error, please immediately notify
the sender by replying to this message and delete the copy you received

---------------------------------------------------------------------

--
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