Calculates the average of the values from the selected column.

Display the average hours worked for a department.

Takes a database column and creates a nested table from the selected rows;
associates a list of data with a specific value, enabling aggregation of data into a collection.

Create a list of employees within a department.

Lists the number of rows returned from the database for the selected column.

Determine the number of employee records returned from a database query.

Returns the maximum value of the selected column’s values.

Display the maximum hours worked.

Returns the minimum value of the selected column’s values.

Display the minimum hours worked.

Returns the standard deviation of the selected column’s values.

Apply standard deviation to values, for example, for forecasting.

Calculates the sum of the values from the selected column.

Display the total hours worked for a department.

From a list of expressions, Oracle determines which has the highest numeric precedence.

For Oracle’s data type comparison rules, see http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements002.htm#i55214

The following query results in BELIZE:

SELECT GREATEST('BELIZE','BELGIUM','BELARUS') "GREATEST"

FROM DUAL

From a list of expressions, Oracle determines which has the lowest numeric precedence.

For Oracle’s data type comparison rules, see http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements002.htm#i55214

The following query results in BELARUS:

SELECT LEAST('BELIZE','BELGIUM','BELARUS') "LEAST"

FROM DUAL

Returns the smallest integer value that is greater than or equal to the value (n).

13551.8 becomes 13552

Returns the largest integer value that is less than or equal to the value (n).

13551.8 becomes 13551

Rounds a number to the specified number of decimal places:

Negative arguments indicate rounding to the left of the decimal point.

Positive arguments round to the right of the decimal point.

25.193 with an argument of 1 will round to 25.2

Returns a number that represents the sign of a value (n).

-1 if n < 0; 0 if n = 0; 1 if n >0

Truncates a value to the specified number of decimal places.

25.193 with an argument of 1 will truncate to 25.1

Creates a comma-separated list of strings.

The Oracle function, aptStringConcat with DISTINCT or UNIQUE, cannot be used to concatenate values in a method, even though the method will validate and save. When that method is used in a report template, it will fail. Use collectString in a method to get this functionality.

aptStringConcat (array_name) can be used to create a comma-separated list of arrays within an array family.

Use this function in the Method Designer to concatenate distinct values. See the APTARE Database Programmer’s Reference Guide for additional examples.

Concatenates multiple character strings.

'Storage' + 'HQ' = 'StorageHQ'

Displays a string with the first letter of each word in uppercase.

'daily backup schedule' becomes 'Daily Backup Schedule'

Displays a string with all letters of each word in lowercase.

'DAILY BACKUP SCHEDULE' becomes 'daily backup schedule'

Pads the left of a string with the specified characters, up to the total string length.

Preface an alert message with a string of asterisks; for example, ****Warning

Trims the specified character set from the left of a string. This is useful for removing redundant words, characters, or labels. When no string is supplied, it defaults to a single blank.

'RAID5' and 'RAID6' would be trimmed to simply '5' and '6'

Substitutes one character string for another character string; in addition, you can remove character strings.

Substituting 'HDS' for 'Hitachi' changes 'Hitachi array' to 'HDS array'

Pads the right of a string with the specified characters, up to the total string length.

Postfix text with a string of asterisks; for example, Error***

Trims the specified character set from the right of a string. This is useful for removing redundant words, characters, or labels. When no string is supplied, it defaults to a single blank.

'RAID 5' and 'RAID 6' could be trimmed to simply 'RAID'

Extracts a portion of a character string.

Use this function to list the first three characters of policy names.

Converts a character string to a date.

to_date('10/09/13', 'DD/mm/YY')

to_date('10-sep-13', 'DD-MON-YYYY')

Makes several single-character substitutions; one-to-one substitution in one operation.

Use this function to replace all spaces with an underscore character.

'System Reference Guide' would become 'System_Reference_Guide'

Removes leading or trailing characters (or both) from a character string

Use this function to remove leading zeroes from object identifiers (00049 --> 49)

Changes all characters in a string to uppercase

'hds' becomes 'HDS'; 'Aptare' becomes 'APTARE'

Takes the date and adds n months. The result has the same day as input date. If the input date is the last day of the month or has fewer days than the resulting month, then the returning date will be the last day of the month.

June 22nd + 3 = September 22nd

Rounds the date to the unit format that you specify: Minute, Hour, Day, Month, Quarter, Year.

If you specify Month, the function rounds up on the 16th day of the month

Returns the current date and time for the system on which the database resides.

06-13-2012 09:34:41 (displayed in the format: MM-DD-YYYY HH:MM:SS)

Converts a date or interval value to a character data type in the specified format: Date, Timestamp.

finish_Date, 'DD-MON-YYYY HH24:'

startDate, 'YYYY-MM-DD HH24:MI:SS'

Truncates the time portion of a day to the specified format unit: Minute, Hour, Day, Month, Quarter, Year.
In Oracle, date values contain a year, month, and day and also the hour, minute, and second.

10-July-2012 09:34:41 becomes 10-July-2012

Searches a string for a sub-string and returns an integer that is the position in the string of the first character of the sub-string. The INTSR and LENGTH functions are mutually exclusive.

Search for 'error' in the string: 'A big system error caused the problem.' This results in 14.

Returns the number of characters of a character string. The INTSR and LENGTH functions are mutually exclusive.

Length of 'array type' would be 10

Checks for a null and substitutes another value.

Use this function to display N/A instead of a blank value

Checks for a value and if there is a match, replaces it with another constant or database field value.

Use this function to display Success if the status is 0, Warning if the status is 1, and Failed if the status is 0.

Enables aggregation on a unique field. This function can be applied to only one field in a report template.