In this section:
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:
For more information, see the Describing Data With WebFOCUS Language manual.
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. For details, see Joining Data Sources.
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
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. 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.
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.
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.
Note: For information about missing attributes for virtual fields, see MISSING Attribute in a DEFINE or COMPUTE Command.
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
The following request redefines the salary field in the EMPDATA data source to print asterisks for job titles that contain the word EXECUTIVE:
SET EXTENDNUM=OFF 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
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:
SET EXTENDNUM=OFF 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
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.
DEFINE FILE filename ADD
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
You can display all virtual fields with the ? DEFINE command.
For more information, see the Developing Reporting Applications manual.
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.
To clear all virtual fields for all data sources, issue the following command:
DEFINE FILE * CLEAR END
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
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:
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;
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 Reporting Applications manual for more information on external indexes.
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
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:
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.
Virtual fields can be compiled into machine code in order to increase the speed of calculations.
When you enable compilation of expressions, expressions in IF criteria are also compiled. In some cases, an expression in an IF test may contain too many elements to be compiled (the limit is 8192). In this case, a FOC1975 message is generated to indicate that the expression could not be compiled. However, the report completes successfully, and the expression is evaluated correctly without compilation.
You can prevent virtual fields from being compiled, and have them interpreted at execution time, by changing the setting of the DEFINES SET parameter. Calculations are then interpreted each time the calculation is performed, which increases the execution time of the procedure.
In order to compile calculations, there must be a C compiler available on Windows and UNIX. On z/OS, calculations are compiled directly into machine code. If, for any reason compilation fails, WebFOCUS automatically suppresses the SET DEFINES=COMPILED option. For details on this parameter, see the Developing Reporting Applications manual.
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.
DEFINE FILE filename SAVE fld1/format1=expression1; fld2/format2=expression2; END TABLE FILE filename ... MODIFY FILE filename ... DEFINE FILE filename RETURN END
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.
DEFINE FILE filename format_field/A8 = expression; END
DEFINE format_field/A8 = expression; $
COMPUTE format_field/A8 = expression;
After the format field is defined, you can apply it in a report request:
TABLE FILE filename display fieldname/format_field[/just] END
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:
The SQL adapters can pass virtual fields that call certain SQL scalar functions to the relational engine for processing. This enables you to use SQL functions in a request even when they have no equivalent in the WebFOCUS language. The function must be row-based and have a parameter list that consists of a comma-delimited list of column names or constants. In order to reference the function in an expression, prefix the function name with SQL.
If the virtual field is in the Master File, both TABLE requests and those SQL requests that qualify for Automatic Passthru (APT) can access the field. If the virtual field is created by a DEFINE FILE command, TABLE requests can access the field. The function name and parameters are passed without translation to the relational engine. Therefore, the expression that creates the DEFINE field must be optimized, or the request will fail.
(FOC32605) NON OPTIMIZABLE EXPRESSION WITH SQL. SYNTAX
This example uses the WebFOCUS Retail demo sample. You can create this sample data source for a relational adapter by right-clicking the application in which you want to place this sample, and selecting New and then Samples from the context menu. Then, select WebFOCUS - Retail Demo from the Sample procedures and data for drop-down list and click Create.
The following request against the WebFOCUS Retail demo data source uses the SQL CONCAT function to concatenate the product category with the product subcategory.
SET TRACEUSER = ON SET TRACEOFF = ALL SET TRACEON = STMTRACE//CLIENT SET TRACESTAMP=OFF SET XRETRIEVAL = OFF DEFINE FILE WF_RETAIL CAT_SUBCAT/A50 = SQL.CONCAT(PRODUCT_CATEGORY, PRODUCT_SUBCATEG); END TABLE FILE WF_RETAIL PRINT CAT_SUBCAT BY PRODUCT_CATEGORY NOPRINT END
The trace output shows that the SQL function call was passed to the RDBMS.
SELECT CONCAT(T2."PRODUCT_CATEGORY",T2."PRODUCT_SUBCATEG"), T2."PRODUCT_CATEGORY", T2."PRODUCT_SUBCATEG" FROM wfr_product T2 ORDER BY T2."PRODUCT_CATEGORY" FOR FETCH ONLY;