Creating a Numeric Expression

In this section:

Order of Evaluation
Evaluating Numeric Expressions With Native-Mode Arithmetic
Using Identical Operand Formats With Native-Mode Arithmetic
Using Different Operand Formats With Native-Mode Arithmetic

How to:

Express a Number in Scientific Notation

Reference:

Arithmetic Operators

A numeric expression performs a calculation that uses numeric constants, fields, operators, and functions to return a numeric value. When you use a numeric expression to assign a value to a field, that field must have a numeric format. The default format is D12.2.

A numeric expression can consist of the following components, shown below in bold:

A numeric constant. For example:
```
COMPUTE COUNT/I2 = 1 ;
```
A numeric constant in scientific notation. For example:
```
COMPUTE COST/D12.2 = EXPN(8E+3);
```
For syntax usage, see Express a Number in Scientific Notation.
A numeric field. For example:
```
COMPUTE RECOUNT/I2 = COUNT ;
```
Two numeric constants or fields joined by an arithmetic operator. For example:
```
COMPUTE BONUS/D12.2 = CURR_SAL * 0.05 ;
```
For a list of arithmetic operators, see Arithmetic Operators.

A numeric function. For example:

COMPUTE LONGEST_SIDE/D12.2 = MAX (WIDTH, HEIGHT) ;

Two or more numeric expressions joined by an arithmetic operator. For example:
```
COMPUTE PROFIT/D12.2 = (RETAIL_PRICE - UNIT_COST) * UNIT_SOLD ;
```
Note the use of parentheses to change the order of evaluation of the expression. For information on the order in which numeric operations are performed, see Order of Evaluation.

Before they are used in calculations, numeric values are generally converted to double-precision floating-point format. The result is then converted to the specified field format. In some cases the conversion may result in a difference in rounding. Note that environments that support native-mode arithmetic handle rounding differently. For details, see Evaluating Numeric Expressions With Native-Mode Arithmetic.

If a number is too large (greater than 10⁷⁵) or too small (less than 10^-75), you receive an Overflow or Underflow warning, and asterisks display for the field value.

Note: You can change the overflow character by issuing the SET OVERFLOWCHAR command.

For detailed information on rounding behavior for numeric data formats, see the Describing Data With TIBCO WebFOCUS® Language manual.

IF-THEN-ELSE logic is supported in numeric expressions.

Syntax: How to Express a Number in Scientific Notation

In an IF clause, use the following:

IF field op n[.nn]{E|D|e|d}[+|-]p

In a WHERE clause, use the following:

