Defining a Virtual Field

In this section:

How to:

Reference:

A virtual field can be used in a request as though it is a real data source field. The calculation that determines the value of a virtual field is performed on each retrieved record that passes any screening conditions on real fields. The result of the expression is treated as though it were a real field stored in the data source.

You can define a virtual field in the following ways:

Tip: If your environment supports the KEEPDEFINES parameter, you can set KEEPDEFINES to ON to protect virtual fields from being cleared by a subsequent JOIN command.


Top of page

x
Reference: Usage Notes for Creating Virtual Fields

Top of page

x
Syntax: How to Create a Virtual Field

Before you begin a report request, include

DEFINE FILE filename[.view_fieldname] [CLEAR|ADD]  
fieldname[/format] [TITLE 'line1[,line2 ...']
 [DESCRIPTION 'description']=expression; 
fieldname[/format][WITH realfield]=expression; 
fieldname[/format] REDEFINES qualifier.fieldname=expression;
.
.
.
END

where:

filename
Is the name of the data source for which you are defining the virtual field.

If the report request specifies an alternate view, use filename in conjunction with view_fieldname.

All fields used to define the virtual field must lie on a single path in the data source. If they do not, you can use an alternate view, which requires alternate view DEFINE commands. For an alternate view, virtual fields cannot have qualified field names or field names that exceed the 12-character limit. For information on alternate views, see Rotating a Data Structure for Enhanced Retrieval.

The DEFINE FILE command line must be on a separate line from its virtual field definitions.

view_fieldname
Is the field on which an alternate view is based in the corresponding request. You may need to use an alternate view if the fields used do not lie on a single path in the normal view.
CLEAR
Clears previously defined virtual fields associated with the specified data source. CLEAR is the default value.
ADD
Enables you to specify additional virtual fields for a data source without releasing any existing virtual fields. Omitting ADD produces the same results as the CLEAR option.
fieldname
Is a name of up to 66 characters. Indexed field names must be less than or equal to 12 characters. It can be the name of a new virtual field that you are defining, or an existing field declared in the Master File, which you want to redefine.

The name can include any combination of letters, digits, and underscores (_), and should begin with a letter.

Do not use field names of the type Cn, En, or Xn (where n is any sequence of one or two digits), because they are reserved for other uses.

format
Is the format of the field. All formats except text fields (TX) are allowed. The default value is D12.2. For information on field formats, see the Describing Data manual.
WITH realfield
Associates a virtual field with a data source segment containing a real field. For more information, see Usage Notes for Creating Virtual Fields.
line1, line2...
Are the lines of default column title to be displayed for the virtual field unless overridden by an AS phrase.
description
Is the description to be associated with the virtual field.
REDEFINES qualifier.fieldname
Enables you to redefine or recompute a field whose name exists in more than one segment. If you change the format of the field when redefining it, the length in the new format must be the same as or shorter than the original. In addition, conversion between alphanumeric and numeric data types is not supported.
expression
Can be an arithmetic or logical expression or function, evaluated to establish the value of fieldname (see Using Expressions). You must end each expression with a semicolon except for the last one, where the semicolon is optional.

Fields in the expression can be real data fields, data fields in data sources that are cross-referenced or joined, or previously defined virtual fields. For related information, see Usage Notes for Creating Virtual Fields.

END
Is required to end the DEFINE FILE command. END must be on its own line in the procedure.

Note: For information about missing attributes for virtual fields, see MISSING Attribute in a DEFINE or COMPUTE Command.



Example: Defining a Virtual Field

In the following request, the value of RATIO is calculated by dividing the value of DELIVER_AMT by OPENING_AMT. The DEFINE command creates RATIO as a virtual field, which is used in the request as though it were a real field in the data source.

DEFINE FILE SALES
RATIO = DELIVER_AMT/OPENING_AMT;
END
TABLE FILE SALES
PRINT DELIVER_AMT AND OPENING_AMT AND RATIO
WHERE DELIVER_AMT GT 50
END

The output is:

