Re: Bug in SQL parser???

See my reply on c.s.i.a.m implying that the SQL [¿H2?] being used by Activiti for their verification\test is apparently depending on a /lenient/ validation of the SELECT statement, and that the IBM DB2 for i seems to be correctly diagnosing the -5001 condition:
https://groups.google.com/d/msg/comp.sys.ibm.as400.misc/qTJ0GxZvavo/ibL9ojqMiF4J

Ignoring the "can't modify" comment, FWiW...

My other reply also includes alternative syntax for the same effective correction, but using the column-list of the "correlation-clause" for the NTE [the derived table that uses the correlation-name\table-designator "RES"] in the given query:

SELECT SUB.*
FROM
(select RES.* , row_number() over (Order by NEW_NAME) rnk
FROM
(select A.field1, B.field1 /* instead of NEW_NAME here */
from A inner join B on A.X = B.Y
) AS RES ( NEW_NAME, FIELD1_TOO ) /* name NEW_NAME here */
) SUB
WHERE SUB.rnk >= ? AND SUB.rnk < ?

Although I can not attempt\verify, the following modified query I expect will also effect the same correction [i.e. no SQL5001] and produce the expected results; adding the ORDER BY clause to the NTE, and adding an ORDER OF clause to the OLAP /ranking/ query to effect the same _order of_ the NTE:

SELECT SUB.*
FROM
(select RES.* , row_number() over (Order by order of RES) rnk
/* use the _ORDER OF_ the NTE for ranking */
FROM
(select A.field1, B.field1
from A inner join B on A.X = B.Y
order by A.field1 /* have the NTE collate by A.FIELD1 */
) RES
) SUB
WHERE SUB.rnk >= ? AND SUB.rnk < ?

Regards, Chuck

On 25 Oct 2012 10:07, Franco Lombardo wrote:

<disclaimer> I posted this message on comp.sys.ibm.as400.misc too:
sorry for the cross-post, but that newsgroup seems a bit neglected
</disclaimer>

I'm trying to support AS400 database in Activiti
(http://www.activiti.org).
This open source project creates lots of query like this:

SELECT SUB.*
FROM
(select RES.* , row_number() over (Order by A.field1) rnk
FROM
(select A.field1, B.field1
from A inner join B on A.X = B.Y
) RES
) SUB
WHERE SUB.rnk >= ? AND SUB.rnk < ?

Unfortunately this query fails with msg SQL5001 - Column qualifier
or table A undefined.

Note that if I change my statement this way:

SELECT SUB.*
FROM
(select RES.* , row_number() over (Order by NEW_NAME) rnk
FROM
(select A.field1 NEW_NAME, B.field1
from A inner join B on A.X = B.Y
) RES
) SUB
WHERE SUB.rnk >= ? AND SUB.rnk < ?

all works fine (but I can't modify Activiti this way :-).
Is it a bug of SQL parser or I don't understand something?
Any suggestion?