Previous | Next | Contents | Index | Navigation | Glossary | Library |

The functions are described below, separated into the three data types and functions that convert between data types. Where a function returns a different data type result than the data type of its operands, the description explains this.

The general form of a function is:

NAME OF FUNCTION(operand, operand, . .)

Notice that, as with the operators, the operands of a function can be variables, constants, or complete expressions. If the operand is a text string, you must enclose it in quote marks.

The GET_LOOKUP_MEANING function enables Oracle FastFormula to translate a lookup code into a meaning. This can be used for any descriptive flexfield items or developer flexfield items that are based on lookups.

Example

GET_LOOKUP_MEANING ('ETH_TYPE', PEOPLE_GB_ETHNIC_ORIGIN)

The GET_TABLE_VALUE function returns the value of a cell in a user-defined table. The three text operands, which identify the cell (table_name, column_name, and row_value), are mandatory. The date operand is optional. If it is not supplied, the function returns the cell value as of the effective date.

You cannot use this function in formulas for user table validation or QuickPaint reports.

Example:

GET_TABLE_VALUE('WAGE RATES', 'Wage Rate', Rate_Code)

GREATEST_OF(*expr*, *expr* [, *expr*] . . .)

The GREATEST function compares the values of all the text string operands. It returns the value of the operand that is alphabetically last. If there are two or more operands that meet the criteria, Oracle FastFormula returns the first.

LEAST_OF(*expr*, *expr* [, *expr*] . . .)

The LEAST function compares the values of all the text string operands. It returns the value of the operand that is alphabetically first. If there are two or more operands that meet the criteria, Oracle FastFormula returns the first.

The LENGTH function returns the number of characters in the text string operand *expr*.

Note:The data type of the result of this function is numeric.

SUBSTRING(*expr*, *m* [,*n*])

The SUBSTRING function returns a substring of the text string operand *expr* of length *n* characters beginning at the *m*th character. If you omit the third operand, the substring starts from *m* and finishes at the end of *expr*.

Note:The first operand is a text operand. The second and third operands are numeric operands. The resulting data type of this function is text.

Suggestion:Always check string length before you start to substring. For example:

/* Check that the tax code starts with GG */

IF length(Tax_code) <= 2

THEN

(message = 'Tax code is too short'

RETURN message

)

IF substr( Tax_code, 1, 2) = 'GG' THEN ...

Or, to check if Tax_code is a string of at least two characters starting with 'GG':

IF Tax_code LIKE 'GG%' THEN ...

The UPPER function convert a text string .to upper case.

The ABS function returns the magnitude of a numeric operand *n* as a positive numeric value.

If the value of the operand is positive, its value returns unchanged. If the operand is negative then the value's sign inverts, and the value returns as a positive number.

Example:

The FLOOR function returns the integer part of a numeric operand *n*.

If the value of the operand contains information after the decimal point, Oracle FastFormula discards that information and returns a whole number.

Example:

GREATEST_OF(*n*, *n* [, *n*] . . .)

The GREATEST function compares all the operands and returns the largest value.

LEAST_OF(*n*, *n* [, *n*] . . .)

The LEAST function compares all the operands and returns the smallest value.

The ROUND function rounds off a numeric value *n* to *m* decimal places. The first operand is the value Oracle FastFormula rounds off, the second the number of places Oracle FastFormula rounds off to.

Examples:

ROUND_UP(*n* [, *m*])

The ROUNDUP function rounds a numeric value *n* up to *m* decimal places. The first operand is the value to be rounded up, the second the number of places to round to. If the digits after the rounding point are zero, the value is unchanged. If the digits are not zero, the value is incremented at the rounding point.

Examples:

TRUNCATE(*n* [, *m*])

The TRUNC function rounds a numeric value *n* down to *m* decimal places. The first operand is the value to be rounded down, the second the number of places to round to.

Oracle FastFormula drops all digits (if any) after the specified truncation point.

Example:

The ADD_DAYS function adds a number of days to a date. The resulting date accords with the calendar.

Note:Oracle FastFormula ignores any fractional part of the numbern.

Example:

The ADD_MONTHS function adds a number of months to a date. The resulting date accords with the calendar.

Note:Oracle FastFormula ignores any fractional part of the numbern.

The ADD_YEARS function adds a number of years to a date. The resulting date accords with the calendar.

Note:Oracle FastFormula ignores any fractional part of the numbern.

The GREATEST function compares all the operands and returns the latest date.

The LEAST function compares all the operands and returns the earliest date.

The DAYS_BETWEEN function returns the number of days between two dates. If the later date is first, the result is a positive number. If the earlier date is first, the result is a negative number. The number returned is also based on the real calendar.

Note:The result is a numeric data type.

Example:

The MONTHS_BETWEEN function returns the number of months between two dates. If the later date is first, the result is a positive number. If the earlier date is first, the result is a negative number. The number returned is also based on the real calendar.

If the result is not a whole number of months (that is, there are some days as well), the days part is shown as a decimal.

Note:The result is a numeric data type.

Converts the number *n* from number data type to text data type using the specified format. This function is equivalent to the SQL TO_CHAR function. For example:

NUM_TO_CHAR(amount, '$9,990.99')

This returns the amount with a leading dollar sign, commas every three digits, and two decimal places. Refer to the *SQL Language Reference Manual* for a full list of the valid number formats you can specify.

Converts the expression *expr* of text data type to a date data type. The text expression must be of the form 'DD-MON-YYYY' if no format is provided. The day and year must be in numeric form. For example:

/* legal */

date_1 = TO_DATE ('12 January 89', 'DD Month YY')

/* illegal */

date_1 = TO_DATE ('12 January Nineteen-Eighty-Nine',

'DD Month Year')

Note:When assigning date variables from constants it is much more efficient to say:

date_1 = '12-JAN-1989'(date)

TO_NUMBER(*expr*)

Converts the expression *expr* of text data type to a number data type. The expression must represent a valid number. So for example, you cannot convert an expression such as `Type 24' but you can convert the text expression `1234'.

TO_CHAR(*n*) TO_CHAR(*date1 [, format]*)

The TO_TEXT function converts:

- the number
*n*from number data type to text data type

- the date
*date1*from date data type to text data type. The optional*format*should be a text string like 'DD-MON-YYYY'. This is the default.

birthdate = '21-JAN-1960' (date)

mesg = 'Birthdate is: ' + TO_CHAR (birthdate)

/* sets mesg to 'Birthdate is: 21-JAN-1960' */

mesg = 'Birthdate is: ' + TO_CHAR (birthdate,

'DD-MON-YY')

/* sets mesg to 'Birthdate is: 21-JAN-60' */

mesg = 'Birthdate is: ' + TO_CHAR (birthdate,

'DD Month Year')

/* sets mesg to 'Birthdate is: 21 January Nineteen-Sixty' */

Previous | Next | Contents | Index | Navigation | Glossary | Library |