Return Value

The data type of the variable argument or NA when you try to lag before the first period of a time dimension.

Syntax

LAG(variable n, dimension, [STATUS|NOSTATUS|limit-clause])

Parameters

variable

A variable or expression that is dimensioned by dimension.

n

The offset (that is, the number of dimension values) to lag. LAG uses this value to determine the number of values that LAG should go back in dimension to retrieve the value of variable.

Typically, n is a positive INTEGER that indicates the number of time periods (or dimension values) before the current one. When you specify a negative value for n, it indicates the number of time periods after the current o ne. In other words, using a negative value for n turns LAG into a LEAD function.

Note:

When using LAG in a model, see "Ensuring One-Way Dimensional Dependence" for information on how to code a value for n so that Oracle OLAP does not use simultaneous blocks when solving the model.

dimension

The dimension along which the lag occurs. While this can be any dimension, it is typically a hierarchical time dimension of type TEXT that is limited to a single level (for example, the month or year level) or a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR.

When variable has a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR and you want LAG to use that dimension, you can omit the dimension argument.

STATUS

Specifies that LAG should use the current status list (that is, only the dimension values currently in status in their current status order) when computing the lag.

NOSTATUS

(Default) Specifies that LAG should use the default status (that is, a list all the dimension values in their original order) when computing the lag.

limit-clause

Specifies that LAG should use the default status limited by limit-clause when computing the lag.

The syntax of limit-clause is the same syntax as any of the limit-clause arguments in the various forms of the LIMIT command (that is, the syntax of the LIMIT command after the limit-type argument such as "TO"). For the syntax of these arguments, see LIMIT (using values) command, LIMIT using LEVELREL command, LIMIT (using parent relation), LIMIT (using related dimension) command, LIMIT NOCONVERT command, and LIMIT command (using POSLIST).

To specify that LAG should use the current status limited by limit-clause when computing the lag, specify a LIMIT function for limit-clause.

Usage Notes

Assigning Results to a Time-Series Variable

Use care when assigning the results of LAG back into the time-series variable. Results are assigned one cell at a time, so you can overwrite the whole array with the first value returned, instead of moving all the values over n positions. You can, however, use LAG to calculate a series of values based on the initial value.

Examples

Return Value

DECIMAL value that corresponds to a percent difference or NA when you try to lag before the first period of a time dimension.

Syntax

LAGABSPCT(variable, n, dimension, [STATUS|NOSTATUS|limit-clause] )

Parameters

time-series

A variable or expression that is dimensioned by dimension.

n

The offset (that is, the number of dimension values) to lag. LAGABSPCT uses this value to determine the number of values that LAGABSPCT should go back in dimension to retrieve the value of variable.

Typically, n is a positive INTEGER that indicates the number of time periods (or dimension values) before the current one. When you specify a negative value for n, it indicates the number of time periods after the current one. In this case, LAGABSPCT compares the current value of the time series with a subsequent value.

dimension

The dimension along which the lag occurs. While this can be any dimension, it is typically a hierarchical time dimension of type TEXT that is limited to a single level (for example, the month or year level) or a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR.

When variable has a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR and you want LAGABSPCT to use that dimension, you can omit the dimension argument.

STATUS

Specifies that LAGABSPCT should use the current status list (that is, only the dimension values currently in status in their current status order) when computing the lag.

NOSTATUS

(Default) Specifies that LAGABSPCT should use the default status (that is, a list all the dimension values in their original order) when computing the lag.

limit-clause

Specifies that LAGABSPCT should use the default status limited by limit-clause when computing the lag.

The syntax of limit-clause is the same syntax as any of the limit-clause arguments in the various forms of the LIMIT command (that is, the syntax of the LIMIT command after the limit-type argument such as "TO"). For the syntax of these arguments, see LIMIT (using values) command, LIMIT using LEVELREL command, LIMIT (using parent relation), LIMIT (using related dimension) command, LIMIT NOCONVERT command, and LIMIT command (using POSLIST).

To specify that LAG should use the current status limited by limit-clause when computing the lag, specify a LIMIT function for limit-clause.

Usage Notes

Formula Used by LAGABSPCT

To obtain its results, LAGABSPCT uses the following formula.

(currentvalue - previousvalue) / ABS(previousvalue)

When the Previous Value of the Time Series Used by LAGABSPCT is Zero

When the previous value of the time series used by LAGABSPCT is zero, the result LAGABSPCT returns is determined by the DIVIDEBYZERO option. When DIVIDEBYZERO is set to NO, an error occurs. When DIVIDEBYZERO is set to YES, LAGABSPCT returns NA.

Examples

Return Value

DECIMAL or NA when you try to lag before the first period of a time dimension.

Syntax

LAGDIF(variable, n, dimension, [STATUS|NOSTATUS|limit-clause] )

Parameters

variable

A variable or expression that is dimensioned by dimension.

n

The offset (that is, the number of dimension values) to lag. LAGDIF uses this value to determine the number of values that LAGDIF should go back in dimension to retrieve the value of variable. Typically, n is a positive INTEGER that indicates the number of time periods (or dimension values) before the current one. When you specify a negative value for n, it indicates the number of time periods after the current one. In this case, LAGDIF compares the current value of the time series with a subsequent value.

dimension

The dimension along which the lag occurs. While this can be any dimension, it is typically a hierarchical time dimension of type TEXT that is limited to a single level (for example, the month or year level) or a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR.

When variable has a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR and you want LAGDIF to use that dimension, you can omit the dimension argument.

STATUS

Specifies that LAGDIF should use the current status list (that is, only the dimension values currently in status in their current status order) when computing the lag.

NOSTATUS

(Default) Specifies that LAGDIF should use the default status (that is, a list all the dimension values in their original order) when computing the lag.

limit-clause

Specifies that LAGDIF should use the default status limited by limit-clause when computing the lag.

The syntax of limit-clause is the same syntax as any of the limit-clause arguments in the various forms of the LIMIT command (that is, the syntax of the LIMIT command after the limit-type argument such as "TO"). For the syntax of these arguments, see LIMIT (using values) command, LIMIT using LEVELREL command, LIMIT (using parent relation), LIMIT (using related dimension) command, LIMIT NOCONVERT command, and LIMIT command (using POSLIST).

To specify that LAG should use the current status limited by limit-clause when computing the lag, specify a LIMIT function for limit-clause.

Examples

For an example of using LAGDIF, see Example 8-2.

Return Value

DECIMAL or NA when you try to lag before the first period of a dimension of a time dimension.

Syntax

LAGPCT(variable, n, [dimension], [STATUS|NOSTATUS|limit-clause] )

Parameters

variable

A variable or expression that is dimensioned by dimension.

n

The offset (that is, the number of dimension values) to lag. LAGPCT uses this value to determine the number of values that LAGPCT should go back in dimension to retrieve the value of variable. Typically, n is a positive INTEGER that indicates the number of time periods (or dimension values) before the current one. When you specify a negative value for n, it indicates the number of time periods after the current one. In this case, LAGPCT compares the current value of the time series with a subsequent value.

dimension

The dimension along which the lag occurs. While this can be any dimension, it is typically a hierarchical time dimension of type TEXT that is limited to a single level (for example, the month or year level) or a dimension with a type of DAY, WEEK, MONTH, QUARTER or YEAR.

When variable has a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR and you want LAGPCT to use that dimension, you can omit the dimension argument.

STATUS

Specifies that LAGPCT should use the current status list (that is, only the dimension values currently in status in their current status order) when computing the lag.

NOSTATUS

(Default) Specifies that LAGPCT should use the default status (that is, a list all the dimension values in their original order) when computing the lag.

limit-clause

Specifies that LAGPCT should use the default status limited by limit-clause when computing the lag.

The syntax of limit-clause is the same syntax as any of the limit-clause arguments in the various forms of the LIMIT command (that is, the syntax of the LIMIT command after the limit-type argument such as "TO"). For the syntax of these arguments, see LIMIT (using values) command, LIMIT using LEVELREL command, LIMIT (using parent relation), LIMIT (using related dimension) command, LIMIT NOCONVERT command, and LIMIT command (using POSLIST).

To specify that LAG should use the current status limited by limit-clause when computing the lag, specify a LIMIT function for limit-clause.

Usage Notes

Formula Used by LAGPCT

To obtain its results, LAGPCT uses the following formula.

(currentvalue - previousvalue) / previousvalue

When the Previous Value of the Time Series Used by LAGPCT is Zero

When the previous value of the time series used by LAGPCT is zero, the result LAGPCT returns is determined by the DIVIDEBYZERO option. When DIVIDEBYZERO is set to NO, an error occurs. When DIVIDEBYZERO is set to YES, LAGPCT returns NA.

Examples

Return Value

The data type of the expression. It can be INTEGER, LONGINT, DECIMAL, or DATE.

Syntax

LARGEST(expression [CACHE] [dimension...])

Parameters

expression

The expression whose largest value is to be returned.

CACHE

Specifies slightly different internal behavior. Specify this keyword only when the original performance is extremely slow.

dimension

The name of a dimension of the result; or, the name of a relation between one dimension of expression and another dimension that you want as a dimension of the result.

By default, LARGEST returns a single value. When you indicate one or more dimensions for the result, LARGEST tests for values along the dimensions that are specified and returns an array of values. Each dimension must be either a dimension of expression or related to one of its dimensions.

Tip:

When you specify a dimension that is not an actual dimension of expression, but, instead, is dimension that is related to a dimension of expression and when there are multiple relations between the two dimensions, Oracle OLAP uses the default relation between the dimensions to perform the calculation. (See the RELATION command for more information on default relations.) When you do not want Oracle OLAP to use this default relation, specify the related dimension by specifying the name of a specify relation.

Usage Notes

NA Values and LARGEST

LARGEST is affected by the NASKIP option in the same manner as other aggregate functions. When NASKIP is set to YES (the default), LARGEST ignores NA values and returns the largest value or values that are not NA. When NASKIP is set to NO, LARGEST returns NA when any value of the expression is NA. When all the values of the expression are NA, LARGEST returns NA for either setting of NASKIP.

Using LARGEST With an Expression Dimensioned by a DWMQY Dimension

When expression is dimensioned by a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, you can specify any other DAY, WEEK, MONTH, QUARTER, or YEAR dimension as a related dimension. Oracle OLAP uses the implicit relation between the dimensions. To control the mapping of one DAY, WEEK, MONTH, QUARTER, or YEAR dimension to another (for example, from weeks to months), you can define an explicit relation between the two dimensions and specify the name of the relation as the dimension argument to the LARGEST function.

For each time period in the related dimension, Oracle OLAP finds the largest data value in any source time period that ends in the target time period. This method is used regardless of which dimension has the more aggregate periods.

Examples

Return Value

DATETIME

Syntax

LAST_DAY(datetime-expression)

Parameters

datetime-expression

An expression that has the DATETIME data type.

Examples

Return Value

The data type of the variable argument or NA when you try to retrieve a value from beyond the last period defined for the time dimension.

Syntax

LEAD(variable, n, [time-dimension], [[STATUS|NOSTATUS|limit-clause] )

Parameters

variable

A variable or expression that is dimensioned by dimension.

n

The offset (that is, the number of dimension values) to lead. LEAD uses this value to determine the number of values that LEAD should go ahead in dimension to retrieve the value of variable. To count the values, LEAD uses the default status, unless you use the STATUS keyword or the limit-clause argument to specify a different dimension status.

Normally, n is a positive INTEGER that indicates the number of time periods (or dimension values) after the current one. When you specify a negative value for n, it indicates the number of time periods before the current one. In effect, using a negative value for n turns LEAD into a LAG function.

Note:

When using LEAD in a model, see "Ensuring One-Way Dimensional Dependence" for information on how to code a value for n so that Oracle OLAP does not use simultaneous blocks when solving the model.

dimension

The dimension along which the lead occurs. While this can be any dimension, it is typically a hierarchical time dimension of type TEXT that is limited to a single level (for example, the month or year level) or a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR.

When variable has a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR and you want LEAD to use that dimension, you can omit the dimension argument.

STATUS

Specifies that LEAD should use the current status list (that is, only the dimension values currently in status in their current status order) when computing the lead.

NOSTATUS

Specifies that LEAD should use the default status (that is, a list all the dimension values in their original order) when computing the lead.

limit-clause

Specifies that LEAD should use the default status limited by limit-clause when computing the lead.

The syntax of limit-clause is the same syntax as any of the limit-clause arguments in the various forms of the LIMIT command (that is, the syntax of the LIMIT command after the limit-type argument such as "TO"). For the syntax of these arguments, see LIMIT (using values) command, LIMIT using LEVELREL command, LIMIT (using parent relation), LIMIT (using related dimension) command, LIMIT NOCONVERT command, and LIMIT command (using POSLIST).

To specify that LAG should use the current status limited by limit-clause when computing the lag, specify a LIMIT function for limit-clause.

Examples

Return Value

The data type of the first expression.

Syntax

LEAST (expr [, expr]...)

Parameters

expr

An expression.

Examples

Return Value

NUMBER or NA if the expression is an empty string or NA

Syntax

{ LENGTH | LENGTHB | LENGTHC}(char)

Parameters

char

A text expression.

Return Value

The return value varies depending on the use of the function and whether or not you specify the INTEGER keyword:

• When the LIMIT function is an argument to an OLAP DML statement (including a user-defined command or function) that expects a valueset, it returns a valueset.

• When the LIMIT function returns an empty valueset, it returns it as a valueset with null status.

• In all other cases, the LIMIT function returns either a TEXT value or an INTEGER value depending on whether or not you include the INTEGER keyword. When it returns a TEXT value that represents empty status, it returns it as NA.

Syntax

The syntax of the LIMIT function varies depending on whether you want to retrieve the values of the dimension or dimension surrogate values that result from a specified LIMIT command or the values of a specified dimension status stack.

Syntax for Retrieving Values From a LIMIT Command

LIMIT([INTEGER] {dimension | valueset | LIMIT_function} [concat-component] limit-type -      [limit-clause] [IFNONE label])

Syntax for Retrieving Values From a Dimension Status Stack

LIMIT([INTEGER] dimension  STATDEPTH stack-position] [IFNONE label])

