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. For details, see Joining Data Sources.

Reference: Usage Notes for Creating Virtual Fields

  • If you do not use the KEEPDEFINES parameter, when a JOIN is issued, all pre-existing virtual fields for that data source are cleared except those defined in the Master File.
  • To join structures using a virtual field with the source, make sure the DEFINE follows the JOIN command. Otherwise, the JOIN command clears the temporary field. For an explanation of reporting on joined data sources, see Joining Data Sources.
  • If no field in the expression is in the Master File or has been defined, use the WITH command to identify the logical home of the defined calculation. See Establishing a Segment Location for a Virtual Field.
  • WITH can be used to move the logical home for the virtual field to a segment lower than that to which it would otherwise be assigned (for example, to count instances in a lower segment).
  • You may define fields simultaneously (in addition to fields defined in the Master File) for as many data sources as desired. The total length of all virtual fields and real fields cannot exceed 32,000 characters.
  • When you specify virtual fields in a request, they count toward the display field limit. For details on determining the maximum number of display fields that can be used in a request, see Displaying Report Data.
  • Virtual fields are only available when the data source is used for reporting. Virtual fields cannot be used with MODIFY.
  • A DEFINE command may not contain qualified field names on the left-hand side of the expression. If the same field name exists in more than one segment, and that field must be redefined or recomputed, use the REDEFINES command.
  • Using a self-referencing DEFINE such as x=x+1 disables AUTOPATH (see the TIBCO WebFOCUS® Developing Reporting Applications manual).
  • Field names used in the expression that defines the virtual field cannot be enclosed in single or double quotation marks. Any character string enclosed in quotation marks is treated as a literal string, not a field reference.
  • A DEFINE FILE command overwrites a DEFINE in the Master File with same name as long as you do not redefine the format (which is not allowed).

Syntax: How to Create a Virtual Field

Before you begin a report request, include

DEFINE FILE filename[.view_fieldname] [CLEAR|ADD]  
fieldname[/format] [(GEOGRAPHIC_ROLE = georole] 
         [,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. 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 that complies with WebFOCUS field naming rules. Indexed field names in FOCUS data sources 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. The default value is D12.2. For information on field formats, see the Describing Data With TIBCO WebFOCUS® Language manual.
georole
Is a valid geographic role. The following is a list of default geographic roles.
  • ADDRESS_FULL. Full address.
  • ADDRESS_LINE. Number and street name.
  • CITY. City name.
  • CONTINENT. Continent name.
  • COUNTY. County name.
  • COUNTRY. Country name.
  • GEOMETRY_AREA. Geometry area.
  • GEOMETRY_LINE. Geometry line.
  • GEOMETRY_POINT. Geometry point.
  • LATITUDE. Latitude.
  • LONGITUDE. Longitude.
  • POSTAL_CODE. Postal code.
  • STATE. State name.
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, enclosed in single quotation marks. The description displays in the tools that browse Master Files.
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:

SET EXTENDNUM=OFF
DEFINE FILE EMPDATA                                            
SALARY REDEFINES EMPDATA.SALARY =                       
 IF TITLE CONTAINS 'EXECUTIVE' THEN  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:

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

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.

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 TIBCO WebFOCUS® 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.

Displaying Virtual Fields

How to:

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

Syntax: How to Display Virtual Fields

? DEFINE

For more information, see the TIBCO WebFOCUS® Developing Reporting Applications manual.

Procedure: How to Display Virtual Fields

Click the Defined Fields tab in the Define tool.

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.

To clear all virtual fields for all data sources, issue the following command:

DEFINE FILE * CLEAR
END

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 TIBCO WebFOCUS® 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.

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:

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 TIBCO WebFOCUS® Developing Reporting 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

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:

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.

Increasing the Speed of Calculations in Virtual Fields

Virtual fields are compiled into machine code in order to increase the speed of calculations.

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.

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.

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.

Syntax: How to Define and Apply a Format Field

  • With a DEFINE command:
    DEFINE FILE filename 
    format_field/A8 = expression;
    END
  • In a Master File:
    DEFINE format_field/A8 = expression; $
  • In a request:
    COMPUTE format_field/A8 = expression;

    where:

    format_field
    Is the name of the field that contains the format for each row.
    expression
    Is the expression that assigns the format values to the format field.

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

TABLE FILE filename 
display fieldname/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.

Reference: Usage Notes for Field-Based Reformatting

  • Field-based reformatting is supported for TABLE and TABLEF. It works with StyleSheets, joins, and any type of data source.
  • Field-based reformatting is not supported for MODIFY, Maintain, MATCH, GRAPH, RECAP, FOOTING, HEADING, or text fields.
  • Although you can use a DEFINE or COMPUTE command to create the format field, you cannot apply a field-based format to a calculated or virtual field.
  • Field-based reformatting cannot be used on a BY sort field. It does work with an ACROSS field.
  • If a report column is produced using field-based reformatting, the format used for a total or subtotal of the column is taken from the previous detail line.
  • Explicit reformatting creates two display fields internally for each field that is reformatted. Field-based reformatting creates three display fields.
  • Field-based formats are applied at the final output phase of report processing, while specific formats are applied at the final output phase of report processing, while specific formats are applied prior to performing calculations. Therefore, the dynamically reformatted field will perform calculations, including summation, using the original format, while a field reformatted using a specific format will use the new format for calculations. Thus, there may be numeric differences in the final output because of rounding when using packed fields that reduce the precision.
  • Field-based reformatting works for alphanumeric fields in a HOLD file, although three fields are stored in the file for each field that is reformatted. To prevent the extra fields from being propagated to the HOLD file, specify SET HOLDLIST=PRINTONLY.
  • If the number of decimal places varies between rows, the decimal points are not aligned in the report output.

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:


Report showing dynamically and specifically formatted fields

Passing Function Calls Directly to a Relational Engine Using SQL.Function Syntax

Reference:

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.

Reference: Usage Notes for Direct SQL Function Calls

  • The expression containing the SQL.function call must be optimized or the request will fail with the following message:
    (FOC32605) NON OPTIMIZABLE EXPRESSION WITH SQL. SYNTAX         
  • The function must be a row-based scalar function and have a parameter list that consists of a comma-delimited list of column names or constants. If the function uses anything other than a list of comma separated values, the SQL. syntax cannot be used to pass it.
  • Constant DEFINE fields must be assigned a segment location using the WITH phrase.
  • Expressions should be declared as DEFINE fields, which are supported as parameters to an SQL function.
  • Data types are not supported as parameters to an SQL function. Examples of data type arguments are CHAR and INT for the CONVERT function and ISO, EUR, JIS, and USA for the CHAR function.

Example: Calling the SQL CONCAT Function in a Request

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;