DELIVER_AMT  OPENING_AMT           RATIO
-----------  -----------           -----
         80           65            1.23
        100          100            1.00
         80           90             .89


Example: Redefining a Field

The following request redefines the salary field in the EMPDATA data source to print asterisks for job titles that contain the word EXECUTIVE:

DEFINE FILE EMPDATA                                            
SALARY REDEFINES EMPDATA.SALARY =                       
 IF TITLE CONTAINS 'EXECUTIVE' THEN 999999999999 ELSE          
 EMPDATA.SALARY;                                               
END                                                            
TABLE FILE EMPDATA                                             
SUM SALARY BY TITLE 
WHERE TITLE CONTAINS 'MANAGER' OR 'MARKETING' OR 'SALES' 
ON TABLE SET PAGE OFF
END

The output is:

TITLE                          SALARY
-----                          ------
EXEC MANAGER               $54,100.00
EXECUTIVE MANAGER     ***************
MANAGER                   $270,500.00
MARKETING DIRECTOR        $176,800.00
MARKETING EXECUTIVE   ***************
MARKETING SUPERVISOR       $50,500.00
SALES EXECUTIVE       ***************
SALES MANAGER              $70,000.00
SALES SPECIALIST           $82,000.00
SENIOR SALES EXEC.         $43,400.00


Example: Redefining a Field That Has the Same Name in Multiple Segments

The following request joins the EMPDATA data source to itself. This creates a two-segment structure in which the names are the same in both segments. The request then redefines the salary field in the top segment (tag name ORIG) so that all names starting with the letter L are replaced by asterisks, and redefines the salary field in the child segment (tag name NEW) so that all names starting with the letter M are replace by asterisks:

JOIN PIN IN EMPDATA TAG ORIG TO PIN IN EMPDATA TAG NEW AS AJ      
DEFINE FILE EMPDATA                                               
SALARY/D12.2M REDEFINES ORIG.SALARY = IF LASTNAME LIKE 'L%'  THEN 
                                      999999999999 ELSE ORIG.SALARY;
SALARY/D12.2M REDEFINES NEW.SALARY = IF LASTNAME LIKE 'M%' THEN   
                                     999999999999 ELSE NEW.SALARY * 1.2;
END                                                               
TABLE FILE EMPDATA                                                
PRINT ORIG.SALARY AS 'ORIGINAL' NEW.SALARY AS 'NEW'               
BY LASTNAME                                                             
WHERE LASTNAME FROM 'HIRSCHMAN' TO 'OLSON'                              
ON TABLE SET PAGE NOPAGE                                          
END

The output is:

LASTNAME                ORIGINAL              NEW 
--------                --------              --- 
HIRSCHMAN             $62,500.00       $75,000.00 
KASHMAN               $33,300.00       $39,960.00 
LASTRA           ***************      $138,000.00 
LEWIS            ***************       $60,600.00 
LIEBER           ***************       $62,400.00 
LOPEZ            ***************       $31,680.00 
MARTIN                $49,000.00  *************** 
MEDINA                $39,000.00  *************** 
MORAN                 $30,800.00  *************** 
NOZAWA                $80,500.00       $96,600.00 
OLSON                 $30,500.00       $36,600.00

Top of page

x
Defining Multiple Virtual Fields

How to:

You may wish to have more than one set of virtual fields for the same data source, and to use some or all of the virtual fields in the request. The ADD option enables you to specify additional virtual fields without clearing existing ones. If you omit the ADD option, previously defined virtual fields in that data source are cleared.

If you want to clear a virtual field for a particular data source, use the CLEAR option.



x
Syntax: How to Add a Virtual Field to Existing Virtual Fields
DEFINE FILE filename ADD

where:

filename
Is the data source.


Example: Adding Virtual Fields

The following annotated example illustrates the use of the ADD and CLEAR options for virtual fields:

1. DEFINE FILE CAR
   ETYPE/A2=DECODE STANDARD (OHV O OHC O ELSE L);
   END 
