How do I join to a first row in OpenEdge SQL? -

joining first row in sql should trivial joining inner select

select so_nbr, sod_line last_line   so_mstr   join sod_det on sod_det.rowid = (select top 1 sod_det.rowid                                      sod_det                                     sod_nbr = so_nbr                                     order sod_line desc) x  so_ord_date > curdate() - 60 

unfortunately get: error code -20302, sql state hy000: [datadirect][openedge jdbc driver][openedge] top clause used in unsupported context. (13694)

note, stuck on oe10.1c , not able upgrade.

according abe voelker's answer:

openedge 11.2 added support offset , fetch clauses sql select queries; versions of openedge below 11.2 not support offset/fetch.

from 11.2 product documentation "sql reference" document:

the offset clause specifies number of rows skip, before starting return rows query expression. fetch clause specifies number of rows return, after processing offset clause. 

it's worth noting top , offset/fetch clauses mutually exclusive - top cannot used in query uses offset or fetch.

try this

  select so_nbr,sod_line last_line   so_mstr        join sod_det on sod_det.rowid = (select sod_det.rowid                                          sod_det sod_nbr = so_nbr                                         order sod_line desc                                          fetch first 1 rows only) x   so_ord_date > curdate() - 60