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

Question

Joining to first row in SQL should be as trivial as simply joining to an inner select

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

Unfortunately I get: Error code -20302, SQL state HY000: [DataDirect][OpenEdge JDBC Driver][OpenEdge] TOP clause used in unsupported context. (13694)

Note, I am stuck on OE10.1C and will not be able to upgrade.

score 1 · Answer 1 · edited May 23 '17 at 11:45

According to Abe Voelker's Answer:

OpenEdge 11.2 added support for OFFSET and FETCH clauses to SQL SELECT queries; versions of OpenEdge below 11.2 do not support OFFSET/FETCH.

From the 11.2 product documentation "SQL Reference" document:
The OFFSET clause specifies the number of rows to skip, before starting to return rows
from the query expression. The FETCH clause specifies the number of rows to return,
after processing the OFFSET clause.
It's worth noting that the TOP and OFFSET/FETCH clauses are mutually exclusive - TOP cannot be used in a query that uses OFFSET or FETCH.

Try this

  SELECT so_nbr,sod_line As last_line
  FROM so_mstr
       JOIN sod_det ON sod_det.rowid = (SELECT sod_det.rowid 
                                        FROM sod_det WHERE sod_nbr = so_nbr
                                        ORDER BY sod_line Desc 
                                        FETCH FIRST 1 ROWS ONLY) x
  WHERE so_ord_date > curdate() - 60

Unfortunately we are on 10.1C and will not have the opportunity to upgrade. Is there a way to achieve this on prior versions of OpenEdge? — Brett Ryan, Jun 26 '14 at 04:30

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

1 Answers1