Parameters

dimension

See the LIMIT command for a complete description of this argument.

valueset

See the LIMIT command for a complete description of this argument.

LIMIT_function

Another LIMIT function.

Note:

When you nest LIMIT functions inside each other in this manner, the first argument of the innermost LIMIT function must be the a dimension or a valueset. See also "Nesting the LIMIT Function".

concat-component

See the LIMIT command for a complete description of this argument.

limit-type

See the LIMIT command for a complete description of this argument.

limit-clause

Specifies the values to use for the limit. There are several types of limit clauses— for example, a limit clause you can use to specifying the limit using values (including using a valueset) and a limit clause you can use to specify the limit using a related dimension. Each of these types of limit clauses has a very complex syntax. Because the syntax is complex, the syntax for the various types of limit clauses are documented separately as part of the following topics:

• LIMIT (using values) command
• LIMIT using LEVELREL command
• LIMIT (using related dimension) command
• LIMIT (using parent relation)
• LIMIT NOCONVERT command
• LIMIT command (using POSLIST)

In the syntax of each of these LIMIT command topics, the limit-clause is that portion of the syntax following the limit-type argument.

INTEGER

When you use the INTEGER keyword, the function returns the position numbers of the values in the default dimension status rather than the names. When you use INTEGER with a valueset, the function returns the position numbers of the values in the default dimension status, not in the valueset.

STATDEPTH

Specifies that Oracle OLAP retrieve the status list values for the status list stack for the specified dimension.

stack-position

An INTEGER value that specifies the position in the status list stack from which to retrieve the values. Keep the following in mind when specifying a value:

• Values from 0 to 1-STATDEPTH(dimension) retrieve stacked values from the top (current status) to the bottom (oldest status.)

• Values from 1 to STATDEPTH(dimension) retrieve stacked values from the bottom of the stack (that is, the oldest status) to the top of the stack (that is, the current status).

See the STATDEPTH function for more information about status list stacks.

Usage Notes

Nesting the LIMIT Function

Use the following syntax to return the result of several LIMIT commands for the same dimension by nesting the LIMIT function.

LIMIT (LIMIT (LIMIT (lim-exp1) lim-exp2) lim-exp3)

Use this nested construction to find the status of a series of LIMIT commands. For example, the following are some LIMIT commands.

LIMIT product TO division 'Camping'
LIMIT product KEEP -
   EVERY(sales GT 50000, product)
LIMIT product KEEP FIRST 1

To see the status of the preceding LIMIT commands, you execute the following statement.

REPORT LIMIT(LIMIT(LIMIT(product TO -
   division 'Camping') KEEP EVERY -
   (sales GT 50000, product))KEEP FIRST 1)

Limiting with a Component of a Concat Dimension

You can limit a concat dimension to the current status of one of its component dimensions as in the following statement.

LIMIT(reg.dist.ccdim TO district)

You can also limit a concat dimension to a set of the values of one of its component dimensions as in the following statement.

LIMIT(reg.dist.ccdim TO district 'Boston' 'Chicago' 'Seattle')

Returning Multidimensional Results

The LIMIT function returns multidimensional results when evaluating multidimensional expressions. In the following example, the sales variable has three dimensions: product, district, and month.

LIMIT product TO ALL
LIMIT district TO 'Boston'
LIMIT month TO 'Jan95' 'Feb95' 'Mar95'

A REPORT sales statement produces the following output.

DISTRICT: BOSTON
          -------------SALES--------------
          -------------MONTH--------------
PRODUCT     Jan95      Feb95      Mar95
--------- ---------- ---------- ----------
Tents      32,153.52  32,536.30  43,062.75
Canoes     66,013.92  76,083.84  91,748.16
Racquets   52,420.86  56,837.88  58,838.04
Sportswear 53,194.70  58,913.40  62,797.80
Footwear   91,406.82  86,827.32 100,199.46

Suppose you want a list of products whose sales exceed $90,000 for the status shown in the preceding report. The LIMIT function evaluates the product sales in each month and district combination and produces a list that is dimensioned by the months and districts in status.

A REPORT limit (product TO sales GT 90000) statement produces the following output.

          ---LIMIT (PRODUCT TO SALES GT---
          -------------90000)-------------
          -------------MONTH--------------
DISTRICT    Jan95      Feb95      Mar95
--------- ---------- ---------- ----------
Boston    Footwear   NA         Canoes
                                Footwear

Examples

Return Value

A TEXT expression.

Syntax

LIMITMAPINFO ([aw], limit-map, column-name)  

Parameters

aw

The name of the analytic workspace that contains the analytic workspace object.

limit-map

The limit map as a text expression.

column-name

The name of the column of a relational table as it appears in limit-map.

Examples

Return Values

TRUE if the condition is false or unknown and FALSE if the condition is true.

Syntax

LNNVL(condition)

Parameters

condition

An expression constructed using any scalar values. Note that you cannot specify an expression that contains AND, OR, or BETWEEN.

Examples

Return Values

TIMESTAMP

Syntax

LOCALTIMESTAMP [ (timestamp-precision) ]

ARGUMENTS

timestamp-precision

Specifies the fractional second precision of the time value returned

Examples

Return Value

DECIMAL

Syntax

LOG([base,] expression)

Parameters

base

The base by which to compute the logarithm. When you do not specify a value, the function computes the natural logarithm of the expression by using e for the base where e equals 2.718281828459.

expression

A numeric expression which is greater than zero. When the value is equal to or less than zero, LOG returns an NA value.

Examples

Return Value

DECIMAL

Syntax

LOG10(expression)

Parameters

expression

The value of expression must be greater than zero. When the value is equal to or less than 0 (zero), LOG10 returns an NA value.

Examples

Return Value

TEXT or NTEXT

When the expression is TEXT, the return value is TEXT. When the expression is NTEXT, the return value is NTEXT

Syntax

LOWCASE(text-expression)

Parameters

text-expression

The text expression whose characters are to be converted.

Examples

Return Value

The data type of text-expression.

Syntax

LOWER(text-expression)

Parameters

text-expression

The text expression whose characters are to be converted.

Return Value

TEXT or NTEXT based on the data type of the expression you want to pad (text-exp).

Syntax

LPAD (text-exp , length [, pad-exp])

Parameters

text-exp

A text expression to pad.

length

The total length of the return value as it is displayed on your screen. In most character sets, this is also the number of characters in the return value. However, in some multibyte character sets, the display length of a character string can differ from the number of characters in the string.

When you specify a value for length that is shorter than the length of text-exp, then this function returns only that portion of the expression that fits into the specified length.

pad-exp

A text expression that specifies the padding characters. The default value of pad-exp is a single blank.

Examples

Return Value

TEXT or NTEXT based on the data type of the first argument.

Syntax

LTRIM (text-exp [,  trim-exp])

Parameters

text-exp

A text expression that you want trimmed.

trim-exp

A text expression that is the characters to trim. The default value of trim-exp is a single blank.

Examples

Return Value

DATE or text

Syntax

MAKEDATE(year month day)

Parameters

year

An INTEGER expression that represents the year of the test date. For any year, you can specify the year as a four-digit number in the range 1000 to 9999. For years in the range 1950 to 2049 (the default) or some other range (as set through the YRABSTART option), you have the alternative of specifying a two-digit number that represents the last two digits of the year (96 represents 1996, for example).

month

Any INTEGER expression, normally in the range 1 to 12. When you specify an INTEGER less than 1 or greater than 12, MAKEDATE returns a date in a year before or after the year specified by the INTEGER expression for year.

For example, if the arguments to MAKEDATE are (97 14 21), MAKEDATE returns the date February 21, 1998 because, in effect, February 1998 is the fourteenth month of 1997.

day

An INTEGER expression in the range 1 to 31.

Usage Notes

Format of the Result Returned by MAKEDATE

When you display the result returned by MAKEDATE, the date is formatted according to the date template in the DATEFORMAT option. When the day of the week or the name of the month is used in the date template, the day names specified in the DAYNAMES option and the month names specified in the MONTHNAMES option are used. You can use the result returned by MAKEDATE anywhere that a DATE value is expected.

Invalid Dates

When the arguments to MAKEDATE do not represent a valid date between January 1, 1000, and December 31, 9999, MAKEDATE returns an NA value.

Examples

Return Value

DECIMAL. The results of MAX are dimensioned by the union of the dimensions of the two expressions

Syntax

MAX(expression1, expression2)

Parameters

expression1

One expression to be compared.

expression2

The other expression to be compared.

Examples

Return Value

INTEGER

Syntax

MAXBYTES(text-expression)

Parameters

text-expression

The TEXT expression whose bytes for each line are to be counted.

Examples

Return Value

INTEGER

Syntax

MAXCHARS(text-expression)

Parameters

text-expression

The text expression whose characters for each line are to be counted. MAXCHARS accepts either a TEXT or NTEXT argument. It does not perform an automatic conversion to either data type. It returns the information that is correct for the data type of the specified argument.

Examples

Return Value

DECIMAL

Syntax

MEDIAN(expression [CACHE] [dimension...])

Parameters

expression

The expression whose median value is to be calculated.

CACHE

Specifies slightly different internal behavior. Specify this keyword only when the original performance is extremely slow.

dimension

The name of a dimension of the result; or, the name of a relation between one dimension of expression and another dimension that you want as a dimension of the result.

By default, MEDIAN returns a single value. When you indicate one or more dimensions for the result, MEDIAN calculates values along the dimensions that are specified and returns an array of values. Each dimension must be either a dimension of expression or related to one of its dimensions.

Tip:

When you specify a dimension that is not an actual dimension of expression, but, instead, is dimension that is related to a dimension of expression and when there are multiple relations between the two dimensions, Oracle OLAP uses the default relation between the dimensions to perform the calculation. (See the RELATION command for more information on default relations.) When you do not want Oracle OLAP to use this default relation, specify the related dimension by specifying the name of a specify relation.

Usage Notes

NA Values and MEDIAN

MEDIAN is affected by the NASKIP option in the same manner as other aggregate functions. When NASKIP is set to YES (the default), MEDIAN ignores NA values and returns the median of the values that are not NA. When NASKIP is set to NO, MEDIAN returns NA when any value of the expression is NA. When all the values of the expression are NA, MEDIAN returns NA for either setting of NASKIP.

Examples

Return Value

DECIMAL. The results of MIN are dimensioned by the union of the dimensions of the two expressions.

Syntax

MIN(expression1, expression2)

Parameters

expression1

One expression to be compared.

expression2

The other expression to be compared.

Examples

Return Value

INTEGER

Syntax

MMOF(date-expression)

Parameters

date-expression

An expression that has the DATE data type, or a text expression that specifies a date. The values of the text expression are converted automatically to DATE values, using the current setting of the DATEORDER option to resolve any ambiguity.

Examples

Return Value

DECIMAL

Syntax

MODE(expression [CACHE} [dimensions])

Parameters

expression

The numeric expression whose mode is to be calculated.

CACHE

Specifies slightly different internal behavior. Specify this keyword only when the original performance is extremely slow.

dimensions

The dimensions of the result. When you do not specify any dimensions, MODE calculates the mode over all the dimensions of expression and it returns a single value. When you specify one or more dimensions (but fewer than all of the dimensions of expression) in the dimension argument, then MODE calculates the mode for each value of the dimensions that you specified and returns an array of values. Each dimension must be a dimension of expression.

Usage Notes

The Effect of NASKIP on MODE

MODE is not affected by the NASKIP option.

More Than One Set of Duplicate Values

When multiple values qualify as having the greatest number of occurrences in the expression, then MODE sorts the values and returns the lowest one. For example, for the data series {4,5,2,3,7,4,6,2,1}, the mode for the series is 2 even though 2 and 4 both occur twice.

Examples

Return Values

Numeric.

Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type

Syntax

MOD(dividend, divisor)

Parameters

dividend

A numeric expression (or an expression that Oracle OLAP can implicitly convert to a numeric expression) that is the number you want to divide.

divisor

A numeric expression (or an expression that Oracle OLAP can implicitly convert to a numeric expression) that is the divisor.

Examples

Return Value

NUMBER

Syntax

MONTHS_BETWEEN(datetime_expression1, datetime_expression2)

Parameters

datetime-expression1

One expression that has the DATETIME data type, or a text expression that specifies a date.

datetime-expression2

A second expression that has the DATETIME data type, or a text expression that specifies a date.

Examples

Return Value

DECIMAL when the data type of expression is DECIMAL or SHORT; otherwise, NUMBER.

Syntax

MOVINGAVERAGE(expression, start, stop, step, -

     [dimension [STATUS|limit-clause]])

Parameters

expression

A numeric variable or calculation whose values you want to average; for example, units or sales-expense.

start

stop

Integer values that specify the range of values over which you want to average. The value of start specifies the beginning of the range. The value of stop specifies the end of the range. Specify the values of start and stop relative to the current value of dimension. To specify a negative position for start or stop precede the value with a comma. Thus, you specify zero (0) for the current dimension value, and -1 for the value preceding the current value. (See also "How the Moving Functions Handle Out-of-Range and NA Values".)

Note:

By default this function uses the default status list when identifying the range of values to average. You can specify either the STATUS or limit-clause phrase to change this behavior.

Tip:

When you want to range to the end of status, for convenience and to document your intent, specify the value of stop as OBJ function with the DIMMAX keyword

step

A positive whole number that specifies whether to average over every value in the range, every other value, every third value, and so on. A value of 1 for step means average over every value. A value of 2 means average over the first value, the third value, the fifth value, and so on. For example, when the current month is Jun96 and the start and stop values are -3 and 3, a step value of 2 means average over Mar96, May96, Jul96, and Sep96.

