Re: Bug in SQL parser???

[CRPence <CRPbottle@xxxxxxxxx>]

  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?