Say, I've got a cursor with data records, with an identifying foreign key in csrData
:
| ID | Foreign_ID |
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
| 5 | 2 |
| 6 | 3 |
And a list of foreign IDs in a separate cursor csrMatches
:
| Match_Foreign_ID |
| 1 |
| 3 |
Is there a SET FILTER TO command I can issue against csrData
to get:
| ID | Foreign_ID |
| 1 | 1 |
| 2 | 1 |
| 6 | 3 |
I know this can be achieved using a SQL query, but the reporting framework I'm using limits me to SET FILTER TO
statements. The INLIST()
function works similarly, but I'd like to do filtering against a set of unknown size.
You can create a filter condition that will look up a given csrData.Foreign_ID
in csrMatches
and return true if it's found, false otherwise.
First, create an index on the Match_Foreign_ID
field in csrMatches
. Then, create a filter that uses indexseek()
to test each Foreign_ID
for inclusion in csrMatches.
select csrMatches
index on Match_Foreign_ID tag Foreign_ID
select csrData
set filter to indexseek(csrData.Foreign_ID, .f., "csrMatches", "Foreign_ID")
Using indexseek()
is slightly faster than seek()
because indexseek()
simply uses the index to check if the key is found, and does not move the active record pointer.