A function is similar to an operator in that it manipulates data items and returns a result. Functions differ from operators in the format in which they appear with their arguments. This format allows them to operate on any number of arguments, including none. Every time you associate a column with a function name, Query Builder performs the defined operation on the value(s) in that column.
Each function can take one or more arguments. An argument is an expression within the parentheses of a function supplying information for the function to use.
An example is the function ROUND, which rounds a number to the degree you specify. If you enter the expression ROUND (CREDIT_LIMIT, -2), Query Builder would round each value for CREDIT_LIMIT to the nearest hundred. If you enter ROUND(CREDIT_LIMIT, 1), Query Builder would round the values for CREDIT_LIMIT to the nearest 10th.
The values used within arguments are:
Argument Value |
Description |
char, set, search |
Character constant surrounded by single quotation |
replace |
marks or an expression returning character data. |
d, e |
Date constant surrounded by single quotation marks, or an expression returning date data. |
m, n |
Any number constant or expression returning numeric data. |
x |
Argument type depends on the context of the function. |
expr |
An expression of any type. |
fmt |
A format used to change the display of data or to enter data into a Query Builder field or condition in a format other than the default format. |
Functions that you can use with queries in Query Builder are:
Number functions accept numeric data and return numeric data. You can use number functions when defining columns and conditions.
Function (arguments) |
Description |
ABS(n) |
Absolute value of n. |
CEIL(n) |
Smallest integer greater than or equal to n. |
COS(n) |
Returns the cosine of n (an angle expressed in radians). |
COSH(n) |
Returns the hyperbolic cosine of n. |
EXP(n) |
Exponential notation. Returns e (2.71828183...) raised to the nth power. |
FLOOR(n) |
Largest integer equal to or less than n. |
LN(n) |
Returns the natural log of n, where n is greater than 0. |
LOG(m,n) |
Returns the logarithm, base m, of n. The base m can be any positive number other than 0 or 1, and n can be any positive number. |
MOD(m,n) |
Remainder of m divided by n. |
POWER(m,n) |
m raised to the nth power. If n is not an integer, it will be truncated |
ROUND(n[,m]) |
n rounded to m decimal places; if m is omitted, to 0 places. m may be negative to round digits left of the decimal point. |
SIGN(n) |
If n<0, -1; if n=0, 0; if n>0, 1. |
SIN(n) |
Returns the SINE of n (an angle expressed in radians). |
SINH(n) |
Returns the hyperbolic sine of n. |
SQRT(n) |
Square root of n; if n<0, NULL. |
TAN(n) |
Returns the tangent of n (an angle expressed in radians). |
TANH(n) |
Returns the hyperbolic tangent of n. |
TRUNC(n[,m]) |
n truncated to m decimal places; if m is omitted, to 0 places. m may be negative to truncate (make zero) digits left of the decimal point. |
You can use character functions that accept character data and return character data when defining columns and conditions.
Function (arguments) |
Description |
CHR(n) |
The character with having the binary equivalent to n in the database character set. |
CONCAT(char1,char2) |
Returns char1 concatenated with char2. This function is equivalent to the concatenation operator (||). |
INITCAP(char) |
char, with first letter of each word capitalized. Words are delimited by white space or characters that are not alphanumeric. |
LOWER(char) |
char, with all letters forced to lowercase. |
LPAD(char1,n[,char2]) |
char1, left-padded to length n with the sequence of characters in char2, replicated as many times as necessary; if char2 is omitted, with blanks. |
LTRIM(char[,set]) |
char, with initial characters removed up to the first character not in set. set defaults to ` ' (a single blank). |
NLS_INITCAP(char [,nlsparams]) |
Returns char, with the first letter of each word capitalized. Words are delimited by white space or characters that are not alphanumeric. The parameters specified in nlsparams can have the form NLS_SORT=sort where sort is a linguistic sort sequence for your session or BINARY. If you specify BINARY, this function returns char. For information on sort sequences, refer to the Oracle9i Server Concepts manual. |
NLS_LOWER(char[,nlsparams]) |
Returns char, with all letters forced to lowercase. See NLS_INITCAP for the nlspa-rams argument. |
NLS_UPPER(char[,nlsparams]) |
Returns char, with all letters forced to uppercase. See NLS_INITCAP for the nlspa-rams argument. |
REPLACE(char,search[,replace]) |
char, with every occurrence of search replaced with replace. If replace is not supplied, all occurrences of search are removed. |
RPAD(char1,n[,char2]) |
char1, right-padded to length n with the characters in char2, replicated as many times as necessary; if char2 is omitted, with blanks. |
RTRIM(char[,set]) |
char, with final characters removed after the last character not in set. set defaults to ` ' (a single blank). |
SOUNDEX(char) |
A char value representing the sound of the word(s) in char. |
SUBSTR(char,m[,n]) |
A substring of char, beginning at character m, n characters long if n is omitted. |
SUBSTRB(char,m[,n]) |
Same as SUBSTR, except that the arguments m and n are expressed in bytes, rather than characters. For a single byte database character set, SUBSTRB is the same as SUBSTR. |
UPPER(char) |
char, with all letters forced to uppercase. |
You can use character functions to return numeric data when defining columns and conditions.
Abbreviation |
Description |
ASCII (char) |
ASCII value of first character of char. |
INSTR(char1,char2[,n[,m]]) |
The position of the nth occurrence of char2 in char1, beginning search at position n. If m is omitted, 1 is assumed. If n is omitted, 1 is assumed. Position is given relative to the first character of char1, even when n>1. |
INSTRB(char1,char2[,n[,m]]) |
Same as INSTR, except that n and the returned value are expressed in bytes, rather than characters. For a single-byte database character set, INSTRB is the same as INSTR. |
LENGTH(char) |
The length, in characters, of char. |
LENGTHB(char) |
Same as LENGTH, except that char is expressed in bytes, rather than characters. If char is NULL, this function returns NULL. For a single-byte database character set, LENGTHB is the same as LENGTH. |
NLSSORT(char[,nlsparams]) |
Returns the string of bytes used to sort char. The parameters specified in nlsparams can have the form NLS_SORT=sort where sort is a linguistic sort sequence for your session or BINARY. If you specify BINARY, this function returns char. For information on sort sequences, refer to the Oracle9i Server Concepts Manual. |
You can use conversion functions to convert data from one datatype to another when defining columns and conditions.
Abbreviation |
Description |
TO_CHAR (d[,fmt]) |
Converts d to a char value in the format specified by the char value fmt. If fmt is omitted, d is converted to a char value in Query Builder's default date format, DD-MON-YY. |
TO_CHAR(n[,fmt]) |
Converts n to a char value in the format specified by the char value fmt. If fmt is omitted, n is converted to a char value exactly long enough to hold the significant digits. |
TO_DATE(char[,fmt]) |
Converts a date from a char value to a date value. fmt is a char value specifying the format of char. If fmt is omitted, char must have the Query Builder default date format, DD-MON-YY. |
TO_DATE(n,[fmt]) |
Converts a number into a date. fmt must be a format that, when used with TO_CHAR, produces a DATE (for example, J or MM). |
TO_LABEL(char[,fmt]) |
Converts char, a value of datatype CHAR or VARCHAR2 containing a label in the format fmt, to a value of MLSLABEL datatype. If you omit fmt, char must be in the default label format. |
TO_MULTI_BYTE (char) |
Returns char with all of its single-byte characters converted to their corresponding multibyte characters. Any single-byte characters that have no multibyte equivalents appear in the output string as single-byte. (This function is only useful if your database character set contains both types.) |
TO_NUMBER (char) |
Converts char, character data containing a number, to numeric data. |
TO_SINGLE_BYTE (char) |
Returns char with all of its multibyte characters converted to their corresponding single-byte characters. Any multibyte characters that have no single-byte equivalents appear in the output string as multibyte. (This function is only useful if your database character set contains both types.) |
All date functions return date data except MONTHS_BETWEEN, which returns numeric data. You can use these functions when defining columns and conditions.
Abbreviation |
Description |
ADD_MONTHS (d,n) |
Date d plus n months. |
LAST_DAY(d) |
Date of last day of month containing d. |
MONTHS_BETWEEN(d,e) |
Number of months between dates d and e. If d is later than e, result is positive; if earlier, negative. |
NEW_TIME(d,a,b) |
Date and time in time zone b when date and time in time zone a are d. a and b are char expressions with the following meaning: |
AST, ADT: |
Atlantic Standard or Daylight Time |
BST, BDT: |
Bering Standard or Daylight Time |
CST, CDT: |
Central Standard or Daylight Time |
EST, EDT: |
Eastern Standard or Daylight Time |
GMT: |
Greenwich Mean Time |
HST, HDT: |
Alaska-Hawaii Standard or Daylight Time |
MST, MDT: |
Mountain Standard or Daylight Time |
NST: |
Newfoundland Standard Time |
PST, PDT: |
Pacific Standard or Daylight Time |
YST, YDT: |
Yukon Standard or Daylight Time |
NEXT_DAY(d,char) |
Date of first day of week named by char that is equal to or later than d. |
ROUND(d[,fmt]) |
d rounded as specified by the rounding unit fmt; if fmt omitted, the nearest day. |
SYSDATE |
Current date and time. Requires no arguments. |
TRUNC(d[,fmt]) |
d with time of day truncated up to the specified truncation unit, fmt; if fmt omitted, the time of day is removed. |
Additional Information: Refer to the Oracle9i Server SQL Language Reference Manual for a table of format models to be used with the ROUND and TRUNC date functions.
Group functions retrieve data based on groups of rows rather than one result per row. Group functions perform operations such as average, count, sum, and standard deviation.
You can also use group functions to define a new column, and the entire result is treated as one group when you display no other columns. Displaying other columns will divide the result into smaller groups. You can use group functions in conditions, but only with a comparison operator, so the entire result is treated as one group. For example, the condition SALARY > AVG(SALARY) is true when the value of SALARY in the current row is greater than the average of SALARY in all of the rows.
Group functions can be used with the DISTINCT or ALL options. DISTINCT makes a group function consider only distinct functions of an expression. For example, the average of 1, 1, 1, and 3 is 1.5; the DISTINCT average is 2. ALL causes a group function to consider all values, including duplicates.
All of the following group functions skip null values except COUNT(*). The datatype of the arguments may be character, number, or date where expr is listed.
Function (argument) |
Description |
AVG([DISTINCT|ALL]n) |
Average value of n, ignoring null values. |
COUNT([DISTINCT|ALL] expr) |
Number of rows where expr is not null. |
COUNT(*) |
Number of rows in the table, including duplicates and those with nulls. |
GLB([DISTINCT|ALL] label) |
Returns the greatest lower bound of label. |
LUB([DISTINCT|ALL] label) |
Returns the least upper bound of label. |
Additional Information: GLB and LUB return values of the MLSLABEL datatype. For more information about the GLB and LUB functions, refer to the Trusted Oracle9i Server Administrator's Guide.
Function (argument) |
Description |
MAX([DISTINCT|ALL] expr) |
Maximum value of expr. |
MIN([DISTINCT|ALL] expr) |
Minimum value of expr. |
STDDEV([DISTINCT|ALL]n) |
Standard deviation of n, ignoring null values, based on an n-1 (sample) calculation. |
SUM([DISTINCT|ALL] n) |
Sum of values of n. |
VARIANCE([DISTINCT|ALL] n) |
Variance of n, ignoring null values. |
Query Builder also accepts these functions:
Function (argument) |
Description |
USER |
Returns the name of the current Oracle user. Requires no arguments. |
DECODE(expr,search1, replace1[,search2, replace2,] ... [default]) |
If expr equals any search, returns the following replace; if not, returns default. If default is omitted and there is no match, NULL is returned. expr may be any datatype; search must be the same type. The value returned is forced to the same datatype as the first return. |
GREATEST (expr1,expr2,...) |
Returns the greatest of a list of values. All expr after the expr1 are converted to the type of the expr1 before the comparison is done. |
GREATEST_LB(label [,label] ...) |
Returns the greatest lower bound of the list of labels. Each label must either have datatype MLSLABEL or RAW MLSLABEL or be a quoted literal in the default label format. The return value has the datatype RAW MLSLABEL. |
LEAST(expr1,expr2,...) |
Returns the least of a list of values. All expr after the expr1 are converted to the type of the expr1 before the comparison is done. |
LEAST_UB(label[,label] ...) |
Returns the least upper bound of the list of labels. Each label must have datatype MLSLABEL or be a quoted literal in the default label format. The return value has the datatype RAW MLSLABEL. |
NVL(x,expr) |
If x is null, returns expr; if x is not null, returns x. x and expr may be of any type. The type of the returned value is the same as the type of x. |
Additional Information: For information about any of the other functions, refer to the Oracle9i Server SQL Language Reference Manual.
Adding conditions to a query (Query Builder)
Pasting a function (Query Builder)
Copyright © 1984, 2005, Oracle. All rights reserved.