Functions (Oracle HRMS Help)


	Previous	Next		Contents	Index	Navigation	Glossary	Library

Functions

Oracle FastFormula provides functions that manipulate data in different ways. Some functions work on only one type of data, some can work on two, others work on all three data types.

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.

Text Functions

GET_LOOKUP_ MEANING

GET_LOOKUP_MEANING(lookup_type , lookup_code)

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)

GET_TABLE_VALUE

GET_TABLE_VALUE(table_name, column_name, row_value [,effective date])

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

GREATEST(expr, expr [, expr] . . .)

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

LEAST(expr, expr [, expr] . . .)

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.

LENGTH

LENGTH(expr)

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

SUBSTR(expr, m [,n])

SUBSTRING(expr, m [,n])

The SUBSTRING function returns a substring of the text string operand expr of length n characters beginning at the mth 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 ...

UPPER

UPPER(expr)

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

Numeric Functions

ABS

ABS(n)

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:

ABS (-17)

FLOOR

FLOOR(n)

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:

FLOOR(35.455)

GREATEST

GREATEST(n, n [, n] . . .)

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

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

LEAST

LEAST(n, n [, n] . . .)

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

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

ROUND

ROUND(n [, m])

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(2.3401, 2)

ROUND (2.3461, 2)

ROUND (2.345,2)

ROUNDUP

ROUNDUP(n [, m])

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:

ROUND_UP(2.3401, 2)

ROUND_UP(2.3400, 2)

TRUNC

TRUNC(n [, m])

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:

TRUNC(2.3401, 2)

Date Functions

ADD_DAYS

ADD_DAYS(date, n)

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 number n.

Example:

ADD_DAYS ('30-DEC-1990', 6)

ADD_MONTHS

ADD_MONTHS(date, n)

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 number n.

ADD_YEARS

ADD_YEARS(date, n)

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 number n.

GREATEST

GREATEST(date1, date2[, date3] . . .)

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

LEAST

LEAST(date1, date2 [, date3] . . .)

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

DAYS_BETWEEN

DAYS_BETWEEN(date1, date2)

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:

DAYS_BETWEEN('27-JUN-1995', '03-JUL-1995')

MONTHS_BETWEEN

MONTHS_BETWEEN(date1, date2)

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.

Data Conversion Functions

Use data conversion functions to convert from one data type to another data type. For example, you could have an expression returning a number value for salary, which you want to include in a printed message (that is, a character value). To print the number as part of the message, you need to convert the value of salary from a number to a character value, using the TO_TEXT function.

NUM_TO_CHAR

NUM_TO_CHAR(n, format)

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.

TO_DATE

TO_DATE (expr [, format])

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

TO_NUM(expr)

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_TEXT

TO_TEXT(n) TO_TEXT (date1 [, format])

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.

For example:

	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' */

Contents

Index

Navigation

Glossary

Library