MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » October 2012

AW: Bug in SQL parser???



fixed

doesn't raise any SQL error, even if it seems it doesn't return records in
the expected order, but I'm not sure of this, there could be some bug in
untit tests... I'll let you know.

If you need your result set in a specific sequence, you need to add an order
by clause to your final select.
The main goal of the optimizer is to get access to the data as fast as
possible. In this way it may be faster to use an index in a different order
and sort the result temporarily (if an additional order by is needed).

... and IMHO you do not need 2 sub-selects a single one should be
sufficient:

SELECT SUB.*
FROM (select a.Field1, b.Field1,
row_number() over (Order by a.Field1)
FROM A inner join B on A.X = B.Y) SUB (New_Name, Field1_Too, Rnk)
WHERE rnk >= ? AND rnk < ?
Order By Rnk;

By the way, the above query is not used neither in H2 nor in MySQL, since
these DBs support the LIMIT clause.
If you only want the first 5 (or 10 or whatever rows), you can specify FETCH
FIRST x ROWS ONLY at the end of your SELECT-Statement:

Select a.Field1, b.Field1
From A join B on a.Key = b.Key
Order By a.Field1
Fetch First 10 Rows Only;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Franco Lombardo
Gesendet: Friday, 26.10 2012 09:56
An: midrange-l@xxxxxxxxxxxx
Betreff: Re: Bug in SQL parser???

Chuck,

as I said in the ng answer, your syntax

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

doesn't raise any SQL error, even if it seems it doesn't return records in
the expected order, but I'm not sure of this, there could be some bug in
untit tests... I'll let you know.

By the way, the above query is not used neither in H2 nor in MySQL, since
these DBs support the LIMIT clause, but it was create to support MS SQL
Server and....DB2 LUW!!!

Thank you again!!!

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.







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

This mailing list archive is Copyright 1997-2014 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