Virtual File Accessor File Variable Behaviour

PROIV Documentation

Click here to see this page in full context

↵

Virtual File Accessor (VFA) File Variable Behaviour

Virtual File Accessor File Variable Behaviour

Overview

When multiple constituent files in a Virtual File Accessor (VFA) share the same file variable, PROIV must determine which file’s data should populate that variable.
The Behaviour depends on the type of joins used (inner or outer) and whether NULL values exist in the data.

This section explains:

How PROIV handles overlapping file variables
How outer joins affect variable values
What workarounds and best practices to use for consistent Behaviour

1. Overlapping File Variables

When one or more constituent file definitions in a VFA use the same file variable name, the system must decide which file’s value takes precedence after each record is read.

This scenario can occur both inside and outside a VFA:

When the files are accessed individually, later file accessors overwrite earlier ones.
If a later file does not find a matching record, the value from the earlier file remains.

The VFA mimics this logic by following specific rules depending on join types.

Behaviour Rules

Case	Join Type	Value Used	Notes
1	All Inner Joins	The latest file’s value always overwrites previous ones — even if NULL	Later constituent file wins
2	Inner Joins + Left Outer Joins	For files before the first outer join: later file wins (even if NULL). After an outer join: uses the latest non-NULL value	Switches to “retain non-NULL” mode after first outer join
3	Outer Join without matching record	Retains the last valid value from earlier files	Prevents overwriting with PROIV NULL
4	No overlapping variables	Each variable remains independent	Safest configuration

Workarounds

If this is not the desired behaviour:

Use child file definitions with unique variable names to avoid overlapping.
Review Virtual File Accessor Considerations before using overlapping file variables.
Avoid relying on implicit overwrites when using mixed inner/outer joins.

2. Missing Values in Outer Joins

Outer joins can cause missing or unexpected data in shared variables when multiple files share the same variable name.

Unlike SQL, PROIV does not differentiate between identical file variable names (it does not use table aliases).
This means that when outer joins return no matching records, PROIV may skip updating those variable values.

To manage this, the VFA uses the SQL COALESCE() function to select the first non-NULL value — but only up to the first outer-joined constituent file. It will use the last value found by the last inner joined files (if any) and any found left outer joined tables. So if all the files after the first left outer join fail to find a matching row then the last value from inner joined files is used.

Behaviour Summary

Scenario	Behaviour	Example Outcome
Inner joins only	Each subsequent file overwrites previous values	Uses the latest value
Outer joins present	Uses the latest non-NULL value found	Skips NULLs from unmatched rows
Intentional NULLs in data	May be ignored by COALESCE logic	Earlier value may persist
No matching row in outer join	Retains last valid (non-NULL) value	Prevents overwrite with NULL

Workarounds and Performance Notes

If your design depends on NULLs, use unique file variable names per file.
COALESCE() can slightly affect performance if the variable is part of a sort order or index.
Consider adjusting sort orders to exclude shared variables when outer joins are present.

Key Takeaways

Use unique file variable names to ensure predictable Behaviour.
Be aware that outer joins and shared variables can lead to retained or missing values.
For complex joins, validate the VFA output carefully to confirm intended data flow.
Performance can vary depending on whether shared variables appear in the sort.

Comment on this topic

Topic ID: 520210