dimension

The dimension over which the moving average is calculated. The data type of dimension can be of any type, but typically, is a time dimension.

When expression has a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR and you want the function to use that dimension, you can omit the dimension argument.

STATUS

Specifies that MOVINGAVERAGE should use the current status list (that is, only the dimension values currently in status in their current status order) when calculating the moving average.

limit-clause

Specifies that MOVINGAVERAGE uses the values specified by a LIMIT function or those specified with the same syntax as any of the limit-clause arguments in the various forms of the LIMIT command (that is, the syntax of the LIMIT command after the limit-type argument such as "TO"). For the syntax of these arguments, see LIMIT (using values) command, LIMIT using LEVELREL command, LIMIT (using parent relation), LIMIT (using related dimension) command, LIMIT NOCONVERT command, and LIMIT command (using POSLIST).

Usage Notes

How the Moving Functions Handle Out-of-Range and NA Values

As a moving function loops through the values, at each step in the loop, if the in-loop dimension position is NA or out of range, then the function considers expression as an NA value. The function treats these NA values in the manner specified by the setting of the NASKIP option (by default, as NA) so, typically, the function ignores out-of-range (NA) values and does not evaluate the expression for that step in the loop.

Examples

Return Value

DECIMAL when the data type of expression is DECIMAL or SHORT; otherwise, NUMBER.

Syntax

MOVINGMAX(expression, start, stop, step, [dimension [STATUS|limit-clause]])

Parameters

expression

A numeric variable or calculation from whose values you want to find the maximum values; for example, units or sales-expense.

start

stop

Integer values that specify the range of values over which you want to find the maximum values. The value of start specifies the beginning of the range. The value of stop specifies the end of the range. Specify the values of start and stop relative to the current value of dimension. To specify a negative position for start or stop precede the value with a comma. Thus, you specify zero (0) for the current dimension value, and -1 for the value preceding the current value. (See also "How the Moving Functions Handle Out-of-Range and NA Values".)

Note:

By default this function uses the default status list when identifying the range of values to average. You can specify either the STATUS or limit-clause phrase to change this behavior.

Tip:

When you want to range to the end of status, for convenience and to document your intent, specify the value of stop as OBJ function with the DIMMAX keyword

step

A positive whole number that specifies whether to search every value in the range, every other value, every third value, and so on. A value of 1 for step means search every value. A value of 2 means check the first value, the third value, the fifth value, and so on. For example, when the current month is Jun96 and the start and stop values are -3 and 3, a step value of 2 means search the months Mar96, May96, Jul96, and Sep96 and return the maximum value that occurs in one of those four months.

dimension

The dimension over which the moving maximum is calculated. While this can be any dimension, it is typically a hierarchical time dimension of type TEXT that is limited to a single level (for example, the month or year level) or a dimension with a type of DAY, WEEK, MONTH, Quarter, or YEAR.

When expression has a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR and you want MOVINGMAX to use that dimension, you can omit the dimension argument.

STATUS

Specifies that MOVINGMAX uses the values specified by a LIMIT function or those specified with the same syntax as any of the limit-clause arguments in the various forms of the LIMIT command (that is, the syntax of the LIMIT command after the limit-type argument such as "TO"). For the syntax of these arguments, see LIMIT (using values) command, LIMIT using LEVELREL command, LIMIT (using parent relation), LIMIT (using related dimension) command, LIMIT NOCONVERT command, and LIMIT command (using POSLIST).

Examples

For an example of calculating maximum sales, see Example 8-31.

Return Value

DECIMAL when the data type of expression is DECIMAL or SHORT; otherwise, NUMBER.

Syntax

MOVINGMIN(expression, start, stop, step, [dimension [STATUS|limit-clause]])

Parameters

expression

A numeric variable or calculation from whose values you want to find the minimum values; for example, UNITS or SALES-EXPENSE.

start

stop

Integer values that specify the range of values over which you want to find minimum values. The value of start specifies the beginning of the range. The value of stop specifies the end of the range. Specify the values of start and stop relative to the current value of dimension. To specify a negative position for start or stop precede the value with a comma. Thus, you specify zero (0) for the current dimension value, and -1 for the value preceding the current value. (See also "How the Moving Functions Handle Out-of-Range and NA Values".)

Note:

By default this function uses the default status list when identifying the range of values to average. You can specify either the STATUS or limit-clause phrase to change this behavior.

Tip:

When you want to range to the end of status, for convenience and to document your intent, specify the value of stop as OBJ function with the DIMMAX keyword

step

A positive whole number that specifies whether to search every value in the range, or every other value, or every third value, and so on. A value of 1 for step means search every value. A value of 2 means check the first value, the third value, the fifth value, and so on. For example, when the current month is Jun96 and the start and stop values are -3 and 3, a step value of 2 means search the months Mar96, May96, Jul96 and Sep96 and return the minimum value that occurs in one of those four months.

dimension

The dimension over which the moving minimum is calculated. While this can be any dimension, it is typically a hierarchical time dimension of type TEXT that is limited to a single level (for example, the month or year level) or a dimension with a type of DAY, WEEK, MONTH, Quarter, or YEAR.

When expression has a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR and you want MOVINGMIN to use that dimension, you can omit the dimension argument.

STATUS

Specifies that MOVINGMIN should use the current status list (that is, only the dimension values currently in status in their current status order) when calculating the moving minimum.

limit-clause

Specifies that MOVINGMIN uses the values specified by a LIMIT function or those specified with the same syntax as any of the limit-clause arguments in the various forms of the LIMIT command (that is, the syntax of the LIMIT command after the limit-type argument such as "TO"). For the syntax of these arguments, see LIMIT (using values) command, LIMIT using LEVELREL command, LIMIT (using parent relation), LIMIT (using related dimension) command, LIMIT NOCONVERT command, and LIMIT command (using POSLIST).

Examples

For an example of calculating minimum sales, see Example 8-31.

Return Value

DECIMAL when the data type of expression is DECIMAL or SHORT; otherwise, NUMBER.

Syntax

MOVINGTOTAL(expression, start, stop, step, [dimension [STATUS|limit-clause]])

Parameters

expression

A numeric variable or calculation whose values you want to total; for example, UNITS or SALES-EXPENSE.

start

stop

Integer values that specify the range of values over which you want to total. The value of start specifies the beginning of the range. The value of stop specifies the end of the range. Specify the values of start and stop relative to the current value of dimension. To specify a negative position for start or stop precede the value with a comma. Thus, you specify zero (0) for the current dimension value, and -1 for the value preceding the current value. (See also "How the Moving Functions Handle Out-of-Range and NA ValuesHow the Moving Functions Handle Out-of-Range and NA Values".)

Note:

By default this function uses the default status list when identifying the range of values to average. You can specify either the STATUS or limit-clause phrase to change this behavior.

Tip:

When you want to range to the end of status, for convenience and to document your intent, specify the value of stop as OBJ function with the DIMMAX keyword

step

A positive whole number that specifies whether to total over every value in the range, every other value, every third value, and so on. A value of 1 for step means total over every value. A value of 2 means total over the first value, the third value, the fifth value, and so on. When the current month is Jun96 and the start and stop values are -3 and 3, a step value of 2 means total over Mar96, May96, Jul96, and Sep96.

dimension

The dimension over which the moving total is calculated. While this can be any dimension, it is typically a time dimension.

When expression has a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR and you want MOVINGTOTAL to use that dimension, you can omit the dimension argument.

STATUS

Specifies that MOVINGTOTAL should use the current status list (that is, only the dimension values currently in status in their current status order) when calculating the moving total.

limit-clause

Specifies that MOVINGTOTAL uses the values specified by a LIMIT function or those specified with the same syntax as any of the limit-clause arguments in the various forms of the LIMIT command (that is, the syntax of the LIMIT command after the limit-type argument such as "TO"). For the syntax of these arguments, see LIMIT (using values) command, LIMIT using LEVELREL command, LIMIT (using parent relation), LIMIT (using related dimension) command, LIMIT NOCONVERT command, and LIMIT command (using POSLIST).

Examples

For an example of calculating a moving total sales, see Example 8-31.

Return Value

An NA value with the NA flag set to TRUE.

Syntax

NA2

Examples

See Example 9-104.

Return Value

The value returned is the same data type as source-expression. When the fill and source expressions do not have the same data type, Oracle OLAP converts the fill expression to the data type of the source expression when possible. Otherwise, an error is produced. When both the source and fill expressions equal NA, then NAFILL returns NA.

Syntax

NAFILL(source-expression fill-expression)

Parameters

source-expression

The expression being evaluated. For values of source-expression that do not equal NA, NAFILL returns the corresponding values of source-expression. Source-expression determines the dimensions and data type of the result.

fill-expression

The expression to be substituted in the return value. Fill-expression must have the same data type as source-expression. Fill-expression is only evaluated for values of source-expression that equal NA.

Usage Notes

Functions in the Fill Expression

You can use any functions in the fill expression if they return the same data type as the source expression.

NATRIGGER Takes Precedence Over NAFILL

Oracle OLAP evaluates an $NATRIGGER property expression before applying the NAFILL function. When the $NATRIGGER expression is NA, then the NAFILL function has an effect.

Examples

Return Values

INTEGER

0 for a non-NA value, 2 if the NA value is an NA2 value, or 1 for all other NA values

Syntax

NAFLAG (expression)

Parameters

expression

An OLAP DML expression.

Examples

See Example 9-104.

Return Value

DATETIME

Syntax

NEW_TIME(datetime-exp this_zone new_zone)

Parameters

this_zone

A text expression that indicates the time zone from which you want to convert datetime-exp. It must be a valid time zone, as listed in the following table.

new_zone

A text expression that indicates the time zone into which you want to convert datetime-exp. It is the time zone of the return value. It must be a valid time zone, as listed in the following table.

Examples

Return Value

DATETIME

Syntax

NEXT_DAY(datetime-expression, weekday)

Parameters

datetime-expression

An expression that has the DATETIME data type.

weekday

A text expression that identifies a day of the week (for example, Monday). Valid names are controlled by the NLS_DATE_LANGUAGE option.

Examples

Return Value

INTEGER when you specify a valid value for the name of the character set, a number; otherwise NA.

Syntax

NLS_CHARSET_ID (charset_name)

Parameters

charset_name

A VARCHAR2 text expression that is a valid character set name or one of the following values:

• CHAR_CS which specifies that the function return the database character set identification number of the server.

• NCHAR_CS which specifies that the function return the national character set id number of the server.

See Also:

Choosing a Character Set in Oracle Database Globalization Support Guide for a list of character set identifiers.

Return Value

When the number is recognized as a valid character, VARCHAR2; otherwise, NA.

Syntax

NSL_CHARSET_NAME (number-exp)

Parameters

number-exp

A number that is the character set ID.

See Also:

Choosing a Character Set in Oracle Database Globalization Support Guide for a list of character set identifiers

Return Value

RAW

Syntax

NLSSORT(char [, 'NLS_SORT = sort[_ai |_ci]' ])

Parameters

char

A text expression.

sort

An NLS language.

_ai

Specifies an accent-insensitive sort.

_ci

Specifies a case-insensitive sort.

Examples

Return Value

BOOLEAN or NA if all the values of the expression are NA

Syntax

NONE(boolean-expression [CACHE] [dimension...])

Parameters

boolean-expression

The Boolean expression to be evaluated.

CACHE

Specifies slightly different internal behavior. Specify this keyword only when the original performance is extremely slow.

dimension

The name of a dimension of the result; or, the name of a relation between one dimension of boolean-expression and another dimension that you want as a dimension of the result.

By default, NONE returns a single YES or NO value. When you indicate one or more dimensions for the result, NONE tests for TRUE values along the dimensions that are specified and returns an array of values. Each dimension must be either a dimension of boolean-expression or related to one of its dimensions.

Tip:

When you specify a dimension that is not an actual dimension of boolean-expression, but, instead, is dimension that is related to a dimension of boolean-expression and when there are multiple relations between the two dimensions, Oracle OLAP uses the default relation between the dimensions to perform the calculation. (See the RELATION command for more information on default relations.) When you do not want Oracle OLAP to use this default relation, specify the related dimension by specifying the name of a specify relation.

Usage Notes

The Effect of NASKIP on NONE

NONE is affected by the NASKIP option. When NASKIP is set to YES (the default), and all of the values in the expression are NA NONE returns NA; if even one value is not NA, NONE ignores all of the NA values in the expression. When NASKIP is set to NO, NONE returns NA when any value of the expression is NA.

Examples

Return Value

DECIMAL

Syntax

NORMAL(mean standard-deviation)

Parameters

mean

A numeric expression that represents the mean of a normal distribution. When mean is NA, NORMAL returns NA.

standard-deviation

A numeric expression that represents the standard deviation of a normal distribution. When standard-deviation is NA, NORMAL returns the mean.

Examples

Return Value

DECIMAL

The result returned by the NPV function is dimensioned by all the dimensions of cashflows except its time dimension. When cashflows is dimensioned only by the time dimension, NPV returns a single value.

Syntax

NPV(cashflows, discount-rate, [time-dimension])

Parameters

cashflows

A numeric expression that is dimensioned by time-dimension and specifies the series of cash flow values.

Note:

All cash flows are assumed to occur at the beginning of the time period with which they are associated. The cash flows are discounted back to the beginning of the earliest time period that appears in the current status of the time dimension. NPV ignores cash flows that corresponds to out-of-status dimension positions.

discount-rate

A numeric expression that specifies the interest rate for each period to be used to discount the cash flow values. It can either be a single value or an array of values with one or more non-time dimensions. Express the discount rate as a decimal quantity; for example, 8.25 percent as .0825.

NPV accepts any positive discount rate, and it also accepts a negative discount rate when the rate is greater than minus one (that is, rate > -1). When you supply a negative rate, you must precede it with a comma.

time-dimension

A name that specifies the time dimension. When cashflows has a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, NPV automatically uses that dimension, and you can omit the time-dimension argument.

