The EntitySpaces Community

Share and learn about the EntitySpaces Architecture.

Welcome to The EntitySpaces Community Sign in | Join | Help

The EntitySpaces Community » EntitySpaces Data Providers » OracleClientProvider » Top command in Oracle does not work the right way when you include order by

Top command in Oracle does not work the right way when you include order by

Last post 08-01-2008, 9:09 AM by David.Parsons. 2 replies.


	Sort Posts: Previous Next

07-31-2008, 8:30 AM 10456

geopoko2
Joined on 07-28-2008
Posts 2

Top command in Oracle does not work the right way when you include order by

The oracle "RowNum" reduction is done before the "ORDER BY" command. Which is basically just wrong, but does not work with your queries (don't get me started on Oracle oddities).

Below is the VB.NET code for entity spaces I used and the SQL generated. What needs to be generated to actually work is shown below that.

Code:

Me.Query.es.Top = 1
Me.Query.Where(Me.Query.UserId.Equal(pUserID))
Me.Query.OrderBy(Me.Query.DateAccessed, esOrderByDirection.Descending)
Return (Me.Query.Load())

Code:

SELECT * 
FROM "TEG"."AEC_REPORT_LAST_ACCESS" 
WHERE "USER_ID" = :USER_ID1 
  AND ROWNUM <= 1 
ORDER BY "DATE_ACCESSED" DESC

Code:

SELECT *
FROM (SELECT * 
      FROM TEG.AEC_REPORT_LAST_ACCESS 
      WHERE USER_ID = :USER_ID1 
      ORDER BY DATE_ACCESSED DESC)
WHERE ROWNUM <= 1

Report abuse

07-31-2008, 4:56 PM 10469 in reply to 10456

Mike.Griffin
Joined on 01-14-2007
Indianapolis
Posts 2,868

Re: Top command in Oracle does not work the right way when you include order by

We will test this and if you're correct (and you probably are) we will fix it. We are putting out a maintenance release on August 11th.

EntitySpaces | Twitter | BLOG | Please honor our Software License

Report abuse

08-01-2008, 9:09 AM 10514 in reply to 10456

David.Parsons
Joined on 01-14-2007
Posts 1,419

Re: Top command in Oracle does not work the right way when you include order by

The SQL Core spec does not include a definition for TOP or LIMIT, and neither does Oracle. EntitySpaces simulates TOP for Oracle as close as possible with ROWNUM, but as you point out, Oracle does have its peculiarities, and it will not produce the same results as SQL Server's TOP or MySQL's LIMIT in all cases.

ES2008 does support sub-queries:

Code:

Dim cqSub As New CustomerQuery("cSub")
cqSub.Where(cqSub.Manager.Equal(1))
cqSub.OrderBy(cqSub.DateAdded.Descending)

Dim cq As New CustomerQuery("c")
cq.es.Top = 1
cq.From(cqSub).As("sub")

Dim collection As New CustomerCollection()
collection.Load(cq)

LastQuery:

Code:

SELECT * 
FROM (
    SELECT * 
    FROM "HIERARCHICAL"."Customer" cSub 
    WHERE cSub."Manager" = :Manager1 
    ORDER BY cSub."DateAdded" DESC) sub 
WHERE ROWNUM <= 1

The preferred approach is to use one of the new functions from the SQL OLAP extensions. Oracle supports ROWNUMBER OVER(), and EntitySpaces implements it in the paging mechanism for Oracle and SQL Server. I think the following approach will not only be more readable than the sub-query, but will give you better performance:

Code:

Dim collection As New CustomerCollection()

collection.Query.es.PageNumber = 1
collection.Query.es.PageSize = 1
collection.Query.Where(collection.Query.Manager.Equal(1))
collection.Query.OrderBy(collection.Query.DateAdded.Descending)

collection.Query.Load()

LastQuery:

Code:

WITH "withStatement" AS (
    SELECT "Customer".*, ROW_NUMBER() OVER(
        ORDER BY "DateAdded" DESC) AS ESRN 
    FROM "HIERARCHICAL"."Customer" 
    WHERE "Manager" = :Manager1) 
SELECT "withStatement".* FROM "withStatement" 
WHERE ESRN BETWEEN 1 AND 1 
ORDER BY ESRN ASC

David Neal Parsons
www.entityspaces.net

Report abuse