Distinction between real and virtual columns

The distinction between the real and virtual columns can be best illustrated by example.

Consider an accounting table called ACCMST that contains an 8 digit column called CHTACC (chart of accounts) that looks like this:

       +---+---+---+---+---+---+---+---+

       | 0 | 8 | 6 | 3 | 4 | 3 | 1 | 2 |

       +---+---+---+---+---+---+---+---+

This example is for chart of accounts number 08634312. In actual fact the chart of accounts has three components. These are:

1.  A 2 digit company number (08),

2.  A 2 digit division number (63), and

3.  A 4 digit cost center (4312).

Since the company number, division number and cost center can be "derived" directly from the chart of accounts number it would be possible to define in table ACCMST three "virtual" columns called COMPNO, DIVNUM and COSTCT.

If this was done the definition of table ACCMST would contain:

Column Alphanumeric Length A "real" column called CHTACC 8 A "virtual" column called COMPNO 2 A "virtual" column called DIVNUM 2 A "virtual" column called COSTCT 4

Of course there is a missing link. That is the definition of how virtual columns COMPNO, DIVNUM and COSTCT are "derived" from the real column CHTACC.

There are two methods available that allow you to specify how a virtual column is derived:

• The first method is the Extended definition of virtual columns function, which allows you to perform the most commonly used actions to derive virtual columns from real columns and vice versa.

• The second method is achieved by entering program code. This method is by far the most flexible method of specification for more complex actions but has platform implications for code.

In this example, the virtual columns COMPNO, DIVNUM and COSTCT could have been derived by using the "Extended definition of a virtual column" function by Substringing the real column into CHTACC OR by entering a few lines of RPG/400 code that mapped column CHTACC into columns COMPNO, DIVNUM and COSTCT.

Once this has been done COMPNO, DIVNUM and COSTCT would appear to be in table ACCMST. However, if the table was examined it would be found that the columns do not actually exist. Hence the name "virtual" columns.

Note that columns COMPNO, DIVNUM and COSTCT are only used when reading (i.e. inputting) from the ACCMST table. When writing to or updating the ACCMST table they have no particular meaning.

This need not be so, in fact virtual columns COMPNO, DIVNUM and COSTCT could be used to "re-assemble" column CHTACC when outputting to the table. This feature of virtual columns is extremely useful in some situations.

Also See

Examples of Virtual Column Applications

Virtual Column Concepts