Usage Notes

NA Discount Rate

When the discount rate used to compute a result value equals NA, then that result value is NA.

Cash Flow Timing

Different assumptions about the intra-period timing of the cash flows, or the base time point for the present value calculations, can be accommodated by multiplying the result of the NPV function by the following quantity: one plus the discount rate, raised to an appropriate positive or negative power.

Examples

Return Value

NA when the expressions are equal, or the base expression when they are not.

Syntax

NULLIF (expr1 , expr2)

Parameters

expr1

An expression. The base expression for the comparison.

expr2

An expression to compare to expr1.

Examples

Return Value

INTEGER

Syntax

NUMBYTES(text-expression)

Parameters

text-expression

The TEXT expression whose bytes are to be counted.

Examples

Return Value

INTEGER

Syntax

NUMCHARS(text-expression)

Parameters

text-expression

The text expression whose characters are to be counted. NUMCHARS accepts either a TEXT or NTEXT argument. It does not perform an automatic conversion to either data type. It returns the information that is correct for the data type of the specified argument.

Examples

Return Value

INTEGER

Syntax

NUMLINES(text-expression)

Parameters

text-expression

The text expression whose lines are to be counted.

Examples

Syntax

NUMTODSINTERVAL (number, interval_unit)

Parameters

number

Any NUMBER value or an expression that can be implicitly converted to a NUMBER value.

interval_unit

A text value that specifies the unit of number and must resolve to one of the following string values: DAY, HOUR, MINUTE, or SECOND.

interval_unit is case insensitive. Leading and trailing values within the parentheses are ignored. By default, the precision of the return is 9.

Syntax

NUMTOYMINTERVAL (number, interval_unit)

Parameters

number

Any NUMBER value or an expression that can be implicitly converted to a NUMBER value.

interval_unit

A text value that specifies the unit of number and must resolve to one of the following string values: YEAR or MONTH.

interval_unit is case insensitive. Leading and trailing values within the parentheses are ignored. By default, the precision of the return is 9.

Return Value

The specified replacement value when the value of the base expression is NA, or the base expression when the value of the base expression is not NA. The data type of the return value is always the same as the data type of the base expression.

Syntax

NVL (exp , replacement-exp)

Parameters

expr

The expression to replace when it has a NA value or an empty string.

replacement-exp

The value with which you want to replace a NA value.

Examples

Return Value

The data type of the return value is always the data type of expr2 (that is, the expression whose value is returned when the value of expr1 is not NA).

Syntax

NVL2 (expr1 , expr2 , expr3)

Parameters

expr1

The expression whose value this function evaluates.

expr2

An expression whose value is returned when the value of expr1 is not an empty string or NA.

expr3

An expression whose value is returned when the value of expr1 is NA.

Usage Notes

Comparing Values of Different Data Types

When the data types of expr2 and expr3 are different, then the function converts expr3 to the data type of expr2 before comparing them.

Examples

Return Value

The return value depends on the value specified for choice. Also, when choice is applicable to only a specific type of object, and you specify a different type of object for object-name, then OBJ returns NA unless otherwise noted.

Syntax

OBJ(choice [object-name])

Parameters

object-name

A TEXT expression that is the name of the object that you want to retrieve information about.

choice

A keyword or keyword phrase which indicates the type of information you want. The following table lists the syntax, data type of the returned value, and description of valid keywords.

Table 8-2 Keywords for the choice argument of the OBJ Function

Keyword for choice	Data Type	Description of Returned Value
ACQUIRED	BOOLEAN	Whether the specified object has been acquired for modification in multiwriter mode. For a partitioned variable, returns YES only when all of the partitions of that variable have been acquire.
ACQUIREDPARTITIONS	TEXT (multiline)	The names of the partitions of the variable specified by object-name that are acquired for modification in multiwriter mode.
AGGMAP	TEXT (multiline)	The specification of the specified aggmap.
AGGMAPLIST	TEXT (multiline)	The names of the aggmap objects in the specified formula.
ALIASLIST	TEXT (multiline)	The names of the alias dimensions for the specified dimension.
ALIASOF	TEXT	The name of the base dimension for the specified alias dimension.
AW	TEXT	The name of an attached workspace that contains the specified object. When the specified object is in only one attached workspace, AW returns the name of the workspace. When the specified object is in multiple attached workspaces, AW still returns only one workspace name. You must use the AWLIST keyword to get all the relevant workspace names. When the object is not in any attached workspace, AW returns NA.
AWLIST	TEXT (multiline)	The names of the attached workspaces that contain an object with the specified name. When you specify a qualified object name for the object, AWLIST returns only the relevant workspace name. When no workspace contains the specified object, AWLIST returns NA.
BTREE	BOOLEAN	Whether a BTREE index was defined for the specified conjoint dimension or composite.
CACHEEMPTY	BOOLEAN	Whether a session cache has been emptied of data for the specified variable. A cache can be emptied by using a CLEAR statement with the CACHE keyword. When object-name is not a variable or when it has no session cache, then CACHEEMPTY returns NA. (For more information on the session cache, see "What is an Oracle OLAP Session Cache?".)
CACHECOUNT	LONG INTEGER	The number of non-NA cells in the session cache for the specified variable. When object-name is not a variable or when it does not have a no session cache, then CACHECOUNT returns NA. (For more information on the session cache, see "What is an Oracle OLAP Session Cache?".).)
CHANGED	BOOLEAN	Whether the specified variable, relation, dimension, or valueset has been modified since the last UPDATE.
CHANGEDPAGES	INTEGER	The number of pages in the analytic workspace that have changed since the last update. This is approximately the number of pages that an UPDATE command will write to disk. The larger the number of changed pages, the longer the UPDATE command takes to complete.
CLASS	TEXT	The storage class of the specified object. Possible return values are: TEMPORARY — An object whose values are not saved in the workspace; applicable to valuesets, variables, relations, and worksheets. An empty string — A permanent object whose values, when modified, are stored in a new place in the workspace until you update and are then included in the update; applicable to all object types.
DATA	TEXT	The data type of the specified object. For dimensions, variables, and formulas, possible return values are INTEGER, SHORTINTEGER, LONGINTEGER, DECIMAL, NUMBER, SHORT (for SHORTDECIMAL), BOOLEAN, ID, TEXT, NTEXT, DATE or DATETIME. For a relation, it returns the name of the related dimension. For a concat dimension, conjoint dimension, composite, or partition template, it returns the names of the base dimensions of an object as a multiline text value. For a program defined with a data type, it returns the name of the data type. For a program defined to return a value of a dimension, returns the name of the dimension. For a valueset, it returns the name of the dimension for which the valueset was defined. For other types of objects, it returns NA.
DEFINE	TEXT (multiline)	The description of the specified object. The value is the same value that DESCRIBE would display for the object minus the words DEFINE and the name of the object.
DFNCHANGED	BOOLEAN	Whether the definition of the specified object has changed since the last UPDATE.
DFNDIMS	TEXT (multiline)	The names of the dimensions and composites in the dimension list that is used to define the specified object. Note that: For an unnamed composite, it returns the form used in the object definition: SPARSE<dim1 dim2 ...>. For a dimension surrogate, it returns the name of the dimension for which the surrogate was defined. When no dimension list was used when the specified object was defined, it returns NA.
DIMMAX	INTEGER	The number of values in the specified dimension. For other object types, it returns 0 (zero). Note: When you use the DIMMAX choice with a dimension that has a read permission that restricts access to the dimension values, the result returned depends on whether the dimension has previously been loaded. Permissions are evaluated when an object is loaded. Generally, the first time you refer to an object in your session, Oracle OLAP loads the object and evaluates its permissions. However, the OBJ function does not load objects, because it is just providing information about them. When you use DIMMAX with a dimension that has not yet been loaded, the result reflects the entire number of values in the dimension, regardless of whether the dimension has read permissions. When a dimension with permissions has been loaded, then the DIMMAX choice reflects the permitted size. To ensure that the DIMMAX choice returns the permitted size, you can execute a LOAD statement before using the OBJ function.
DIMS	TEXT (multiline)	The names of the dimensions of the specified object. Specifically: For dimensions, simple, concat, or conjoint, it returns the name of the dimension itself. To find out the base dimensions of a concat or conjoint dimension, use the DATA keyword. For composites, it returns the base dimensions of the composite. For a dimension surrogate, it returns the name of the dimension for which the surrogate was defined. For dimensioned objects, it returns the names of the dimensions of the object. When an object has no dimensions, it returns NA.
DIMTYPE	TEXT	The type of the specified dimension. Specifically: For a concat dimension, it returns CONCAT. For a conjoint dimension, it returns CONJOINT. For a composite, it returns COMPOSITE. For a simple dimension, it returns the data type of the dimension. For a partition template object, it returns PARTITION TEMPLATE. For all other objects, it returns NA.
DISKSIZE	INTEGER	The total number of pages used to store the specified object. Note: For a temporary object, OBJ(DISKSIZE) returns a value of 0 (zero), because the values of a temporary object are stored in temporary storage and not in the database file.
FORMULA	TEXT	The expression in the definition of the specified formula.
HASAGGCOUNT	BOOLEAN	Whether an Aggcount object is associated with the specified variable. (For more information on Aggcount variables, see "Aggcount Variables".)
HASCACHE	BOOLEAN	Whether a session cache that is local to the session has been established to store data for the specified variable. (For more information on the session cache, see "What is an Oracle OLAP Session Cache?".)
HASH	BOOLEAN	Whether a HASH index was defined for the specified conjoint dimension or composite.
HASHSIZE	INTEGER	The number of pages allocated for the specified hashed dimension's TBLSPACE (that is, the page space containing the anchors for the hash table's K/V node lists). Note that this does not include the number of pages used for the actual Key/Value nodes, which are allocated from the RANSPACE's page space (KVPAGES).
HASPROPERTY prop-name	BOOLEAN	Whether the property specified by prop-name exists for the specified object. (Abbreviated HASPRP)
HIDDEN	BOOLEAN	Whether the text of the specified program or model has been hidden. (For more information on hiding programs and models, see the HIDE and UNHIDE commands.)
INDEXSIZE	INTEGER	The total size of all inversions associated with the specified relation or conjoint dimension.
INORDER	BOOLEAN	Whether the logical order of the values of the underlying dimensions of the specified object matches the physical order in the analytic workspace. INORDER returns TRUE when the logical order of the values of the dimension has not been specified using MAINTAIN with the keywords ADD FIRST, ADD AFTER, ADD BEFORE, or MOVE.
ISBY [RECURSIVE] dimname	BOOLEAN	When you supply object-name, whether the specified object is dimensioned by, related to, or a surrogate for the specified dimension (dimname). When you supply only dimname, whether an object is dimensioned by the dimension you specify in dimname; or when the object is an aggmap, whether the specified dimension is a dimension of any relations or models in the aggmap. RECURSIVE specifies that Oracle OLAP should search for dimname in the base dimensions of the specified object, at any level. the. See Example 8-49 . dimname is a text expression that is the name of a dimension. (Oracle OLAP automatically converts the name to uppercase.) When dimname is a composite, the value returned by ISBY indicates if an object was defined with the composite. Specify a value for object-name when the object is a dimension surrogate, variable, relation, or valueset name to learn if that object is dimensioned by or related to or a surrogate for the specified dimension. You can omit object-name when you are looping through the list of workspace objects to obtain information about multiple objects, or when you are using OBJ to limit the NAME dimension.
ISCOMPILED	BOOLEAN	Whether the compilation status of the specified compilable object (such as a program, model, or formula). The value returned depends on the type of object and on whether a compilation error was found in that object. For example: For programs, returns YES when the program has been processed by the compiler since the last time it was modified. A return value of YES does not necessarily indicate that all lines of the program are compiled. See the COMPILE command for more information. For formulas, returns YES only when the formula was compiled without finding a single error and when the formula can be saved. When the formula contains ampersand substitution, it cannot be saved. When the formula is empty, the ISCOMPILED choice returns NO. For models, returns YES only when the model was compiled without a single error found or when the model is empty. For programs, formulas, and models, returns NO when you delete an object that the program, formula, or model references.
ISCOMPRESSED	BOOLEAN	Whether the specified object is a compressed composite.
ISLATEST	BOOLEAN	Whether the specified object is the latest version of the object. This syntax is the equivalent of OBJ(VERSION)EQ OBJ(LATESTVER).
ISSOLVED	BOOLEAN	When the specified object is a variable which is dimensioned by a compressed composite, whether the variable has been aggregated.
KVSIZE	INTEGER	The number of pages currently allocated to hash and BTREE indexes.
LATESTVER	INTEGER	The current state of the specified object, referring to the most recently committed version of the object. The returned value is either equal to or greater than the value returned by OBJ(VERSION). When the value returned by LATESTVER is greater than that returned by OBJ(VERSION), then another user has updated this object since the analytic workspace was attached in read-only or read/write mode, or, if the analytic workspace was attached in multiwriter mode, since the last synchronization.
LD	TEXT (multiline)	The LD (long description) of the specified object.
MODEL	TEXT (multiline)	The specification of the specified model.
NACACHECOUNT	INTEGER	The total number of NA cached cells.
NACACHEEMPTY	BOOLEAN	Whether the NA cache is empty.
NAPAGES	INTEGER	The number of pages that contain only NA values and are not stored in the database.
NOHASH	BOOLEAN	Whether the specified conjoint dimension uses the NOHASH index algorithm to load and access data.
NUMCELLS	INTEGER	The total number of physical cells within the specified variable. Oracle OLAP determines this value by finding the Cartesian product of the OBJ(DIMMAX) values for the dimensions of the variable, taking composites into account.
NUMDELS	DECIMAL	The number of deleted cells for the specified dimensioned object.
NUMDFNDIMS	INTEGER	The number of dimensions or composites in the dimension list used to define the specified object. For this count, each composite counts as one, and the dimensions within the dimension list of the composite are not counted. An object defined with a dimension list could be a variable, relation, formula, valueset, concat or conjoint dimension, dimension surrogate, or composite. When no dimension list was used when defining the object (as for single-cell variables, programs, and so on.), it returns 0 (zero).
NUMDIMS	INTEGER	The number of dimensions of the specified dimensioned object; or the number of base dimensions of the specified composite. When the specified object is a dimension is a dimension or dimension surrogate, it returns 1, and for all other objects, it returns 0 (zero).
NUMSEGS	INTEGER	The number of analytic workspace segments associated with the specified object when that object has multiple dimensions. (Note that Oracle OLAP uses segments internally to keep track of the physical storage of the object's values. Too many segments may slow the retrieval of information.)
NUMVALS	INTEGER	The number of values or cells in the specified object. For a compressed composite or a variable dimensioned by a compressed composite, returns an INTEGER value that is the number of logical values in the object (that is, the value that would be returned if the composite was a b-tree composite).
OWNSPACE	BOOLEAN	When the specified object is a conjoint dimension or a composite defined with a BTREE index algorithm, whether the object is using private page space to store BTREE nodes. When the specified object is a variable-width text dimension, a relation, or a variable-width text variable, whether the data for the object is stored in one or more private page spaces that are associated with that object.
PARTBY	TEXT (multiline)	The names of the partition dimensions of the specified partitioned variable or partition template object. The names are returned as a multiline text value (one line for each dimension).
PARTDIMS partitions	TEXT (multiline)	The names of the dimensions of the partitions of the specified partition template or a partitioned variable. The value is returned as a multiline text value (one line for each dimension). partitions is a multiline text value (one line for each partition name) that specifies which partitions you're asking about. When you specify a partition name that is not a valid partition in partitions, an error occurs.
PARTITION partitions	TEXT (multiline)	For a partitioned variable or a partition template object, a textual description of the partitions of the specified partition template or a partitioned variable. When called on a partition template, the returned description is similar to the DEFINE PARTITION TEMPLATE statement. When called on a partitioned variable, the returned description is similar to the DEFINE VARIABLE statement. partitions is a multiline text value (one line for each partition name) that specifies which partitions you're asking about.When you specify a partition name that is not a valid partition in partitions, an error occurs.
PARTMETH	TEXT	The method (RANGE or LIST) by which the specified partition template or a partitioned variable is partitioned.
PARTNAMES	TEXT (multiline)	A multiline TEXT value containing the names of all the defined partitions of the specified partition template. When name is the name of a partitioned variable, returns a multiline TEXT value containing the names of all the partitions of the variable. Note: Not all of the partitions defined by a partition template necessarily exist in each partitioned variable. Calling OBJ(PARTNAMES)on a partitioned variable returns only those partitions that actually exist within the variable.
PARTRANGE partitions	TEXT (multiline)	The values of the LESS THAN clause for each of the partitions for the specified RANGE partition template or RANGE partitioned variable. partitions is a multiline text value (one line for each partition name) that specifies which partitions you're asking about. When you specify a partition name that is not a valid partition in partitions, an error occurs.
PERIOD	TEXT	When the specified object is a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, the type of the dimension plus an indication of multiple periods or phasing, if any.
PHYSMAX	INTEGER	The maximum physical value for positions within the specified dimension or composite.
PMTMAINTAIN	TEXT	The permission condition for the maintain permission associated with the specified dimension. When there is no maintain permission for the dimension, it returns NA.
PMTPERMIT	TEXT	The permission condition for the permit permission associated with the specified object. When there is no permit permission for the object, it returns NA.
PMTREAD	TEXT	The permission condition for the read permission associated with the specified object. When there is no read permission for the object, it returns NA.
PMTWRITE	TEXT	The permission condition for the write permission associated with the specified object. When there is no write permission for the object, it returns NA.
PRECISION	INTEGER	The precision of the specified NUMBER dimension or variable. The precision is the total number of digits. When the variable was defined without a precision specification, then OBJ returns NA.
PROGRAM	TEXT (multiline)	The text of the specified program.
PROPCHANGED	BOOLEAN	Whether the properties of the specified object have changed since the last UPDATE.
PROPERTY prop-name	WORKSHEET	The value of the specified object's property as specified by prop-name which is a text expression that specifies the name of the property. The data type of the return value is determined at run time. When the named property does not exist, it returns NA. (Abbreviated PRP)
PROPERTYLIST	TEXT (multiline)	The names of the properties associated with the specified object, one property on a line. The names are in uppercase letters and are stored in the collating sequence for ASCII characters. For objects without properties, it returns NA. (Abbreviated PRPLIST)
PROPERTYTYPE prop-name	TEXT	The data type of object's property as specified by prop-name which is a text expression that specifies the name of the property. The type is derived from the expression used in the PROPERTY statement that created the property. Possible return values are BOOLEAN, TEXT, ID, DATE, DATETIME, NUMBER, INTEGER, LONGINTEGER, DECIMAL, and SHORT.When the named property does not exist or has a value of NA, it returns NA. (Abbreviated PRPTYPE)
PUSHCOUNT	INTEGER	The number of times PUSH has executed for the specified executed (that is, the number of pushed values currently saved for the specified object).
REFERS [RECURSIVE] text-expression	TEXT (multiline)	The words found in the specified compilable object (for example, a program) that match the ones you specify in text-expression. REFERS returns NA when it does not find any of the specified words, when the specified object is not a compilable object, or when the workspace does not contain any compilable objects. When you supply both arguments, REFERS searches only the specified object for the listed words. When you omit object-name, REFERS searches all the compilable objects in the current workspace. text-expression is a multiline TEXT expression that is the words for which it should search. Each line in the text value is considered a separate word to be searched for. When, for text-expression, you specify a list of words that is the result of the OBJLIST function, you can produce a cross-reference for compilable objects in the current workspace. RECURSIVE specifies that Oracle OLAP should search (following the calling tree) for the text-expression to retrieve a list of all of the occurrences of text-expression, at any level. Tip: The search is not case-sensitive; REFERS treats TEXTVAR and Textvar as the same word. REFERS ignores all text that is included in a comment or enclosed in single quotes.
RELATION relation-query	TEXT (multiline)	The default relation (as specified the RELATION command) for the specified object. The values that are returned vary depending on what you specify for relation-query. The syntax for relation-query is: DEFINELIST | DIMLIST | ACTUAL rel-dimname | SPECIFIED rel-dimname where: DEFINELIST specifies that the function return all of the names of the dimensions and their associated default relations. As shown in the following example, the names are returned and it associated default relation are returned one per line, alternating between dimension name and its associated default relation. dimname1 relname1 dimname2 relname2 DIMLIST specifies that the function return all of the names of the dimensions for which default relations have been specified. As shown in the following example, Oracle OLAP returns the values one dimension name per line. dimname1 dimname2 ACTUAL dimname specifies that the function return the relation that Oracle OLAP uses as the default relation between object-name and its related dimension specified by rel-dimname. SPECIFIED dimname specifies that the function return the name of the relation specified in the RELATION command as the default relation between object-name and its related dimension specified by rel-dimname which is the value as actually entered in the RELATION command, even if an error occurred entering the data and there is no such relation in the analytic workspace.
RSSIZE	INTEGER	The number of random subset pages used by main object.
SCALE	INTEGER	The scale of a NUMBER dimension or variable. A positive scale indicates the number of digits to the right of the decimal point. A negative scale indicates the number of rounded digits to the left of the decimal point. When the variable was defined without a scale specification, then OBJ returns NA.
SEGWIDTH {dim-name|ALL}	TEXT (multiline)	The default or user-specified segment size of a variable that has multiple dimensions and that is associated with either a particular dimension or all dimensions. Each line begins with a segment-size (up to 11 digits) followed by the name of the associated dimension or composite. The dimension name is not included in the line when you specify a dimension and its dimensioned object. In that case only the segment value is returned. When the segment size is reported as zero, it means the default segment size is in effect, and therefore you may have to use CHGDFN to set an appropriate size for the variable's segments. dim-name is a text expression that is the name of a dimension. ALL specifies all dimensions.
SESSION	BOOLEAN	Whether the specified object is a session object.
SHAREMAP	BOOLEAN	When the specified object is dimensioned by a compound dimension, whether the compound dimension is shared with other objects.
SPARSE	TEXT (multiline)	The composites used in the definition of the specified object.
SURROGATELIST	TEXT (multiline)	The surrogates defined for the specified surrogate or dimension.
TRIGGER [triggering-event}	TEXT (multiline)	TRIGGER without a triggering-event keyword returns all the triggering-event keywords and trigger programs names associated with the specified object; or NA when the object does not have any trigger programs associated with it. TRIGGER with a triggering-event keyword returns the names of the trigger programs associated with the specified object event. Specify the triggering-event using one of the following keywords: MAINTAIN DELETE PROPERTY ASSIGN BEFORE_UPDATE AFTER_UPDATE
TYPE	TEXT	The object type of the specified object.
UNIQUE	BOOLEAN	Whether the specified concat dimension is unique. Returns FALSE for other dimensions, and NA for other object types (including Partition Templates).
UPDATED	BOOLEAN	When the analytic workspace is attached in multiwriter mode, whether the specified object been updated. For other attachment modes, returns NA.
VALCOUNT	INTEGER	The number of logical uncompressed values in the specified compressed composite. For all other dimensions the result is identical to DIMMAX.
VALSIZE	INTEGER	The number of pages used to store the specified object's values. For text dimensions and variables defined without the WIDTH keyword, OBJ(VALSIZE) counts only those pages that contain the four-byte pointers to the text, not the storage for the text itself. For a temporary variable, OBJ(VALSIZE) returns a value of 0 (zero) because the values of a temporary variable are stored in temporary storage and not in the analytic workspace.
VERSION	INTEGER	The current state of the attached version of the specified object. (Note that changes that are updated and committed increase this number.)
VNF	TEXT	When the specified object is a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR for which you have defined a VNF (value name format), the VNF of the dimension. For other types of objects, it returns NA.
WIDTH	INTEGER	The width, in bytes, of the storage area of each value of the specified object: For dimensioned INTEGER and BOOLEAN variables that you defined with a width, it returns 1. For dimensioned text variables and text dimensions that you defined with a width, it returns a value between 1 and 4000, which identifies the defined width. For all other objects, it returns NA.

object-name

A text expression that contains the name of the object in which you are interested. The object can be in any attached workspace. When you specify object-name as a text literal, you must enclose it in single quotes. (Oracle OLAP automatically converts the name to uppercase.) When you specify the name of a program as the object-name and you omit the quotes, Oracle OLAP runs the program and uses its return value as the name of the object to be supplied as object-name.

You can omit object-name when you are using the OBJ function as part of a statement, such as the LIMIT command, that loops through the NAME dimension. In this case, the return value is dimensioned by the NAME dimension in the current workspace.

Examples

Return Value

TEXT

Syntax

OBJLIST[(text-expression)]

Parameters

text-expression

A text expression that contains a single name or several names of currently attached workspaces. Each workspace name must be on a separate line of a multiline TEXT value. When you do not supply this argument, OBJLIST uses the current workspace name. When text-expression includes the name of an analytic workspace that is not attached, OBJLIST does not return a value. Instead, it signals an error.

Examples

Return value

Analytic workspace object name or TEXT; or NA if an analytic workspace has the requested definition information, but the field is empty.

Syntax

OBJORG(keyword [OWNER owner ] top-level-object-name [ sub-object-name ] )

Parameters

keyword

The valid keywords vary by the type of top-level-object or sub-object specified, as documented in:

• Table 8-3.

• Table 8-4.

• Table 8-5.

• Table 8-6.

• Table 8-7.

• Table 8-8.

• Table 8-9.

Table 8-3 Keywords when the top-level-object is a Cube

Keyword	Returns
Aggmap	The aggmap for the cube. (Note: The function will never return NA when you specify this keyword.)
Composite	When a composite is used for the cube, the name of the composite; or NA if the cube's sparsity type is DENSE, or if the cube is partitioned. (See also the keyword PartitionComposite.)
CubeStorageType	(Property.) The DML data type used for all of the cube's measures; or NA if there is no such single data type (that is, if different measures have different data types).
MeasureDim	When the value returned for the keyword CubeStorageType is not NA, the analytic workspace measure dimension used to refer to the cube's base measures; or NA when the value returned for the keyword CubeStorageType is NA.
PartitionCompositeVar	The text variable, dimensioned by PartitionDim, that holds the names of the composites for the cube's partitions; or NA if the cube's sparsity type is DENSE, or if the cube is not partitioned. (See also the Composite keyword.)
PartitionDim	The analytic workspace dimension that contains the names of the partitions for the cube; or NA if the cube is not partitioned.
PartitionRel	The relation, dimensioned by PartitionDim, that relates each cube partition to its corresponding root dimension value; or NA if the cube is not partitioned.
PartitionTemplate	The partition template relating each cube partition to its corresponding analytic workspace dimension values; or NA if the cube is not partitioned.
SharedMeasureVar	When the cube has a storage type, the variable (of that type) that stores all of the cube's measures; or NA when it does not.
SparseType	(Property.) The cube sparsity type. Valid values are: COMPRESSED, SPARSE, DENSE, or SPARSE_GLOBAL. Corresponds to column SPARSE_TYPE in the USER_CUBES and ALL_CUBES views.

Table 8-4 Keywords when the top-level-object is a Cube and the sub-object is a Measure

Keyword	Returns
Measure	When the value returned when you specify the CubeStorageType keyword for a cube is NA, the variable that stores this measure; or when not NA, the formula extracting this measure from the cube's top level variable.
OverrideAggMap	The aggmap for the measure, if the measure has one; otherwise returns NA. (Note that when the measure has no aggmap, then the cube's aggmap is used.)

Table 8-5 Keywords when the top-level-object is Cube and the sub-object is a Dimension Calculation Model

Keyword	Returns
AggFloorValueSet	If the cube's consistent solve specification specifies an aggregation at a specific level, the valueset consisting of all members of the dimension that belong to that level; otherwise, NA.
HierValueSet	The valueset containing the names of all hierarchies of the analytic workspace dimension over which the cube is aggregated. (Note that when you specify this keyword, the function never returns NA.)
OperVar	If at least one of the cube's measures has an OverrideAggMap, the variable, dimensioned by the cube's measure dimension, that contains the aggregation operator used for each measure for which an OverrideAggMap exists.; otherwise, NA.
PrecomputeValueSet	When the cube organization specifies a precompute condition, the valueset containing all members of the dimension that satisfy that condition; otherwise NA. (In particular, when the cube organization specifies a precompute percent, returns NA.)

Table 8-6 Keywords when the top-level-object is a Cube Dimension

Keyword	Returns
AggRel	The relation, dimensioned by Dim and HierDim, that relates each dimension value (in each hierarchy) to its parent dimension value (in that hierarchy). (Note that when you specify this keyword, the function never returns NA, even if the dimension has no hierarchies.)
AttributeCubeGIDRel	The relation, dimensioned by Dim, that relates each dimension value to its cube GID (including attribute columns) in ___AW_GID_DIMENSION. AttributeCubeGIDRel is the same as CubeGIDRel, except it includes attribute columns. (Note that when you specify this keyword, the function never returns NA.)
CubeGIDRel	The relation, dimensioned by Dim, that relates each dimension value to its cube GID in ___AW_GID_DIMENSION. When the dimension does not have a materialized view (MV), then CubeGIDRel is empty. The difference between a GID and a cube GID is that cube GIDs span all hierarchies. (Note that when you specify this keyword, the function never returns NA.)
Dim	The analytic workspace physical dimension corresponding to this logical dimension. (Note that when you specify this keyword, the function never returns NA.)
DimOrderVar	The variable, dimensioned by Dim and ___AW_ALL_LANGUAGES, that contains integers (for each dimension value, in each language) used for sorting. (Note that when you specify this keyword, the function never returns NA.)
FamilyRel	The relation, dimensioned by Dim, LevelDim, and HierDim, that relates each dimension value (in each level, in each hierarchy) to the dimension value (at that level, in that hierarchy), if any, of which it is a descendant. FamilyRel is similar to ParentRel, but it allows you to specify a level rather than automatically choosing the next level up. FamilyRel is similar to ValueFamilyRel, but used for level hierarchies. (Note that when you specify this keyword, the function never returns NA.)
GIDRel	The relation, dimensioned by Dim and HierDim, that relates each dimension value (in each hierarchy) to its GID, if any, in ___AW_GID_DIMENSION. (Note that when you specify this keyword, the function never returns NA.)
HierDim	The dimension containing all hierarchies for this dimension. (Note that when you specify this keyword, the function never returns NA.)
HierLevelValueSet	The valueset, dimensioned by HierDim, that (for each hierarchy) contains all levels (from the LevelDim) that belong to that hierarchy. (Note that when you specify this keyword, the function never returns NA.)
HierOrderVar	The variable, dimensioned by Dim, HierDim, and ___AW_ALL_LANGUAGES, that contains integers (for each dimension value, in each hierarchy, in each language) used for sorting. (Note that when you specify this keyword, the function never returns NA.)
InHierValueSet	The valueset, dimensioned by HierDim, that (for each hierarchy) contains all dimension values (contained in that hierarchy). (Note that when you specify this keyword, the function never returns NA, even if the dimension has no hierarchies.)
LevelDim	The dimension containing all levels for this dimension. (Note that when you specify this keyword, the function never returns NA, even if the dimension has no levels.)
LevelRel	The relation, dimensioned by the Dim, that relates each dimension value to a level in the LevelList. (Note that when you specify this keyword, the function never returns NA.)
MemberDepthRel	The relation, dimensioned by Dim and HierDim, that relates each dimension value (in each hierarchy) to its depth (if any) in ___AW_DEPTH_DIMENSION. (Note that when you specify this keyword, the function never returns NA.)
MemberSourceRel	The relation, dimensioned by Dim, that relates each dimension value to an element of ___AW_MEMBER_SOURCES. (Note that when you specify this keyword, the function never returns NA.)
ParentRel	The relation, dimensioned by Dim and HierDim, that relates each dimension value (in each hierarchy) to its parent dimension value (in that hierarchy). (Note that when you specify this keyword, the function never returns NA, even if the dimension has no hierarchies.)
ValueFamilyRel	The relation, dimensioned by Dim, ___AW_GID_DIMENSION, and HierDim, that relates each dimension value (for each GID, in each hierarchy) to the dimension value (with that GID, in that hierarchy), if any, of which it is a descendant. ValueFamilyRel is similar to FamilyRel, but used for value hierarchies. (Note that when you specify this keyword, the function never returns NA.)

Table 8-7 Keywords when the top-level-object is a Cube Dimension and the sub-object is a Base Attribute

Keyword	Returns
Attribute	The value returned varies depending on whether or not the dimension is indexed. When the dimension is not indexed, the (physical) variable, dimensioned by Dim corresponding to the attribute's dimension and used to hold this (logical) attribute. When the dimension is indexed, the (physical) relation, dimensioned by Dim corresponding to the attribute's dimension, that relates each dimension value to a corresponding value from AttributeDim. (Note that when you specify this keyword, the function never returns NA.)
AttributeDim	When the attribute is indexed, the dimension that stores all of the attributes values; otherwise NA.
HierUniqueKeyRel	When the attribute is a unique key attribute, the relation, dimensioned by Dim and HierDim corresponding to the attribute's dimension, that relates each analytic workspace dimension value (in each hierarchy) to a corresponding value from AttributeDim; otherwise, NA.
UniqueKeyRel	When the attribute is a unique key attribute (that is, indexed), the relation, dimensioned by Dim, that corresponds to the attribute's dimension, that relates each dimension value to a corresponding value from AttributeDim.; otherwise, NA.

Table 8-8 Keywords when the top-level-object is a Cube Dimension and the sub-object is a Dimension Level

Keyword	Returns
SurrogateDim	The surrogate for the owning dimension's Dim that contains only those dimension values contained in this dimension level. (Note that when you specify this keyword, the function never returns NA.)

Table 8-9 Keywords when the top-level-object is a Cube Dimension and the sub-object is a Dimension Calculation Model

Keyword	Returns
Model	The model corresponding to this dimension's calculation model. (Note that when you specify this keyword, the function never returns NA.)

OWNER owner

The owner of the cube or cube dimension identified by top-level-object. If you do not include this clause, the function uses the current owner.

top-level-object

A text expression that specifies the name of the cube or the cube dimension for which information is requested.

sub-object

A text expression that specifies the name of the "logical" OLAP sub-object, if any, for which information is requested. Valid values vary depending on whether the top-level-object is a cube or a cube dimension:

• When top-level-object is a cube, you can specify the name of a measure or dimensionality

• When top-level-object is a cube dimension, you can specify the name of a base attribute, hierarchy, dimension level, or dimension calculation model.

Examples

Examples of using OBJORG with various keyword appear in several sample OLAP DML programs described within this manual:

• For examples of using OBJORG with the Dim keyword, see the following sample OLAP DML programs:

  • ADD_L1_2 program in Example 9-5

  • REMOVE_L2_1 program in Example 10-98

  • SETUP_PRE_MODEL and SETUP_POST_MODEL programs in Example 10-118

  • ADD_L1_2_DATES program in Example 10-169

• For an example of using OBJORG with the Measure keyword, see the ADD_L1_2 program in Example 9-5.

• For an example of using OBJORG with the Parentrel keyword, see the REMOVE_L2-1 program in Example 10-98.

• For examples of using OBJORG with the Attribute keyword, see the following sample programs:

  • SETUP_POST_MODEL program in Example 10-118

  • ADD_L1_2_DATES program in Example 10-169

Return Value

TEXT

Syntax

OBSCURE({HASH|HIDE|UNHIDE} [TEXT] seed-exp input-exp)

Parameters

HASH

Specifies that Oracle OLAP encodes the input text expression according to the seed expression that you specify. With the HASH keyword:

• Encoded values cannot be restored to their original form.

• The same seed expression and input text always produce the same result.

HIDE

Specifies that Oracle OLAP encodes the input text expression according to the seed expression that you specify. With the HIDE keyword:

• Encoded values can be un-encoded to their original form with UNHIDE.

• The same seed expression and input text always produce different results.

The HIDE keyword provides a mechanism for storing values in encoded form while actually comparing their un-encoded values.

UNHIDE

When specified with the original seed expression, decodes values encoded with the HIDE keyword to their original form. See "Decoding Text".

TEXT

The TEXT keyword causes OBSCURE to convert binary data to text, such that the return value consists only of text data. When you do not specify the TEXT keyword, the output of OBSCURE is binary data. See "Decoding Text", and "Example 8-59".

seed-exp

A single-line case-sensitive text expression that is used as a seed value when encoding of the input text expression.

input-exp

A single-line case-sensitive text expression to be encoded or decoded by OBSCURE.

Usage Notes

Decoding Text

When you have used OBSCURE(HIDE) with the TEXT keyword to encode a text expression, you must also specify the TEXT keyword with OBSCURE(UNHIDE) to decoded expression to its original value.

Examples

Return Value

NUMBER

Syntax

ORA_HASH (expr [, max_bucket [, seed_value ] ])

Parameters

expr

The data for which you want the function to compute a hash value. There are no restrictions on the type or length of data represented by expr.

max_bucket

The maximum bucket value returned by the function. You can specify any value between 0 and 4294967295. The default is 4294967295.

seed_value

A value between 0 and 4294967295. The default is 0. When you want to produce different has values for the same set of data, specify a value for this argument.

Return Value

Text

Syntax

PARTITION (partition_template_values)

Parameters

partition_template_values

An expression that represents one or more values of the partition template (for example, the name of a partition template or a QDR).

Examples

Return Value

BOOLEAN.

YES when Oracle OLAP would not issue an error when aggregating a variable partitioned using the specified partition template using the specified aggmap; or NO when an error would occur.

Syntax

PARITITONCHECK (aggmap parttition-template)

Parameters

aggmap

A text expression that is the name of an aggmap object.

partition-template

A text expression that is the name of the partition template object to check for compatibility with aggregation.

Return Value

DECIMAL

Syntax

PERCENTAGE(expression [CACHE] [BASEDON dimension-list])

Parameters

expression

The numeric expression for which percent figures are to be computed.

CACHE

Specifies slightly different internal behavior. Specify this keyword only when the original performance is extremely slow.

BASEDON dimension-list

An optional list of one or more of the dimensions of expression on which to base the percentage for each value. When you do not specify the dimensions, then PERCENTAGE bases the percentage on the total of all of the values of all of the dimensions of expression.

Usage Notes

The Effect of NASKIP on PERCENTAGE

PERCENTAGE is affected by the NASKIP option. When NASKIP is set to YES (the default), then PERCENTAGE ignores NA values. When NASKIP is set to NO, then PERCENTAGE returns NA for any cell in expression whose value is NA.

Examples

Return Value

The value that is returned has the same data type as the expression being qualified.

Syntax

QUAL(expression, dimname1 dimexp1 [, dimnameN dimexpN])

Parameters

expression

The expression being qualified. Use QUAL to qualify complex expressions that contain computation, function calls, or ampersand substitution. You can also use QUAL when the expression is a simple variable name. However, QUAL is not required for simple expressions, and you can use the following standard QDR syntax.

expression(dimname1 dimexp1 [, dimname2 dimexp2 ...])

dimname

The dimension to be limited. You can specify one or more of the dimensions of the expression. Each dimension must be paired with a dimexp. You can specify a dimension surrogate instead of the dimension.

dimexp

An expression that represents the value to which the dimension should be limited. The expression can be a value of the dimension, a text expression whose result is a value of the dimension, a numeric expression whose result is the logical position of a value of the dimension, or a relation of the dimension.

When the dimension being limited is a conjoint dimension, then dimexp must be enclosed in angle brackets and must include a value for each of its base dimensions.

When the dimension being limited is a concat dimension, then dimname and dimexp can be one combination listed in the following table:

Table 8-10 Valid dimname and dimexp Combinations for Concat Dimensions

dimname	dimexp
The name of the concat dimension	A value of the concat dimension
The name of the concat dimension	The name of a base dimension
The name of a base dimension of the concat dimension	A value of the base dimension
The name of a base dimension of the concat dimension	The name of the concat dimension

Examples

Return Value

DECIMAL

Syntax

RANDOM([lowbound] [highbound])

Parameters

lowbound

A numeric expression that specifies the lower boundary for the random number series. The default is 0. When lowbound is NA, the RANDOM function produces NA.

highbound

A numeric expression that specifies the upper boundary for the random number series. The default is 1. When highbound is NA, the RANDOM function produces NA

Examples

Return Value

DECIMAL

Syntax

RANK(expression method [attributes] [BASEDON dimension-list])

where attributes is one or more of the following:

• RESET
• NAFIRST
• NALAST
• LIMITSAVE (limit-expression...)
• TIEBREAKERS (target-expression...)

Parameters

expression

The numeric expression for which rankings are to be computed.

method

The method to use in computing the rank of the values in expression. The method argument can be one of the following keywords. See also "Results of Method Values".

Table 8-11 Methods for Computing RANK

Method	Description
MIN	Identical values get the same minimum rank.
MAX	Identical values get the same maximum rank.
AVERAGE	Identical values get the same average rank.
PACKED	Identical values get the same rank but the results are packed into consecutive INTEGER values.
UNIQUE	All values get a unique rank; for identical values the rank is arbitrary.
PERCENTILE	Values are ranked from 1 to 100, based on the relative frequency of their occurrence in the expression.
DECILE	Values are ranked from 1 to 10, based on the relative frequency of their occurrence in the expression.
QUARTILE	Values are ranked from 1 to 4, based on the relative frequency of their occurrence in the expression.

RESET

Changes how Oracle OLAP computes RANK within a looping statement (for example, an assignment statement):

• When you do not specify RESET, Oracle OLAP ranks the members of each group only once and, then caches those ranked values. As the looping statement continues to execute and RANK executes against same set of values, Oracle OLAP uses those cached values to return values for RANK.

• When you include RESET, Oracle OLAP recomputes RANK each and every time it executes within the looping statement. This behavior significantly increases the time it takes for Oracle OLAP to execute the looping statement that contains RANK.

Within a looping statement, the only time you use RANK with RESET is when you know that within any group the rankings of members within that group will change during the execution of the looping statement.

NAFIRST

Specifies that Oracle OLAP converts all NA values to the largest positive decimal number or (10**308) before ranking the values.

Note:

An NA expression value produces an NA rank unless you specify either NAFIRST or NALIST.

NALAST

Specifies that Oracle OLAP converts all NA values to the largest negative decimal number or -(10**308) before ranking the values.

Note:

An NA expression value produces an NA rank unless you specify either NAFIRST or NALIST.

LIMITSAVE

Specifies the status of the dimensions that Oracle OLAP uses when calculating RANK. By specifying LIMITSAVE within the RANK function, rather than specify CHGDIMS with LIMITSAVE, you insure that Oracle OLAP evaluates the status only once when RANK needs to calculate a new result.

limit-expression

The dimension values that Oracle OLAP uses to determine dimension status while executing RANK. For the limit-expression argument, you can specify any expression including a valueset, a LIMIT function, or a SORT function.

TIEBREAKERS

Specifies how Oracle OLAP sequences values of equal rank.

tiebreaker-expression

Any expression including a valueset, a LIMIT function, or a SORT function. Oracle OLAP executes the tiebreaker-expressions in the order in which they are specified. The status of the dimensions of each tiebreaker-expression is the current status of the dimensions or the status specified in the LIMITSAVE clause, if any.

Note:

When you specify a valueset for tiebreaker-expression, Oracle OLAP returns the ranked items in -(STATRANK) order.

BASEDON dimension-list

An optional list of one or more of the dimensions of expression to include in the ranking. When you do not specify the dimensions, then RANK bases the ranking on all of the dimensions of expression.

Note:

When the current value of a BASEDON dimension is not in ranking status, Oracle OLAP returns a rank of NA.

Usage Notes

Monitoring the Behavior of RANK

The OLAP DML provides the RANK_CALLS, RANK_CELLS, and RANK_SORTS options that you can use to monitor the behavior of the RANK function.

RANK_CALLS

The RANK_CALLS option is an INTEGER, read-only option that holds the number of calls that Oracle OLAP has made to the RANK function.

RANK_CELLS

The RANK_CELLS option is an INTEGER, read-only option that holds the number of values that Oracle OLAP has computed when executing the RANK function.

RANK_SORTS

The RANK_SORTS option is a read-only option that holds the number of sorts that have been triggered by the execution of the RANK function

Results of Method Values

This note describes the results of the different methods of ranking values. The results are based on the sales2 variable, which is described in "Example 8-65", with the geography dimension limited to G2 as the following statements demonstrate.

LIMIT geography TO 'G2'
SORT items D sales2
REPORT DOWN geography sales2

The preceding statements produce the following output.

               ------------------------SALES2------------------------
               ------------------------ITEMS-------------------------
GEOGRAPHY        ITEM4      ITEM2      ITEM3      ITEM1      ITEM5
-------------- ---------- ---------- ---------- ---------- ----------
G2                  25.00      20.00      20.00      15.00       7.00

The following table shows the results of the different methods of ranking that are produced by a statement of the form

REPORT DOWN geography RANK(sales2 MIN BASEDON items)

with the different method keywords substituted for MIN.

Note that the value that is returned by the UNIQUE method for Item2 and Item3 can be either 2 or 3, because the RANK function randomly assigns a unique rank for identical values in the expression.

Examples

Returns

TEXT

Syntax

RAWTOHEX(raw-exp)

Parameters

raw-exp

A raw expression.

Return Value

INTEGER

Syntax

RECNO(fileunit)

Parameters

fileunit

A file unit number assigned to a file opened for reading in a previous call to the FILEOPEN function.

Usage Notes

Opening Files

Before you can use the RECNO function, you must open the file for reading. When the file unit number is not associated with an open file or the file has been opened for writing, RECNO returns an error.

Using RECNO with FILEGET

RECNO is usually used with FILEREAD or FILENEXT, which read whole records. When you are reading data from a file with the FILEGET function, which can read partial records, RECNO returns the number of times you have read data from the file, not the number of actual records.

LINENUM Option

See also the LINENUM option, which holds the current line number of output.

Records in Text Files

When the file is a text file, a record is delimited by a newline character. When the file is a binary file, you must set the file's LSIZE attribute to the record length with a FILESET statement. TEXT is the default file type.

Examples

Return value

When a match is found, an INTEGER that indicates the number of occurrences of the pattern; otherwise 0.

Syntax

REGEXP_COUNT (source_char, pattern [, position [, match_param]])

Parameters

source_char

The text expression for which the function searches.

pattern

The text expression for which the function searches. It is usually a text literal and can contain up to 512 bytes. If the data type of pattern is different from the data type of source_char, then the function converts pattern to the data type of source_char.

Note that the function ignores subexpression parentheses in pattern. For example, the pattern '(123(45))' is equivalent to '12345'. Also, the function interprets a period as a wildcard character that matches any character.

See:

For a listing of the operators you can specify in pattern, see "Oracle Regular Expression Support" in Oracle Database SQL Language Reference.

See Also:

"Oracle Regular Expression Support" in Oracle Database SQL Language Reference for a listing of the operators that you can specify in pattern

position

A positive INTEGER indicating the character of source_char where the function should begin the search. The default is 1, meaning that the function begins the search at the first character of source_char. After finding the first occurrence of pattern, the function searches for a second occurrence beginning with the first character following the first occurrence.

match_parameter

A text expression that lets you change the default matching behavior of the function. You can specify one or more of the values shown in the following table.

Value	Specifies
c	Case-sensitive matching.
i	Case-insensitive matching.
m	Treat the source string as multiple lines. The function interprets ^ (caret) and $ (dollar sign) as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. By default, the function treats the source string as a single line.
n	A newline character is among the characters matched by a period (the wildcard character). By default, it is not.
x	Ignores whitespace characters.

If you specify multiple contradictory values for this argument, then the function uses the last value. For example, if you specify 'ic', then the function uses case-sensitive matching. If you specify a character other than those shown above, then the function returns an error.

If you omit this optional argument, then the default case sensitivity is determined by the value of the NLS_SORT parameter; a period (.) does not match the newline character; and the source string is treated as a single line.

Return Value

When the pattern is found a NUMBER; otherwise 0.

Syntax

REGEXP_INSTR (source_char, pattern[, position[, occurrence[, return_option[, match_parameter ]]]])

Parameters

source_char

The text expression for which the function searches.

pattern

The text expression for which the function searches. It is usually a text literal and can contain up to 512 bytes. The function interprets a period as a wildcard character that matches any character.

position

A positive integer that indicates the character of source_char at which the function begins the search. The default value of position is 1, which means that the function begins searching at the first character of source_char.

occurrence

A positive integer that indicates which occurrence of pattern the function should search for. The default value of occurrence is 1, meaning that the function searches for the first occurrence of pattern.

return_option

Specify 0 (zero) when you want the function to return the position of the first matched character (default), or 1 when you want the function to return the position of the character following the match.

match_parameter

A text expression that lets you change the default matching behavior of the function. You can specify one or more of the values shown in the following table.

Value	Specifies
c	Case-sensitive matching.
i	Case-insensitive matching.
m	Treat the source string as multiple lines. The function interprets ^ (caret) and $ (dollar sign) as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. By default, the function treats the source string as a single line.
n	A newline character is among the characters matched by a period (the wildcard character). By default, it is not.
x	Ignores whitespace characters.

Examples

The following statement

REGEXP_INSTR('Mississippi', 'i', 1, 3) 

searches the string Mississippi for the third instance of the letter i, beginning the search at the first letter. It returns the value 8.

Return Value

VARCHAR2

Syntax

REGEXP_REPLACE(source_char, pattern[, replace_string[, position[, occurrence[, match_parameter ]]]])

Parameters

source_char

The text expression for which the function searches.

pattern

The text expression for which the function searches. It is usually a text literal and can contain up to 512 bytes. The function interprets a period as a wildcard character that matches any character.

replace_string

The text that replaces pattern in source_char.

position

A positive integer that indicates the character of source_char at which the function begins the search. The default value of position is 1, which means that the function begins searching at the first character of source_char.

occurrence

A positive integer that indicates which occurrence of pattern the function should search for. The default values of occurrence is 1, meaning that the function searches for the first occurrence of pattern.

match_parameter

A text expression that lets you change the default matching behavior of the function. You can specify one or more of the values shown in the following table.

Value	Specifies
c	Case-sensitive matching.
i	Case-insensitive matching.
m	Treat the source string as multiple lines. The function interprets ^ (caret) and $ (dollar sign) as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. By default, the function treats the source string as a single line.
n	A newline character is among the characters matched by a period (the wildcard character). By default, it is not.
x	Ignores whitespace characters.

Example

The following statement

REGEXP_REPLACE('500   Oracle     Parkway,    Redwood  Shores, CA', '( ){2,}', ' ')

eliminates extra spaces and returns the following string

500 Oracle Parkway, Redwood Shores, CA

Return Value

VARCHAR2 | CLOB

Syntax

REGEXP_SUBSTR(source_char, pattern[, position[, occurrence[, match_parameter ]]])

Parameters

source_char

The text expression for which the function searches.

pattern

The text expression for which the function searches. It is usually a text literal and can contain up to 512 bytes. The function interprets a period as a wildcard character that matches any character

position

A positive integer that indicates the character of source_char at which the function begins the search. The default value of position is 1, which means that the function begins searching at the first character of source_char.

occurrence

A positive integer that indicates which occurrence of pattern the function should search for. The default value of occurrence is 1, which means that the function searches for the first occurrence of pattern.

match_parameter

A text expression that lets you change the default matching behavior of the function. You can specify one or more of the values shown in the following table.

Value	Specifies
c	Case-sensitive matching.
i	Case-insensitive matching.
m	Treat the source string as multiple lines. The function interprets ^ (caret) and $ (dollar sign) as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. By default, the function treats the source string as a single line.
n	A newline character is among the characters matched by a period (the wildcard character). By default, it is not.
x	Ignores whitespace characters.

Examples

Return Value

DECIMAL

Syntax

REM(expression1 expression2)

Parameters

expression

REM returns the remainder of expression1 divided by expression2.

Examples

Return Values

Numeric. Oracle OLAP determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.

Syntax

REMAINDER (diviidend, divisor)

Parameters

dividend

A numeric expression (or an expression that Oracle OLAP can implicitly convert to a numeric expression) that is the number you want to divide.

divisor

A numeric expression (or an expression that Oracle OLAP can implicitly convert to a numeric expression) that is the divisor.

Examples

Return Value

TEXT

Syntax

REMBYTES(text-expression start [length])

Parameters

text-expression

The TEXT expression from which REMBYTES removes bytes. When the characters to be removed from text-expression contain embedded line breaks, these breaks are also removed. Other line breaks are preserved. Removed line breaks are not counted toward the total number of characters removed.

Tip:

When you must use this function on NTEXT values, use the CONVERT or TO_CHAR function to convert the NTEXT value to TEXT.

start

An INTEGER that represents the character position at which to begin removing characters. The position of the first character in text-expression is 1. When the value of start is greater than the length of text-expression, REMBYTES simply returns text-expression.

length

An INTEGER that represents the number of characters to be removed. When length is not specified, only the character at start is removed.

Examples

Return Value

TEXT or NTEXT

Syntax

REMCHARS(text-expression start [length])

Parameters

text-expression

The expression from which REMCHARS removes characters. When the characters to remove from text-expression contain embedded line breaks, these breaks are also removed. Other line breaks are preserved. Removed line breaks are not counted toward the total number of characters removed.

When you specify a TEXT expression, the return value is TEXT. When you specify an NTEXT expression, the return value is NTEXT.

start

An INTEGER that represents the character position at which to begin removing characters. The position of the first character in text-expression is 1. When the value of start is greater than the length of text-expression, REMCHARS simply returns text-expression.

length

An INTEGER that represents the number of characters to be removed. When length is not specified, only the character at start is removed.

Examples

Return Value

TEXT or NTEXT

REMCOLS always returns a TEXT value that has the same number of lines as text-expression, though some lines may be empty.

Syntax

REMCOLS(text-expression start [length])

Parameters

text-expression

The text expression from which the specified columns should be removed. When text-expression is a multiline TEXT value, the characters in the specified columns are removed from each one of its lines.

When you specify a TEXT expression, the return value is TEXT. When you specify an NTEXT expression, the return value is NTEXT.

start

An INTEGER, between 1 and 32,767, representing the column position at which to begin removing columns. The column position of the first character in each line of text-expression is 1. When you specify a starting column that is to the right of the last character in a given line in text expression, the corresponding line in the return value is identical to the given line.

length

An INTEGER representing the number of columns to be removed. When you do not specify length, REMCOLS removes only the starting column. When you specify a length that exceeds the number of characters that follow the starting position in a given line in text expression, the corresponding line in the return value includes only the characters that precede the starting column.

Examples

Return Value

TEXT or NTEXT

Syntax

REMLINES(text-expression start [length])

Parameters

text-expression

A multiline text expression from whose values REMLINES removes one or more lines. When you specify a TEXT expression, the return value is TEXT. When you specify an NTEXT expression, the return value is NTEXT.

start

An INTEGER that represents the line number at which to begin removing lines. The position of the first line in text-expression is 1.

length

An INTEGER that represents the number of lines to be extracted. When you do not specify length, only the line at start is removed.

Examples

Return Value

The same data type as char.

Syntax

REPLACE ( char, search_string [, replacement_string ])

Parameters

char

A text expression that is the character that you want returned when a replacement is made.

search_string

A text expression that is the string you want to replace. When you specify NA, then the function returns char.

replacement_string

A text expression that is the string with which you want to replace search_string. When you do not specify a value for this argument or when you specify the value of NA, all occurrences of search_string are removed.

Return Value

TEXT

Syntax

REPLBYTES(text-expression replacement [start])

Parameters

text-expression

A TEXT expression in which REPLBYTES replaces bytes. When the bytes to replace from text-expression contain embedded line breaks, these breaks are removed. Other line breaks are preserved. Removed line breaks are not counted toward the total number of bytes replaced. Line breaks in the replacement expression are retained in the output of REPLBYTES, but are likewise not counted.

Tip:

When you must use this function on NTEXT values, use the CONVERT or TO_CHAR function to convert the NTEXT value to TEXT.

replacement

A text expression that contains one or more bytes that replaces existing bytes in text-expression.

start

An INTEGER that represents the byte position at which to begin replacing bytes. The position of the first byte in text-expression is 1. When you omit this argument, REPLBYTES starts with the first byte. REPLBYTES replaces as many bytes of text-expression as are required for the bytes specified by replacement. When the value of start is greater than the length of text-expression, REPLBYTES simply returns text-expression.

Examples

Return Value

TEXT or NTEXT

This function accepts TEXT values and NTEXT values as arguments. The data type of the return value depends on the data type of the values specified for the arguments:

• When all arguments are TEXT values, the return value is TEXT.

• When all arguments are NTEXT values, the return value is NTEXT.

• When the arguments include both TEXT and NTEXT values, the function converts all TEXT values to NTEXT before performing the function operation, and the return value is NTEXT.

Syntax

REPLCHARS(text-expression characters [start])

Parameters

text-expression

The expression in which characters are to be replaced. When the characters to be replaced from text-expression contain embedded line breaks, these breaks are removed. Other line breaks are preserved. Removed line breaks are not counted toward the total number of characters replaced. Line breaks in the replacement expression are retained in the output of REPLCHARS, but are likewise not counted.

characters

A text expression that contains one or more characters that replaces existing characters in text-expression.

start

An INTEGER that represents the character position at which to begin replacing characters. The position of the first character in text-expression is 1. When you omit this argument, REPLCHARS starts with the first character. REPLCHARS replaces as many characters of text-expression as are required for the specified new characters. When the value of start is greater than the length of text-expression, REPLCHARS simply returns text-expression.

Examples

REPLCHARS has replaced the first 6 characters of var1 (Hellot of HellothereJoe) with the 6 characters of var2 (HiJane). It has preserved the line breaks following Hi (from var2) and there (from var1).

To replace all 13 characters in var1, you must specify 13 replacement characters; for example, you can add 7 spaces after Jane.

var2 = JOINLINES('Hi' 'Jane       ')

The statement

SHOW REPLCHARS(var1 var2)

produces the following output.

Hi
Jane 

Return Value

TEXT or NTEXT

This function accepts TEXT values and NTEXT values as arguments. The data type of the return value depends on the data type of the values specified for the arguments:

• When all arguments are TEXT values, the return value is TEXT.

• When all arguments are NTEXT values, the return value is NTEXT.

• When the arguments include both TEXT and NTEXT values, the function converts all TEXT values to NTEXT before performing the function operation, and the return value is NTEXT.

The number of lines in the return value is always the same as the number of lines in text-expression. When the columns text expression has fewer lines, REPLCOLS repeats its last line in each subsequent line of the return value.

Syntax

REPLCOLS(text-expression columns [start])

Parameters

text-expression

The text expression in which you want to replace one or more columns.

columns

A text expression containing one or more lines. This expression provides the columns to replace some or all of the columns in text-expression.

start

An INTEGER, between 1 and 32,767, representing the column position at which to begin replacing. The column position of the first character in each line of text-expression is 1. When you do not specify start, replacement begins with Column 1. When you specify a starting column that is to the right of the last character in a given line in text-expression, the corresponding line in the return value has spaces filling in the intervening columns. See Example 8-80.

Examples

Return Value

TEXT or NTEXT

This function accepts TEXT values and NTEXT values as arguments. The data type of the return value depends on the data type of the values specified for the arguments. When all arguments are TEXT values, the return value is TEXT. When all arguments are NTEXT values, the return value is NTEXT. When the arguments include both TEXT and NTEXT values, the function converts all TEXT values to NTEXT before performing the function operation, and the return value is NTEXT.

Syntax

REPLLINES(text-expression lines [start])

Parameters

text-expression

A multiline text expression in which you want to replace one or more lines.

lines

A text expression that contains one or more lines that replace the existing lines in text-expression.

start

An INTEGER that represents the line number at which to begin replacing. The position of the first line in text-expression is 1. When you omit this argument, REPLLINES starts with line 1. REPLLINES replaces as many lines of text-expression as are required for the specified new lines.

Examples

Return Value

Either a multiline text expression or BOOLEAN, depending on whether or not you specify an argument to the function.

Syntax

RESERVED [(word-expression)]

Parameters

word-expression

A text expression that represents a word that may or may not be reserved in the OLAP DML. When you specify word-expression, the RESERVED function returns a BOOLEAN value indicating if the word is reserved in OLAP DML. When you do not specify an argument, RESERVED returns a TEXT value consisting of all the reserved words in OLAP DML, with each word on a separate line.

Usage Notes

Other Reserved Words

The RESERVED function only recognizes words known to the OLAP DML parser. The RESERVED function des not recognize the names of option objects and some other objects in the EXPRESS analytic workspace. The names of these objects are reserved in Oracle OLAP, but are ignored by the RESERVED function. To identify the names of these objects, issue the following statements.

AW ATTACH EXPRESS
LISTNAMES 

NA is Reserved

When you specify NA for the argument, the RESERVED function returns NO. When you specify NA, the RESERVED function returns YES.

Case-Sensitivity

The list of reserved words returned by the RESERVED function contains some words in all uppercase and some in mixed case. Words all in uppercase are reserved in their entirety. Words in mixed case can be abbreviated to the uppercase portion. For such words, any subset of the word containing the uppercase portion is reserved. For example, one word in the list returned by RESERVED is CODEVERsion. The following are all reserved: codever, codeversi, codeversio, and codeversion. However, codeve is not reserved.

Examples

Return Value

TEXT

Syntax

ROW([attribs] [ACROSS dimension [limit-clause]:] {exp1|SKIP} -      [[attribs] [ACROSS dimension [limit-clause]:] {expn|SKIP}])

See the ROW command for a complete description of the arguments.

The ROW function without any arguments returns a blank line.

Usage Notes

The notes for the ROW command also apply to the ROW function.

Examples

Return Value

TEXT

The result of this conversion is always in the national character set and is 18 characters long.

Syntax

ROWIDTOCHAR (rowid)

Parameters

rowid

The value of type ROWID to convert.

Examples

Return Value

NCHAR

The result of this conversion is always in the national character set and is 18 characters long.

Syntax

ROWIDTONCHAR (rowid)

Parameters

rowid

The value of type ROWID to convert.

Examples

See Example 8-91.

Return Value

TEXT or NTEXT based on the data type of the expression you want to pad (text-exp).

Syntax

RPAD (text-exp , length [, pad-exp])

Parameters

text-exp

A text expression to pad.

length

The total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multibyte character sets, the display length of a character string can differ from the number of characters in the string.

When you specify a value for length that is shorter than the length of text-exp, then this function truncates the expression to the specified length.

pad-exp

A text expression that specifies the padding characters. The default value of pad-exp is a single blank.

Examples

Return Value

TEXT or NTEXT based on the data type of the first argument.

Syntax

RTRIM (text-exp [,  trim-exp])

Parameters

text-exp

A text expression that you want trimmed.

trim-exp

A text expression that is the characters to trim. The default value of trim-exp is a single blank.

Examples

Return Value

DECIMAL

Syntax

RUNTOTAL(n)

Parameters

n

One of the 32 subtotals (1 to 32) that Oracle OLAP accumulates for the current column of a report. RUNTOTAL returns the value of this subtotal for the specified column, but does not reset the value of the subtotal to zero.

The numbers by which the 32 subtotals are referenced (1 to 32) have no intrinsic significance; all the subtotals are the same until you reference them.

Usage Notes

How RUNTOTAL Works

Unlike the SUBSTR function, RUNTOTAL does not reset the indicated subtotal to zero, nor does it add the value returned by RUNTOTAL to the indicated subtotal. However, the value returned by RUNTOTAL is added to the other 31 accumulating totals for the current column.

Accessing Data from Another Column

You can obtain a running total of an expression shown in another column of a report by adding that expression to RUNTOTAL. You can use the COALESCE function to refer to the values in the other column. For example, to show the sales for each month in the first data column of a row, and a cumulative total of sales in the second data column, you could use this statement.

ROW month sales COLVAL(-1) + RUNTOTAL(1)

Resetting Subtotals

When you use a ROW statement to produce a report, you can use a ZEROTOTAL statement to reset any subtotal of any column to zero. Typically, use ZEROTOTAL at the beginning of a report program to make sure all totals begin at zero. A REPORT statement automatically resets all subtotals to zero before producing output.

NA Values and RUNTOTAL

RUNTOTAL ignores NA values unless all values are NA. When all values are NA, the total is NA.

Examples

Return Values

A time zone offset (a character type in the format '[+|]TZH:TZM') or a time zone region name, depending on how the user specified the session time zone value in the most recent ALTER SESSION statement

Syntax

SESSIONTIMEZONE

Examples

Return Value

INTEGER. SIGN returns -1 when n<0, 0 (zero) when n=0, or 1 when n>0.

Syntax

SIGN (n)

Parameters

n

A numeric expression.

Examples

Return Value

NUMBER

The result returned by SIN is a value with the same dimensions as the specified expression.

Syntax

SIN(angle-expression)

Parameters

angle-expression

A numeric expression that contains an angle value, which is specified in radians.

Examples

Return Value

NUMBER

Syntax

SINH(expression)

Parameters

expression

A numeric expression that contains an angle value, which is specified in radians.

Examples

Return Value

The data type of the expression. It can be INTEGER, LONGINT, DECIMAL, or DATE.

Syntax

SMALLEST(expression [CACHE] [dimension...])

Parameters

expression

The expression whose smallest value is to be returned.

CACHE

Specifies slightly different internal behavior. Specify this keyword only when the original performance is extremely slow.

dimension

The name of a dimension of the result; or, the name of a relation between one dimension of expression and another dimension that you want as a dimension of the result.

By default, SMALLEST returns a single value. When you indicate one or more dimensions for the result, SMALLEST tests for values along the dimensions that are specified and returns an array of values. Each dimension must be either a dimension of expression or related to one of its dimensions.

Tip:

When you specify a dimension that is not an actual dimension of expression, but, instead, is dimension that is related to a dimension of expression and when there are multiple relations between the two dimensions, Oracle OLAP uses the default relation between the dimensions to perform the calculation. (See the RELATION command for more information on default relations.) When you do not want Oracle OLAP to use this default relation, specify the related dimension by specifying the name of a specify relation.

Usage Notes

NA Values and SMALLEST

SMALLEST is affected by the NASKIP option in the same manner as other aggregate functions. When NASKIP is set to YES (the default), SMALLEST ignores NA values and returns the smallest value or values that are not NA. When NASKIP is set to NO, SMALLEST returns NA when any value of the expression is NA. When all the values of the expression are NA, SMALLEST returns NA for either setting of NASKIP.

Examples

Return Value

DECIMAL

Syntax

SMOOTH(expression {SINGLE alpha|DOUBLE alpha beta m} [BASEDON dimension-list])

Parameters

expression

The numeric expression whose values are to be smoothed.

SINGLE

DOUBLE

The method to use in the exponential smoothing of the values in expression. The SINGLE method specifies single exponential smoothing and requires an alpha argument. The DOUBLE method specifies double exponential smoothing (also known as Holt's linear exponential smoothing) and requires an alpha argument, a beta argument, and an m argument.

alpha

A number in the range from 0 to 1 that smooths the difference between the observed data forecast and the last forecast. The higher the value, the more weight the most recent forecast has, so smoothing decreases as the smoothing factor increases. A smoothing factor of 0 completely smooths the forecasts and always returns the first forecast, which is the first data observation. A smoothing factor of 1 produces no smoothing at all and returns the previous data observation. (See "alpha ".)

beta

A number in the range from 0 to 1 that smooths the difference between the previous forecast and the current forecast. As with the alpha argument, smoothing decreases as the smoothing factor increases.

m

A positive INTEGER between 1 and the total number of periods of data in the data series. The m argument specifies the number of periods on which to base the forecasts.

BASEDON dimension-list

An optional list of one or more of the dimensions of expression to include in the exponential smoothing. When you do not specify the dimensions, then SMOOTH bases the smoothing on all of the dimensions of expression.

Usage Notes

The Effect of NASKIP on SMOOTH

SMOOTH is affected by the NASKIP option. When NASKIP is set to YES (the default), then SMOOTH ignores NA values. When NASKIP is set to NO, then SMOOTH returns NA for every forecast after the NA value.

Results of alpha Values

This note illustrates the results of using different alpha values for single exponential smoothing. The results are based on the sales variable with the dimensions limited by the following statements.

LIMIT month TO 'Jan96' TO 'Dec96'
LIMIT product TO 'Tents'
LIMIT district TO 'Boston'
REPORT DOWN month SMOOTH(sales, SINGLE, ALPHA, BASEDON month)

The following table shows the data values of the sales variable and also shows the results of the SMOOTH function in the preceding statement when the alpha argument variable has the different values shown in the table.