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:

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




Scala, Java, As400.....

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.

Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2015 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact