Getting first 100 records from the table in Progress OpenEdge database (e.g. SELECT TOP 100..)

Question

How can I get a limited number of records from the table in Progress OpenEdge database?

Something like in SQL:

SELECT TOP 100 * FROM MyTable

The only ugly solution I can find is looping through all records and breaking when 100 of them were displayed. But feels like there should be some better way of doing it.

Flasket

Answer 1

If you are using the 4GL you might also want to look at using OPEN QUERY and MAX-ROWS to achieve the result that you are looking for. The following shows a traditional FOR EACH loop with a counter and then a QUERY with MAX-ROWS:

define variable i as integer no-undo.
define frame a with 10 down.

for each customer no-lock break by name:
  i = i + 1.
  display i custNum name discount.
  if i >= 5 then leave.
end.

define query q for customer scrolling.

open query q for each customer no-lock break by name max-rows 5.

do i = 1 to 5 with frame a:
  get next q.
  display i custNum name discount.
end.

Deberadeberry

Answer 2

If you are using the SQL-92 engine then something like:

SELECT TOP 100 FROM pub.customer;

should work just fine.

If you are using the 4GL engine then you should not be attempting to blend SQL with 4GL. It will only lead to pain, misery and agony. The 4GL is not SQL. There are a few SQL-89 statements that were put in the 4GL a long, long time ago for marketing reasons. Trying to use them will result in severe emotional trauma. You have been warned.

Deberadeberry

Sadly I can only give you 1 upvote. Your words describe exactly the experience I dad ! – Ariminum

Answer 3

Please follow the link to download the file. Hoping that the question will answer on it OpenEdge Database

Capitular

linking to a 924 page manual on Open Edge database administration is not a good answer – Bisexual

Answer 4

DEFINE VARIABLE i AS INTEGER NO-UNDO.

FOR EACH customer NO-LOCK with width 320:

ASSIGN i = i + 1.

if i <= 100 then DISP CustNum Address Balance City Contact Country CreditLimit Discount
Name Phone PostalCode SalesRep State Terms .

END.

Herbertherbicide

Not a good solution. If you have a million customer records, this will keep going through the other 999,900 after you've displayed the first 100. – Autosuggestion