Page History
&<img src="../resources/images/opentocr.png" title="Open Contents list" border="0"&>
You are here:
...
The Distinction between real and virtual columns section described a very simple example of three "virtual" columns that were directly derived from a "real" column by simple mapping.
These examples demonstrate some of the capabilities of the virtual column facility and common examples of its usage. Hopefully it can also be seen that the capabilities are only really limited by the imagination of the user:
Substring and concatenation
The example in the preceding section that involved breaking a column down into three virtual columns is an example of substring. Virtual columns can be used in all forms of substring. This includes substringing one real column into multiple virtual columns and substringing multiple real columns into one virtual column (also called concatenation).
Access to tables with no external description
Many older S/38 applications, and S/36 migrated IBM i applications, use tables that have no "external" column descriptions. These are also referred to as "internally described" tables. These tables appear as if they only contain one very large column which is in fact the entire table record, rather than a series of columns.
In such cases, all the columns in the table can be defined as virtual columns and then mapped from the record. This is in fact another example of substringing one real column into multiple virtual columns.
This case would also involve the mapping of virtual columns to the real column before output to the table. For more details, refer to examples 1 and 2 in Examples of Virtual Columns & Derivation Code in the LANSA for i User Guide.
Simplification and standardization of common data manipulations
In some applications the manipulation of data from a table in a particular way is very common and is repeated many times in differing applications.
For instance, consider an inventory table. The rule to determine whether a product requires re-ordering may be something like ("quantity on hand" + "quantity awaiting return" - "quantity on order") < "re-order level". This rule may be used in many different applications, particularly in the inquiry and reporting areas.
The logic in this rule can be simplified and standardized by using a virtual column. If a virtual column called RE-ORDER was set up in the table definition, then derivation code could be written to evaluate the rule and set RE-ORDER to "YES" or "NO". This approach has several advantages:
...
If the customer name column was called CUSNAM then it would be a simple task to define a virtual column in the table called PRTNAM that matched these requirements.
Also See
Distinction between real and virtual columns
Virtual Column Concepts
&<img src="../resources/images/opentoc-dark.png" title="Open Contents List" border="0"&>