Describing a Virtual Field: DEFINE

In this section:

How to:

Reference:

DEFINE is an optional attribute used to create a virtual field for reporting. You can derive the virtual field value from information already in the data source (that is, from permanent fields). Some common uses of virtual data fields include:

Virtual fields are available whenever the data source is used for reporting.

Syntax: How to Define a Virtual Field 

DEFINE fieldname/format [(GEOGRAPHIC_ROLE = georole)]
  [REDEFINES field2] = expression; 
  [,TITLE='title',] 
  [TITLE_ln='titleln', ... ,]
  [,DESC[CRIPTION]='desc',] 
  [DESC_ln='descln', ... ,]$

where:

fieldname

Is the name of the virtual field. The name is subject to the same conventions as names assigned using the FIELDNAME attribute. FIELDNAME is described in The Field Name: FIELDNAME.

format

Is the field format. It is specified in the same way as formats assigned using the USAGE attribute, which is described in The Displayed Data Type: USAGE. If you do not specify a format, it defaults to D12.2.

georole

Is a valid geographic role. Geographic roles can be names, postal codes, ISO (International Organization for Standardization) codes, FIPS (Federal Information Processing Standards) codes, or NUTS (Nomenclature of Territorial Units for Statistics ) codes. The following is a list of supported geographic roles.

  • ADDRESS_FULL. Full address.
  • ADDRESS_LINE. Number and street name.
  • CITY. City name.
  • CONTINENT. Continent name.
  • CONTINENT_ISO2. Continent ISO-3166 code.
  • COUNTRY. Country name.
  • COUNTRY_FIPS. Country FIPS code.
  • COUNTRY_ISO2. Country ISO-3166-2 code.
  • COUNTRY_ISO3. Country ISO-3166-3 code.
  • GEOMETRY_AREA. Geometry area.
  • GEOMETRY_LINE. Geometry line.
  • GEOMETRY_POINT. Geometry point.
  • LATITUDE. Latitude.
  • LONGITUDE. Longitude.
  • NUTS0. Country name (NUTS level 0).
  • NUTS0_CC. Country code (NUTS level 0).
  • NUTS1. Region name (NUTS level 1).
  • NUTS1_CC. Region code (NUTS level1).
  • NUTS2. Province name (NUTS level 2).
  • NUTS2_CC. Province code (NUTS level 2).
  • NUTS3. District name (NUTS level 3).
  • NUTS3_CC. District code (NUTS level 3).
  • POSTAL_CODE. Postal code.
  • STATE. State name.
  • STATE_FIPS. State FIPS code.
  • STATE_ISO_SUB. US State ISO subdivision code.
  • USSCITY. US city name.
  • USCITY_FIPS. US city FIPS code.
  • USCOUNTY. US county name.
  • USCOUNTY_FIPS. US county FIPS code.
  • USSTATE. US state name.
  • USSTATE_ABBR. US state abbreviation.
  • USSTATE_FIPS. US state FIPS code.
  • ZIP3. US 3-digit postal code.
  • ZIP5. US 5-digit postal code.
field2

Enables you to redefine or recompute a field whose name exists in more than one segment.

expression

Is a valid expression. The expression must end with a semicolon (;). Expressions are fully described in the Creating Reports With TIBCO WebFOCUS® Language manual.

Note that when an IF-THEN phrase is used in the expression of a virtual field, it must include the ELSE phrase.

TITLE='title'

Is a column title for the virtual field in the default language.

TITLE_ln='titleln'

Is a column title for the virtual field in the language specified by the language code ln.

DESC[CRIPTION]='desc'

Is a description for the virtual field in the default language.

DESC_ln='descln'

Is a description for the virtual field in the language specified by the language code ln.

Place each DEFINE attribute after all of the field descriptions for that segment.

Example: Defining a Field

The following shows how to define a field called PROFIT in the segment CARS: 

SEGMENT = CARS ,SEGTYPE = S1 ,PARENT = CARREC, $
   FIELDNAME = DEALER_COST ,ALIAS = DCOST ,USAGE = D7, $
   FIELDNAME = RETAIL_COST ,ALIAS = RCOST ,USAGE = D7, $
   DEFINE PROFIT/D7 = RETAIL_COST - DEALER_COST; $

Reference: Usage Notes for Virtual Fields in a Master File

Note the following rules when using DEFINE:

  • Alias. DEFINE does not have an alias.
  • Changes. You can change the virtual field declaration at any time.
  • A DEFINE FILE command takes precedence over a DEFINE in the Master with same name.
  • If the expression used to derive the virtual field invokes a function, parameter numbers and types are not checked unless the USERFCHK parameter is set to FULL.

Using a Virtual Field

A DEFINE attribute cannot contain qualified field names on the left-hand side of the expression. Use the WITH phrase on the left-hand side to place the defined field in the same segment as any real field you choose. This will determine when the DEFINE expression will be evaluated.

Expressions on the right-hand side of the DEFINE can refer to fields from any segment in the same path. The expression on the right-hand side of a DEFINE statement in a Master File can contain qualified field names.

A DEFINE attribute in a Master File can refer to only fields in its own path. If you want to create a virtual field that derives its value from fields in several different paths, you have to create it with a DEFINE FILE command using an alternate view prior to a report request, as discussed in the Creating Reports With TIBCO WebFOCUS® Language manual. The DEFINE FILE command is also helpful when you wish to create a virtual field that is only used once, and you do not want to add a declaration for it to the Master File.

Virtual fields defined in the Master File are available whenever the data source is used, and are treated like other stored fields. Thus, a field defined in the Master File cannot be cleared in your report request.

A virtual field cannot be used for cross-referencing in a join. It can, however, be used as a host field in a join.

Note: Maintain Data does not support DEFINE attributes that have a constant value. Using such a field in a Maintain Data procedure generates the following message:
(FOC03605) name is not recognized.