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?
