Hi

Are you uncommenting both lines? In that case you need to replace ,CTE2 by WITH CTE2 to get it work.

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

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

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

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.

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

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.

------------------------------
Satid S
------------------------------