I can´t select rownum in a query, when inner query contains SDO_ANYINTERACT
746767Jan 15 2010 — edited Jan 16 2010I have written a query in Oracle that looks like this:
select ID, NAME, GEOMETRY from
(
select a.*, rownum as rnm from
(
select ID, NAME, GEOMETRY from MY_TABLE
where SDO_ANYINTERACT(GEOMETRY, SDO_UTIL.SDO_GEOMETRY('POLYGON ((670000 6268000, 670000 6269000, 700000 6269000, 700000 6268000, 670000 6268000))')) = 'TRUE'
order by NAME asc
) a
)
where rnm <= 50 and rnm >= 40
The inner query is selecting rows from MY_TABLE using a bounding box. The outer queries are included to enable paging for the results.
For some odd reason this query does not yield any results. If I try and run the subquery:
select ID, NAME, GEOMETRY from MY_TABLE
where SDO_ANYINTERACT(GEOMETRY, SDO_UTIL.SDO_GEOMETRY('POLYGON ((670000 6268000, 670000 6269000, 700000 6269000, 700000 6268000, 670000 6268000))')) = 'TRUE'
order by NAME asc
It yields a list of results as expected. If i run the subquery:
select a.*, rownum as rnm from
(
select ID, NAME, GEOMETRY from MY_TABLE
where SDO_ANYINTERACT(GEOMETRY, SDO_UTIL.SDO_GEOMETRY('POLYGON ((670000 6268000, 670000 6269000, 700000 6269000, 700000 6268000, 670000 6268000))')) = 'TRUE'
order by NAME asc
) a
the result set is empty. Somehow rownum is preventing the query from yielding any results. If I remove rownum the results are returned as in the innermost query:
select a.* from
(
select ID, NAME, GEOMETRY from MY_TABLE
where SDO_ANYINTERACT(GEOMETRY, SDO_UTIL.SDO_GEOMETRY('POLYGON ((670000 6268000, 670000 6269000, 700000 6269000, 700000 6268000, 670000 6268000))')) = 'TRUE'
order by NAME asc
) a
What am I doing wrong here?? I am running Oracle 10g..
Edited by: user12456076 on 2010-01-15 06:14