Is there a Progess abl way of only displaying a certain number of records, i.e. equivalent of rownum in SQL?

Question

When I am querying the progress database often I just want to see 10 or so records to see what the values are, not the entire table of records. Is there an equivalent of SQL's rownum in Progress abl?

SQL:

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

Progress ABL:

FOR EACH table_name NO-LOCK:
    display table_name.column_1, table.name.column_2. 
//just show me 10 records -- how can I do that?
END.

Unrelated, but: there is no rownum "in SQL" - that's feature that is only available in Oracle, but not in standard SQL (or any other SQL database that is) — , Jan 22 '22 at 14:47
Yes, this is OpenEdge. Thanks for the response. And also for the clarification that rownum is just Oracle. IMy objective is to limit the number of rows that are output, to say, 10 or 3. — Felice, Jan 22 '22 at 19:54

score 5 · Answer 1 · answered Jan 23 '22 at 01:13

5

Taking your request literally:

FOR EACH table_name NO-LOCK:
    display table_name.column_1 table.name.column_2
      with 10 down.
END.

Note: I removed an inappropriate comma between the field names.

You can also simply increment a counter and LEAVE when you have displayed N records.

answered Jan 23 '22 at 01:13

Tom Bascom

13,405
2
27
33

super helpful, thank you! – Felice Jan 24 '22 at 13:31

score 5 · Answer 2 · answered Jan 24 '22 at 08:39

5

I'll just add this odd and rarely seen feature as well. At least in systems I've worked with it never seem to get much attention.

This will leave the FOR-loop after 10 iterations.

DEFINE VARIABLE iCounter AS INTEGER NO-UNDO.
FOR EACH table_name NO-LOCK iCounter = 1 TO 10:

    DISPLAY table_name.table_field iCounter. 

END.

answered Jan 24 '22 at 08:39

Jensd

7,886
2
28
37

1

Neat! And it's hidden in plain sight: https://documentation.progress.com/output/ua/OpenEdge_latest/pdsoe/PLUGINS_ROOT/com.openedge.pdt.langref.help/rfi1424919813003.html – Stefan Drissen Jan 24 '22 at 10:35
1

@StefanDrissen Yes, a well unknown feature! – Jensd Jan 24 '22 at 10:36
that solution worked nicely, too! Thanks! – Felice Feb 03 '22 at 16:54

score 3 · Answer 3 · answered Jan 23 '22 at 08:46

Just for the sake of completeness, the ProDataset supports only reading the first 10 rows with an auto-stop - not requiring the user to hit cancel after 10 or the programmer keeping a counter and leave as suggested by Tom Bascom.

DEFINE TEMP-TABLE ttCustomer NO-UNDO
    LIKE Customer .

DEFINE DATASET dsCustomer FOR ttCustomer .

DEFINE QUERY qCustomer FOR Customer .

DEFINE DATA-SOURCE srcCustomer FOR QUERY qCustomer .

QUERY qCustomer:QUERY-PREPARE ("for each Customer by Balance descending") .

BUFFER ttCustomer:ATTACH-DATA-SOURCE (DATA-SOURCE srcCustomer:HANDLE) .

BUFFER ttCustomer:BATCH-SIZE = 10 .

DATASET dsCustomer:FILL () .

FOR EACH ttCustomer BY ttCustomer.Balance DESCENDING:
    DISPLAY ttCustomer.CustNum 
            ttCustomer.Name
            ttCustomer.Balance .
END.

Great information, I appreciate it! – Felice Jan 24 '22 at 13:31 — Felice, Jan 24 '22 at 13:31

Is there a Progess abl way of only displaying a certain number of records, i.e. equivalent of rownum in SQL?

3 Answers3