Creating Temporary Fields Independent of a Master File

How to:

Reference:

The temporary fields you create with the DEFINE and COMPUTE commands are tied to a specific Master File, and in the case of values calculated with the COMPUTE command, to a specific request. However, you can create temporary fields that are independent of either a Master File or a request using the DEFINE FUNCTION command.

A DEFINE function is a named group of calculations that use any number of input values and produce a return value. When calling a DEFINE function, you must first define the function.

A DEFINE function can be called in most of the same situations that are valid for Information Builders-supplied functions. Data types are defined with each argument. When substituting values for these arguments, the format must match the defined format. Alphanumeric arguments shorter than the specified format are padded with blanks, while longer alphanumeric arguments are truncated.

All calculations within the function are done in double precision. Format conversions occur only across equal signs in the assignments that define temporary fields.


Top of page

x
Syntax: How to Define a Function
DEFINE FUNCTION name (argument1/format1,..., argumentn/formatn) 
[tempvariablea/formata [TITLE 'line1[,line2 ...']
 [DESCRiption 'description'] = expressiona;] 
   .
   .
   .
[tempvariablex/formatx = expressionx;]  
name/format = [result_expression];
END

where:

name
Is the name of the function. This must be the last field calculated in the function, and is used to return the value of the function to the calling procedure.
argument1...argumentn
Are the argument names.
format1...formatn
Are the formats of the function arguments.

If the format of an argument is alphanumeric, the argument value must also be alphanumeric. Shorter arguments are padded on the right with blanks, and longer arguments are truncated.

If the format of an argument is numeric, the argument value must also be numeric. To prevent unexpected results, you must be consistent in your use of data types.

tempvariablea...tempvariablex
Are temporary fields. Temporary fields hold intermediate values used in the function. You can define as many temporary fields as you need.
tempformata...tempformatx
Are the formats of the temporary 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. The description displays in the tools that browse Master Files.
expressiona...expressionx
Are the expressions that calculate the temporary field values. The expressions can use parameters, constants, and other temporary fields defined in the same function.
format
Is the format of the value the function returns.
result_expression
Is the expression that calculates the value returned by the function. The expression can use parameters, constants, and temporary fields defined in the same function.

All names defined in the body of the function are local to the function. The last field defined before the END command in the function definition must have the same name as the function, and represents the return value for the function.


Top of page

x
Reference: DEFINE Function Limits and Restrictions


Example: Defining a Function

The following example creates and calls the SUBTRACT function. SUBTRACT performs a calculation with the arguments VAL1 and VAL2.

DEFINE FUNCTION SUBTRACT (VAL1/D8, VAL2/D8)
 SUBTRACT/D8.2 = VAL1 - VAL2;
END
TABLE FILE MOVIES
 PRINT TITLE LISTPR IN 35 WHOLESALEPR AND
 COMPUTE PROFIT/D8.2 = SUBTRACT(LISTPR,WHOLESALEPR);
 BY CATEGORY
   WHERE CATEGORY EQ 'MYSTERY' OR 'ACTION'
END

The output is:

CATEGORY
TITLE
LISTPR
WHOLESALEPR
PROFIT
ACTION
JAWS
 19.95
      10.99
  8.96
ROBOCOP
 19.98
      11.50
  8.48
TOTAL RECALL
 19.99
      11.99
  8.00
TOP GUN
 14.95
       9.99
  4.96
RAMBO III
 19.95
      10.99
  8.96
MYSTERY
REAR WINDOW
 19.98
       9.00
 10.98
VERTIGO
 19.98
       9.00
 10.98
FATAL ATTRACTION
 29.98
      15.99
 13.99
NORTH BY NORTHWEST
 19.98
       9.00
 10.98
DEAD RINGERS
 25.99
      15.99
 10.00
MORNING AFTER, THE
 19.95
       9.99
  9.96
PSYCHO
 19.98
       9.00
 10.98
BIRDS, THE
 19.98
       9.00
 10.98
SEA OF LOVE
 59.99
      30.00
 29.99


x
Procedure: How to Display DEFINE Functions

Issue the following command from the Command Console:

? FUNCTION


Example: Displaying DEFINE Functions

Issuing the command

? FUNCTION

displays information similar to the following:

FUNCTIONS
CURRENTLY 
ACTIVE
NAME
FORMAT
PARAMETER
FORMAT
----------
---------
---------
-------
SUBTRACT
D8.2
VAL1
D8
VAL2
D8

If you issue the ? FUNCTION command when no functions are defined, the following appears:

NO FUNCTIONS CURRENTLY IN EFFECT


x
Syntax: How to Clear DEFINE Functions
DEFINE FUNCTION {name|*} CLEAR

where:

name
Is the name of the function name to clear.
*
Clears all active DEFINE functions.

WebFOCUS