Using Access:File.Previous() with SQL filtered records

questions
,
1

Hi there,

I need to know whether we have some way to use Access:File.Previous() with SQL Query returned data.
For example I have below code in Clarion.

File{PROP:SQL} = ‘Select * FROM tablename WHERE id > 100’
Now i want to loop from the last record using Previous() method as below.
LOOP UNTIL Access:File.Previous()
I am getting some error because I think it is checking from 1st record if there is any previous record.

Can someone help me on this please.

2

Put an ORDER BY on your query and sort it how you want to retrieve the rows

3

Hi @amardeep_srivastav

I think using PREVIOUS wouldn’t work as expected when issuing a PROP:SQL over a SET() in Clarion for a key reason: PROP:SQL sets the SQL query for the file, but it doesn’t inherently establish the current record position or the direction for record traversal. PREVIOUS is designed to read records in reverse sequence based on the current position and sequence established by a SET statement. Without a SET statement to define the starting position and sequence, PREVIOUS cannot reliably navigate through the records in the desired order, especially if the intent is to iterate in reverse order from a specific point.

Here’s how you might modify your code to get what you need:

File{PROP:SQL} = 'SELECT * FROM tablename WHERE id > 100 ORDER BY id DESC'
LOOP UNTIL Access:File.Next()
   ! Process each record
END

I hope this helps

Mark

1 Like
4

Thanks for the help. But do we have any other approach like creating cursor or something else so we can iterate from some point in reverse order

5

The select statement from Mark will return the items from SQL in descending order, which means you can run through them using Access:File.Next(). Note the DESC at the end of his select statement.

Because the items are returned in descending order, you can process them with next() - there is no need to look into cursors.