It works for you because you add the commented text of all your test statements. Once you uncomment your test statement of the first CTE, the subsequent CTE is meaningless without using WITH in place of comma. This is why you received the error in your original question.
The error you indicated in your original question came from DB2 i Query Engine. Run SQL Script tool has no capability to do any SQL syntax check and produce such a kind of error message. (RSS is just a "glorified" text editor.) You can prove this by invoking the tool's menu View --> Job Log and a new window appears showing the job log of QZDASOINIT job in IBM i server that is serving your Run SQL Script session.
Original Message:
Sent: Mon May 06, 2024 09:53 AM
From: Matt Tyler
Subject: ACS RSS session returns 42601 when no obvious reason
Carlos/Satid,
The method I mentioned is allowed in RSS (Run SQL Scripts). I use it quite often (during query development). The main reason for having these small statements right after a CTE is to provide simplicity when needing to test a single or small chain of CTEs when a statement is long. Its not an issue with the query engine as I mentioned in my OP, I can run the statement I have giving me error message 42601 through Code for IBM i, DB2 for i extension without this error being given.
The SQL script is stored in a local workspace file. I have used other editor tools to look to see if there are special non-visible characters causing problems.
"If you want to use comma to define two CTEs within the same WITH clause, you must not add any statement after the first CTE definition." This statement cannot be true as I do this ALL the time. Oddly enough while writing up this reply, the statement that was giving me 42601 (and the statement i created the sample statement from) no longer reports error 42601.
"VS Code is a totally different product from IBM i ACS Run SQL Scripts tool. There is no basis to expect these two different products should behave in the same way to your same codes." So, this statement leads me to believe the error 42601 is coming from ACS RSS tool and not the server query engine even though it defined and referenced like it comes from the same server query engine that is used when I use the DB2 for i extension in VS Code. Executing a query and getting back an error from RSS that does not come from the server should not look like it does.
Thanks, Matt
------------------------------
Matt Tyler
Original Message:
Sent: Mon May 06, 2024 04:10 AM
From: Satid S
Subject: ACS RSS session returns 42601 when no obvious reason
Dear Matt
To add to Carlos (and assuming you encountered the issue in IBM i ACS Run SQL Scripts tool), when you uncomment -- select * from CTE1;, it breaks the chain of CTE definition that uses comma as a separator for the next CTE definition. So, , CTE2... becomes meaningless text. If you want to use comma to define two CTEs within the same WITH clause, you must not add any statement after the first CTE definition.
This means if you rewrite the code to
with CTE1 as (select ... from ... ) , CTE2 as (select ... from CTE1 ...)
-- select * from CTE1;
-- select * from CTE2;
select ... from CTE2 ...
It would serve the same purpose that you have BUT ALSO helps you avoid the anomaly you encountered. To be a good programmer, it pays to have a disciplined mind.
VS Code is a totally different product from IBM i ACS Run SQL Scripts tool. There is no basis to expect these two different products should behave in the same way to your same codes.
------------------------------
Satid S
Original Message:
Sent: Fri May 03, 2024 04:10 PM
From: Matt Tyler
Subject: ACS RSS session returns 42601 when no obvious reason
When I write my queries that contain CTE's (common table expressions), I often write a small simple select statement in between any additional CTE and the main query so that I can debug the statement.
An example of the pattern I am talking about.
<code>
with CTE1as(select ... from ... )
-- select * from CTE1;
,CTE2 as (select ... from CTE1 ...)
-- select * from CTE2;
select ... from CTE2 ...
</code>
In the query I am having troubles with right now if I uncommment the line "select * from cte1" it runs. If i uncomment the line "select * from CTE2;" it does not run but it gives me the following.
" SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token ; was not valid. Valid tokens: <END-OF-STATEMENT>."
Now if I run the same exact statement in VSCode with Code for IBM i installed and the Db2 for i installed I have no issues running any of the testing lines.
My question is WHY? There are no special characters in the source that I can find that I can account for getting the error.
Thanks, Matt
------------------------------
Matt Tyler
------------------------------