2. DEFINE FILE CAR ADD
   TAX/D8.2=IF MPG LT 15 THEN .06*RCOST
      ELSE .04*RCOST;
   FCOST = RCOST+TAX;
   END
  1. The first DEFINE command creates the TYPE virtual field for the CAR data source. For information about the DECODE function, see the Using Functions manual.
  2. Two or more virtual fields, TAX and FCOST, are created for the CAR data source. The ADD option allows you to reference ETYPE, TAX, and FCOST in future requests.

Top of page

x
Displaying Virtual Fields

How to:

You can display all virtual fields with the ? DEFINE command.



x
Syntax: How to Display Virtual Fields
? DEFINE

For more information, see the Developing Applications manual.


Top of page

x
Clearing a Virtual Field

The following can clear a virtual field created in a procedure:

Unlike fields created in a procedure, virtual fields in the Master File are not cleared in the above ways.



Example: Clearing Virtual Fields

The following annotated example illustrates the use of the CLEAR options for virtual fields:

1. DEFINE FILE CAR
   ETYPE/A2=DECODE STANDARD (OHV O OHC O ELSE L);
   END 
2. DEFINE FILE CAR CLEAR
   COST = RCOST-DCOST;
   END
  1. The first DEFINE command creates the TYPE virtual field for the CAR data source. For information about the DECODE function, see the Using Functions manual.
  2. The CLEAR option clears the previously defined virtual fields, and only the COST virtual field in the last DEFINE is available for further requests.

Top of page

x
Establishing a Segment Location for a Virtual Field

Virtual fields have a logical location in the data source structure, just like permanent data source fields. The logical home of a virtual field is on the lowest segment that has to be accessed in order to evaluate the expression, and determines the time of execution for that field. Consider the following data source structure and DEFINE command:

virtual field

DEFINE RATIO = DELIVER_AMT/RETAIL_PRICE ;

The expression for RATIO includes at least one real data source field. As far as report capabilities are concerned, the field RATIO is just like a real field in the Master File, and is located in the lowest segment.

In some applications, you can have a virtual field evaluated by an expression that contains no real data source fields. Such an expression might refer only to temporary fields or literals. For example,

NCOUNT/I5 = NCOUNT+1;

or

DATE/YMD = '19990101';

Since neither expression contains a data source field (NCOUNT and the literal do not exist in the Master File), their logical positions in the data source cannot be determined. You have to specify in which segment you want the expression to be placed. To associate a virtual field with a specific segment, use the WITH phrase. The field name following WITH may be any real field in the Master File.

For FOCUS data sources, you may be able to increase the retrieval speed with an external index on the virtual field. In this case, you can associate the index with a target segment outside of the segment containing the virtual field. See the Developing Applications manual for more information on external indexes.



Example: Establishing a Segment Location

The field NCOUNT is placed in the same segment as the UNITS field. NCOUNT is calculated each time a new segment instance is retrieved.

DEFINE FILE GGSALES
NCOUNT/I5 WITH UNITS = NCOUNT+1;
END

Top of page

x
Defining Virtual Fields Using a Multi-Path Data Source

Calculations of a virtual field may include fields from all segments of a data source, but they must lie in a unique top-to-bottom path. Different virtual fields may, of course, lie along different paths. For example, consider the following data source structure:

different virtual fields

This data source structure does not permit you to write the following expression:

NEWAMT = SALARY+GROSS;

The expression is invalid because the structure implies that there can be several SALARY segments for a given EMPLOYEE, and it is not clear which SALARY to associate with which GROSS.

To accomplish such an operation, you can use the alternate view option explained in Improving Report Processing.


Top of page

x
Increasing the Speed of Calculations in Virtual Fields

Virtual fields can be compiled into machine code in order to increase the speed of calculations. For more information, see Chapter 16, Improving Report Processing.


Top of page

x
Preserving Virtual Fields Using DEFINE FILE SAVE and RETURN

How to:

Occasionally, new code needs to be added to an existing application. When adding code, there is always the possibility of over-writing existing virtual fields by reusing their names inadvertently.

The DEFINE FILE SAVE command forms a new context for virtual fields. Each new context creates a new layer or command environment. When you first enter the new environment, all of the virtual fields defined in the previous layer are available in the new layer. Overwriting or clearing a virtual field definition affects only the current layer. You can return to the default context with the DEFINE FILE RETURN command, and the virtual field definitions remain intact.

Therefore, all the virtual fields that are created in the new application can be removed before returning to the calling application, without affecting existing virtual fields in that application.

For an example of DEFINE FILE SAVE and DEFINE FILE RETURN, see Joining Data Sources.

Note: A JOIN command can be issued after a DEFINE FILE SAVE command. However, in order to clear the join context, you must issue a JOIN CLEAR command if the join is still in effect. If only virtual fields and DEFINE FILE ADD were issued after a DEFINE FILE SAVE command, you can clear them by issuing a DEFINE FILE RETURN command.



x
Syntax: How to Protect Virtual Fields From Being Overwritten
DEFINE FILE filename SAVE 
fld1/format1=expression1;  
fld2/format2=expression2;
END
TABLE FILE filename ...
MODIFY FILE filename ...
DEFINE FILE filename RETURN
END

where:

SAVE
Creates a new context for virtual fields.
filename
Is the name of the Master File that gets a new context and has the subsequent virtual fields applied before the DEFINE FILE RETURN command is issued.
RETURN
Clears the current context if it was created by DEFINE FILE SAVE, and restores the previous context.

Top of page

x
Applying Dynamically Formatted Virtual Fields to Report Columns

How to:

Reference:

Dynamic formatting enables you to apply different formats to specific data in a column by using a temporary field that contains dynamic data settings.

Before you can format a report column using the dynamic format, you must create the report, then apply the temporary field to a column in the report. For example, you can create a temporary field that contains different decimal currency formats for countries like Japan (which uses no decimal places) and England (which uses 2 decimal places). These currency formats are considered dynamic formats. You can then apply the temporary field containing the dynamic formatting to a Sales column. In a report, the Sales column reflects the different currency formats for each country.

The field that contains the format specifications can be:

The field that contains the formats must be alphanumeric, and at least eight characters in length. Only the first eight characters are used for formatting.

The field-based format may specify a length longer than the length of the original field. However, if the new length is more than one-third larger than the original length, the report column width may not be large enough to hold the value (indicated by asterisks in the field).

You can apply a field-based format to any type of field. However, the new format must be compatible with the original format:

If the field-based format is invalid or specifies an impermissible type of conversion, the field displays with plus signs (++++) on the report output.



x
Syntax: How to Define and Apply a Format Field

After the format field is defined, you can apply it in a report request:

TABLE FILE filename
displayfieldname/format_field[/just]
END

where:

display
Is any valid display command.
fieldname
Is a field in the request to be reformatted.
format_field
Is the name of the field that contains the formats. If the name of the format field is the same as an explicit format, the explicit format is used. For example, a field named I8 cannot be used for field-based reformatting, because it is interpreted as the explicit format I8.
just
Is a justification option: L, R, or C. The justification option can be placed before or after the format field, separated from the format by a slash.


x
Reference: Usage Notes for Field-Based Reformatting


Example: Creating Dynamically Formatted Fields

The following request formats the DOLLARS2 field according to the value of the CATEGORY field and shows the numeric differences in sums using dynamic and static reformatting:

DEFINE FILE GGSALES
MYFORMAT/A8=DECODE CATEGORY ('Coffee' 'P15.3' 'Gifts' 'P15.0' ELSE 
'P15.2');
DOLLARS2/P15.2 = DOLLARS + .5;
END
TABLE FILE GGSALES
SUM DOLLARS2/MYFORMAT AS 'Dynamic' DOLLARS2/P10.2 AS 'Specific'
BY CATEGORY
ON TABLE SUBTOTAL
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
END

The output is shown in the following image:


Information Builders