WHERE field op EXPN(n[.nn{E|D|e|d}[+|-]p);

In a COMPUTE command, use the following:

COMPUTE field[/format] = EXPN(n[.nn]{{E|D|e|d}[+|-]p);

In a DEFINE command, use the following:

DEFINE FILE filename 
field[/format] = EXPN(n[.nn]{E|D|e|d}[+|-]p);
END

In a DEFINE in the Master File, use the following:

DEFINE field[/format] = EXPN(n[.nn]{{E|D|e|d}[+|-]p);

where:

field: Is a field in a request.
/format: Is the optional format of the field. For information on formats, see the Describing Data With TIBCO WebFOCUS® Language manual.
op: Is a relational operator in a request.
n.nn: Is a numeric constant that consists of a whole number component, followed by a decimal point, followed by a fractional component.
E, D, e, d: Denotes scientific notation. E, e, d, and D are interchangeable.
+, -: Indicates if p is positive or negative. Positive is the default.
p: Is the power of 10 to which to raise the number. The range of values for p is between -78 and +78 on z/OS, -99 to +99 elsewhere.

Note: EXPN is useful for calculations on fields with F and D formats. It is generally not useful for calculations on fields with P or I formats.

Example: Evaluating a Number in Scientific Notation

You can use scientific notation in an IF or WHERE clause to express 8000 as 8E+03:

IF RCOST LT 8E+03

WHERE RCOST LT EXPN(8E+03)

Reference: Arithmetic Operators

The following list shows the arithmetic operators you can use in an expression:

Addition	+
Subtraction	-
Multiplication	*
Division	/
Exponentiation	**

Note: If you attempt to divide by 0, the value of the expression is 0. Multiplication and exponentiation are not supported for date expressions of any type. To isolate part of a date, use a simple assignment command.

Order of Evaluation

Numeric expressions are evaluated in the following order:

Exponentiation.
Division and multiplication.
Addition and subtraction.

When operators are at the same level, they are evaluated from left to right. Because expressions in parentheses are evaluated before any other expression, you can use parentheses to change this predefined order. For example, the following expressions yield different results because of parentheses:

COMPUTE PROFIT/D12.2  =  RETAIL_PRICE  -  UNIT_COST  *  UNIT_SOLD ;
COMPUTE PROFIT/D12.2  = (RETAIL_PRICE  -  UNIT_COST) *  UNIT_SOLD ;

In the first expression, UNIT_SOLD is first multiplied by UNIT_COST, and the result is subtracted from RETAIL_PRICE. In the second expression, UNIT_COST is first subtracted from RETAIL_PRICE, and that result is multiplied by UNIT_SOLD.

Note:Two operators cannot appear consecutively. The following expression is invalid:

a * -1

To make it valid, you must add parentheses:

a* (-1)

Example: Controlling the Order of Evaluation

The order of evaluation can affect the result of an expression. Suppose you want to determine the dollar loss in retail sales attributed to the return of damaged items. You could issue the following request:

TABLE FILE SALES
PRINT RETAIL_PRICE RETURNS DAMAGED
COMPUTE RETAIL_LOSS/D12.2 = RETAIL_PRICE * RETURNS + DAMAGED;
BY PROD_CODE
WHERE PROD_CODE IS 'E1';
END

The calculation

COMPUTE RETAIL_LOSS/D12.2 = RETAIL_PRICE * RETURNS + DAMAGED;

gives an incorrect result because RETAIL_PRICE is first multiplied by RETURNS, and then the result is added to DAMAGED. The correct result is achieved by adding RETURNS to DAMAGED, then multiplying the result by RETAIL_PRICE.

You can change the order of evaluation by enclosing expressions in parentheses. An expression in parentheses is evaluated before any other expression. You may also use parentheses to improve readability.

Using parentheses, the correct syntax for the preceding calculation is:

COMPUTE RETAIL_LOSS/D12.2 = RETAIL_PRICE * (RETURNS + DAMAGED);

The output is:

PROD_CODE  RETAIL_PRICE  RETURNS  DAMAGED     RETAIL_LOSS
---------  ------------  -------  -------     -----------
E1                 $.89        4        7            9.79

Example: Using IF-THEN-ELSE Logic in an Arithmetic Expression

The following request uses IF-THEN-ELSE logic in an arithmetic expression to determine how much to add to LISTPR to calculate NEWPRICE.

TABLE FILE MOVIES
SUM COPIES
LISTPR
COMPUTE
NEWPRICE = LISTPR + (IF COPIES GT 10 THEN 0.00 ELSE 25.00);
BY CATEGORY
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image. Where there are more than 10 copies, the NEWPRICE equals LISTPR, otherwise NEWPRICE is $25.00 greater than LISTPR.

Evaluating Numeric Expressions With Native-Mode Arithmetic

When native-mode arithmetic is used, a specific evaluation path is followed for each numeric expression based on the format of the operands and the operators. If the operands all have the same format, most operations are carried out in that format. If the operands have different formats, the operands are converted to a common format in a specific order of format precedence. Regardless of operand formats, some operators require conversion to specific formats so that all operands are in the appropriate format.

Using Identical Operand Formats With Native-Mode Arithmetic

If all operands of a numeric operator are of the same format, you can use the following table to determine whether or not the operations are performed in that native format or if the operands are converted before and after executing the operation. In each case requiring conversion, operands are converted to the operational format and the intermediate result is returned in the operational format. If the format of the result differs from the format of the target variable, the result is converted to the format of the target variable.

Operation		Operational Format
Addition	+	Native
Subtraction	-	Native
Multiplication	*	Native
Full Division	/	Accepts single or double-precision floating point, converts all others to double-precision floating point
Exponentiation	**	Double-precision floating point

Example: Using Identical Operand Formats (Native-mode Arithmetic)

The following variables are defined as integers in Maintain Data:

COMPUTE OPERANDONE/I4  ;
        OPERANDTWO/I4  ;
        RESULT/I4  ;

The required multiplication is done in native-mode arithmetic (integer arithmetic):

COMPUTE RESULT/I4 = OPERANDONE * OPERANDTWO  ;

Using Different Operand Formats With Native-Mode Arithmetic

If operands of a numeric operator have different formats, you can use the following table to determine what the common format is after they are converted. The lower operand is converted to the format of the higher operand before performing the operation.

Order	Format
1	16-byte packed decimal
2	Double-precision floating point
3	8-byte packed-decimal
4	Single-precision floating point
5	Integer
6	Character (alphanumeric and text)

For example, if a 16-byte packed-decimal operand is used in an expression, all other operands are converted to 16-byte packed-decimal format for evaluation. On the other hand, if an expression includes only integer and alphanumeric operands, all alphanumeric operands are converted to integer format.

A character (that is, alphanumeric or text) value can be used in a computation if it is a numeric string. An attempt is made to convert the character operand to the format of the other operand in the expression. If both operands are character, an attempt is made to convert them to double-precision. If the conversion is not possible, an error message is generated.

If you assign a decimal value to an integer, the fractional value is truncated.