Virtual Fields – What are they, and
why should you care?
Datatel
data is stored in ‘normalized’ data files. This means that each file contains
related data only. Each record in each file has a unique ‘key’. This may be an
ID number, a field like semester id (2002SP), a combination (ID*TERM), or a
system generated id (2345).
A
Query can only access one file. If you need data from another file in a query,
we (AIS) need to create a virtual field that is a part of the file you’re
working with, but contains data from a field in another file. In order to do
this, we need to know what file you’re working with, and what field you need in
what other file. To get this information, find the data on a screen. With the
cursor on that field, press field help (F1) and return. A help screen will pop
up with the heading “Help information
for the field xxxxx”. Write down the field name. then go to the RDEL screen,
enter the field name, and note the file name. It may also be useful for us to
know the database usage type.
With
this information, we can build a virtual field. We do need to find a key from
the file you’re working with to the file where the data is. This may actually
mean going to several files.
There
are several types of virtual fields. The above field is called a ‘trans’ or
translate field. The other types are:
Concatenation
– putting two fields together: LAST’,
‘:FIRST
Extraction
– pulling part of a field out:
MIDDLE[1:1], Zip[1,5]
Conditional
– if, then, else: If SAT.HIGH >
ACT.HIGH.CONVERTED THEN SAT.HIGH ELSE
ACT.HIGH.CONVERTED
Conversions
– Dates are stored as the number of days since Dec 31, 1973. Numbers are stored
without decimal points. Conversions put these fields into readable format
Formatting
– data can be placed in fixed length fields, either left or right justified:
FMT(DESC,’40L’):SPACE(4):FMT(AMT,’5R’)
Selecting
specific multivalued fields – either specific one(first, second, third…) or one
that matches a specific value
Subroutines
– access a small program written by AIS. These programs need specific input,
and will produce one result: SUBR('XADB17',X.PERSON.HIGH.ACT) returns high ACT
converted.
Any
combination of the above.
Ó Copyright 2002