I think this would be a problem in any SQL compiler.
Run just this statement:
select A.field1, B.field1 from A inner join B on A.X = B.Y
Your result will be rows with two columns, BOTH with a column name of "field1". How would the "over (Order by A.field1)" ever know about the aliases in a temporary subquery?
The aliases are only visible to the subselect and disappear after they are done (meaning not available outside of the subquery identified as RES). This is common functionality in any database I've ever dealt with.
-----Original Message-----
From: Franco Lombardo [mailto:f_lombardo@xxxxxxxxxxx]
Sent: Thursday, October 25, 2012 10:07 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Bug in SQL parser???
<disclaimer>I posted this message on comp.sys.ibm.as400.misc too: sorry for the cross-post, but that newsgroup seems a bit negleted</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?
Thanks!
Bye
Franco
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
http://www.francolombardo.net
Scala, Java, As400.....
http://twitter.com/f_lombardo
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
