Virtual File Accessor Conventions and Limitations.

The virtual file accessor (VFA) must behave like other file accessors so that PROIV can use it but at the same time is limited compared to existing file accessors. This page describes the conventions that the VFA follows and the limitations it has. Even compared to a file accessor from a (single) SQL file definition.

Use

The VFA must be the primary file in a cycle. Hence there can be at most one VFA in a cycle.

The VFA must be in lookup file mode.

The VFA can be used in any PROIV function but is restricted to the types of cycle it can be used in those functions:

many-time (paging) update cycles;
many-time (paging) report cycles;
many-time (paging) document cycles;
many-time selection (file) cycles.

The VFA has an associated name. The names do not have to be unique in the function, as the VFAs have to be in different cycles. The name may match another file accessor in the same cycle. Giving the VFA a unique name in a PROIV function is good practice and makes error messages easier to understand.

Specification

The VFA is constructed from two or more file definitions. The same file definition cannot appear in a VFA more than once. Though a child file definition of one of the other file definitions with alternate file variables names can; allowing a self-join.

These constituent file definitions must one of the SQL types and be of the same SQL type e.g. “ORA”, “SQLSERVE”.

The constituent file definitions may have a logical database but all must have the same logical database.

The constituent file definitions must have the same codeset (see codeset).

The constituent file definitions cannot use PROIV encryption.

The VFA does not support the “Co / Div” flag (Co/Div and Operator Wrapper) where access to records is limited by an operator’s company or division. If the constituent file definitions do support this flag then explicit selection criteria on the join or results filtering must be added.

The VFA has security category and levels like any other file accessor. These are taken from its first constituent file definition only. If subsequent constituent files have a different category or level then this is ignored.

The VFA’s file variables are the file variables of its constituent files. Key file variables in the constituent files become key file variables in the VFA. So the VFA can uniquely identify each record and cope with one-to-many joins. See later for a discussion of file variable overlapping.

Joins

The initial constituent file of the VFA does not have join criteria as there’s nothing for it to join to. To simplify the user interface of Developer, it appears to have the selection criteria on the joined rows associated with it. These are for the whole VFA and not just the initial file but it’s a convenient place to put them.

Subsequent constituent files will have a join type and join criteria associated with them. A subsequent constituent file is joining to all the previous constituent files. There are two types of join: inner and left-outer. For the inner, all records returned by the previous files must match (one or more) records in this constituent file to be returned by the join. For the outer-left, all records returned by the previous files will still be returned but any matching records in this constituent file will be returned as well, or default values used if there is none.

How records are matched is controlled by the join criteria associated with subsequent constituent files. There must be a join condition against at least one of any of the previous constituent files (including the initial). There cannot be a join condition against itself or a later file.

There may be one or more condition in the join criteria against a PROIV variable or literal. This is of most use in left-outer joins where it filters the candidate rows for joining to the previous files.

A join condition against a previous file can only be certain operators:

“=” – values must be the same.
“MAX<=”, “MAX<”, “MIN>=”, “MIN>” – select one row from this constituent file where the value is the maximum/minimum less/greater (or equal) to the value in a previous constituent file. This is particularly useful for date indexed records. See advanced join operators for more details.(Virtual File Accessor Advanced Operators).

A join condition against a PROIV variable or literal can use the same operators as “SQL Select” (SQL Where Clause), but not “MAX<=”, “MAX<”, “MIN>=”, “MIN>”.

Row Filtering (Where Clause)

The first constituent file of the VFA has the selection criteria of the rows returned by the joined files associated with it. These selection criteria can be applied to any to the file variables in any constituent file and not just the initial one. The only difference between this and the “SQL Select” for a normal file accessor is that the constituent “File” containing the file variable must be specified.

The row filtering selection criteria cannot contain join criteria. The user can specify PROIV file variables to compare VFA’s constituent file variables against. Effectively a snapshot of these PROIV file variables (and other variable types) is taken after the cycle’s On Entry logic and used by the RDBMS when querying the VFA. They do not form additional join criteria applied by the RDBMS.

Overlapping File Variables

One or more constituent file definitions in a VFA may have a file variable with the same name. What updates its value after a record is read?

This situation can occur outside of the VFA as well. If the file definitions are used as individual file accessors then the value retrieved by earlier file accessors is overwritten by later file accessors. This is the convention adopted by the VFA.

So, if one or more constituent file definitions in a VFA have a file variable with the same name then the later file (in constituent file order) will populate the file variable. If this isn’t what the developer desires then they should use child file definitions with alternate file variable names to avoid them overlapping.

There are points you need to consider (see Virtual File Accessor Considerations) before using overlapping file variable names.

Sorting

The VFA supports sorting at the cycle level like other files accessors. The “Events” tab of the cycle’s “Properties” dialog contains the “Sort” options. Choose the “Sort” radio button and enter the file variables (in the VFA) you want to sort by. Ensure “Include in Sort?” is clicked in the “Behaviour” tab of the VFA’s “Properties” dialog.

The presence of the VFA in a cycle automatically enables the “SQL_Sort” (SQLSort) where the RDBMS will perform the sort using an “ORDER BY” clause. The VFA only allows this kind of SQL sorting. Introducing logic at certain points or explicitly disabling SQL Sort will cause PROIV to revert to its own sorting. In this case a run-time error will be generated saying the VFA does not support PROIV sorting and any attempt at sorting is abandoned.

Comment on this topic

Topic ID: 520205