Re: Query Manager SQL is getting SQL0104 error

On 17-Oct-2016 13:31 -0500, Jim Hawkins wrote:

On Monday, October 17, 2016 1:46 PM Jim Hawkins wrote:

We have a Query Manager SQL process with several statements that
runs as part of a nightly process. This has been in place for
years (I think it was written 2 or 3 of my predecessors ago)
(however it has been modified annually to update internal hard
coded dates--but that was done in January--).

Over the weekend it started blowing up with SQL0104 error "Token ;
was not valid. Valid tokens:<END-OF-STATEMENT>. "

Here are the first few lines of process:

0001.00
0002.00 --Setup Work File in Qtemp to hold new jobs invoices
0003.00
0004.00 Create table Qtemp/JobInvWk01
0005.00 (Job_No Char( 15 ) );
0006.00
0007.00 --Get Jobs already Invoiced
0008.00
0009.00 insert into qtemp/JobInvWk01
0010.00 select distinct job_No
0011.00 from salelib/saletotal;
0012.00

The indication appears to be the semicolon on the create table
statement. If I remove that semicolon, I get SQL0199 "Keyword
INSERT not expected. Valid tokens: IN ON NOT DATA LONG OBID UNIT
WITH INDEX VALUE APPEND RCDFMT."
(that really didn't come as a surprise.)

Note: We have not made a recent changes to the system (PTF's etc.)
and this code was not recently modified.
So I am perplexed at what is up with this. Why would it suddenly
take a dislike to the semicolon?

I am fairly sure that one of you fine folks can take a quick look
at this and will say "you need to do...."

For the archives, the aforementioned errors, the first as sqlcode -104 and the second as sqlcode -199 after the noted revisions, are each the expected results had the given source been compiled into a QMQRY object and then run with the Start Query Management Query (STRQMQRY) command; first for having compiled a statement with a trailing semicolon, and second, for the redacted version causing the second statement to be included as a suffix of the otherwise-valid first statement. As noted in another reply, the QMQRY allows for only *one* statement per compiled QM Query object.

Found it, it was really an error later in the code. Not sure how it
crept in, but we are good now. Thanks.

The reason I responded with the above text, for the archives, is that a reader might incorrectly infer the correction was something with the above source with two statements, apparently in a QMQRY, for lack of any indication otherwise; i.e. might be difficult to glean just what is meant by "it" for what was found "later in the code", given the original context of just some apparent QMQRY source.