LAG() on SDO_GEOMETRY column gives ORA-22901 error
502035Mar 27 2006 — edited Mar 29 2006Hi,
I have a table containing a date column and an SDO_GEOMETRY column. I'd like to use the LAG() function to access the value of the SDO_GEOMETRY column in the previous row, ordered by the date column; however this doesn't appear to work:
SELECT
logtime,
longlat,
LAG(longlat, 1, NULL) OVER (ORDER BY logtime) AS prev_longlat
FROM
neve
ORDER BY logtime
logtime is of type DATE and longlat is of type MDSYS.SDO_GEOMETRY. The error I get is:
ERROR at line 4:
ORA-22901: cannot compare nested table or VARRAY or LOB attributes of an object type
I understand that SDO_GEOMETRY types can't be directly compared; however I didn't think the LAG() function needed to compare the value of the expression given as its argument, it's simply fetching it from a single row that has already been completely specified.
I think I might be able to work around this by creating a view that uses LAG() to fetch the primary key of the previous row, then use this view to select the SDO_GEOMETRY fields. But that's kludgy.