Consultor Eletrônico

Status: Verified

GOAL:

How to retrieve records in a dynamic-query using a Record ROWID.

FACT(s) (Environment):

Progress 9.x

FIX:

Here you will see the correct syntax for using variables in a WHERE clause and the correct method to use within a ROWID.

This example code is not responding to any logic because the ROWID is not a sequential value. Although the TO-ROWID function converts a properly formatted string to a ROWID value, there is no guarantee that this value corresponds to an existing record of your Database.

Example Code using the Sports2000 database

DEFINE VARIABLE iForClause AS CHARACTER NO-UNDO.
DEFINE VARIABLE iWhereClause AS CHARACTER NO-UNDO.
DEFINE VARIABLE iByClause AS CHARACTER NO-UNDO.
DEFINE VARIABLE iQuery AS CHARACTER NO-UNDO.
DEFINE VARIABLE iQueryHdl AS HANDLE NO-UNDO.
DEFINE VARIABLE bbegin AS ROWID NO-UNDO.
DEFINE VARIABLE bend AS ROWID NO-UNDO.
DEFINE VARIABLE vbegin AS CHARACTER NO-UNDO.
DEFINE VARIABLE vend AS CHARACTER NO-UNDO.

FIND FIRST customer NO-LOCK.

ASSIGN bbegin = ROWID(customer)
vbegin = STRING(bbegin).

FIND LAST customer NO-LOCK.

ASSIGN bend = ROWID(customer)
vend = STRING(bend).

DEFINE QUERY qcust FOR customer SCROLLING.

DEFINE FRAME f-a
customer.NAME FORMAT "x(30)"
WITH CENTERED ROW 5 10 DOWN.

ASSIGN iForClause = 'For each customer no-lock '
iWhereClause = 'where rowid(customer) >= to-rowid( "'+ vbegin + '") and ':U +
'rowid(customer) <= to-rowid( "'+ vend + '")'
iByClause = 'BY customer.custnum '.
iQuery = iForClause + iWhereClause + iByClause + 'indexed-reposition'.
iQueryHdl = QUERY qcust:HANDLE.

iQueryHdl:query-prepare(iQuery).
iQueryHdl:query-open().

GET FIRST qcust.
IF AVAILABLE(customer) THEN
DO:
DISPLAY customer.NAME WITH FRAME f-a.
DOWN 1 WITH FRAME f-a.
END.
ELSE
LEAVE.

REPEAT:
GET NEXT qcust.
IF AVAILABLE(customer) THEN
DO:
DISPLAY customer.NAME WITH FRAME f-a.
DOWN 1 WITH FRAME f-a.
END.
ELSE
LEAVE.
END.