Virtual File Accessor Advanced Operators

It is envisaged that most join criteria (against other constituent files) in the virtual file accessor (VFA) will use the equality operator. However, the VFA also offers these advanced operators:

“MAX<=” – match row on the left whose specified column has maximum value less than or equal to value in the column on the right;
“MAX<” – match row on the left whose specified column has maximum value strictly less than the value in the column on the right;
“MIN>=” – match row on the left whose specified column has minimum value greater than or equal to value in the column on the right;
“MIN>” – match row on the left whose specified column has minimum value strictly greater than the value in the column on the right.

The join criteria for a constituent file can only have one of these operators if any. If one of these operators is present in the join criteria then the other entries can only be against the same previous constituent file, or against literals / PROIV variables.

Consider the following example to illustrate their use:

Advanced operators

The file “EG3_OnLoan” links books to borrowers in a library. It is (inner) joined with “EG1_Books” and “EG1_Borrowers” to get details of the books and borrowers. The function also wants to know the address of the borrower when the book was borrowed.

“EG3_Addresses” contains the current and past addresses of borrowers. “Address_FromDate” tells us that the corresponding address is applicable from this date until next address date.

When joining “EG3_Addresses” to the previous files, the operator “MAX<=” is used as we want the borrower’s latest (“MAX”) address that is on or before (“<=”) the date on which the book was borrowed. However, it’s not the address of any borrower. It has to be the address of the borrower of this book, so the borrower id of the address has to match as well.

Note that the join criteria for “EG3_Addresses” are both against the same previous constituent file “EG3_OnLoan”.

Comment on this topic

Topic ID: 520206