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:
Type and length conversions
When the type (i.e. alpha, packed or signed) or length of a column is inappropriate or inconvenient for common usage requirements a virtual column can be established.
For instance a 15 digit numeric column that never contains more than 999 can annoy users in reporting environments because it wastes 12 spaces on the report. A virtual column containing only three digits can be set up for use on reports.
Likewise an alphanumeric date may annoy users because it cannot be printed with an edit code/word. A numeric virtual column could be set up to solve this problem.
Aggregation and accumulation
Many database tables contain "arrays". This most commonly takes the form of a series of columns like SAL01, SAL02, SAL03 ...... SAL12 representing company sales for each of the months of the year.
Virtual columns can be used to aid users when working with these type of structures, particularly in the reporting and inquiry environments.
For instance virtual column SALYR could be defined to contain the total of columns SAL01 -> SAL12.
Similarly virtual columns SALQ1 (containing total of SAL01 -> SAL03), SALQ2 (containing total of SAL04 -> SAL06), and so on, could be defined to contain quarterly sales totals.
Date conversion
When a date is held in a real column in format YYMMDD it can be easily mapped into a virtual column in format DDMMYY. The real column is the most commonly used format for ordering the table, but the virtual column format is the most commonly used for printing.
Date to age conversion
A date (possibly of date of birth) column in a table can be converted into two virtual columns containing "age in years" and "age in months". Note that derivation logic here would actually involve the date column in the table and the current date (which is why the "age" cannot be stored in the database table - it will be wrong tomorrow).
Note that the logic involved here is more complex. In fact many sites would have a subroutine to do the job. Since derivation logic is specified to LANSA as RPG code it is easy to call existing subroutines.
Dynamic data conversion
Just about any form of dynamic data conversion can be achieved by using virtual columns.
To take an example to the extreme, imagine that a new company requirement for printing customer names is that:
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
