Return Value

DECIMAL.

The dimensionality of the result is the same as the specified expression.

Syntax

ABS(expression)

Parameters

expression

The expression whose absolute value is to be calculated.

Examples

Return Value

DATETIME

Syntax

ADD_MONTHS(start_datetime, n)

Parameters

start_datetime

A DATETIME expression that identifies the starting date. When the day component of start_datetime is the last day of the month or when the returned month has fewer days, then the returned day component is the last day of the month. Otherwise, the day component of the returned date is the same as the day component of start_datetime. See Example 7-2.

n

An INTEGER that identifies the number of months to be added to start_datetime.

Examples

Return Value

INTEGER

The values of the Aggcount variable that are the non-NA counts of the number of leaf nodes that contribute to the calculation of aggregate values when RELATION (for aggregation) statements that have an AVERAGE, HAVERAGE, WAVERAGE, or HWAVERAGE execute.

Syntax

AGGCOUNT(variable-name)

Parameters

variable-name

The name of the variable with which the Aggcount variable is associated.

Examples

Return Value

Varies depending on the type of information that is requested. See the following table for more information.

Syntax

AGGMAPINFO (name {choice | {choice-at-position rel-pos} })

Parameters

name

The name of the aggmap object.

choice

Specifies the type of information returned. See the following table for details.

choice-at-position

Specifies exactly which piece of information you want returned.

PRECOMPUTE returns the text of the limit clause that follows the PRECOMPUTE keyword in a RELATION statement. You must use the rel-pos argument to specify a single RELATION statement. Returns NA when the RELATION statement does not have a PRECOMPUTE keyword. (Applies to AGGMAP type aggmaps only.)

RELATION returns the name of the relation that follows the RELATION statement that you specify with the rel-pos argument.

STATUS returns the status list that results from the compilation of the PRECOMPUTE clause in the RELATION statement that you specify with the rel-pos argument. (Applies to AGGMAP type aggmaps only.)

rel-pos

An INTEGER that specifies a RELATION statement in the aggmap. The INTEGER indicates the position of the statement in the list of RELATION statements. You can use the rel-pos argument only with the RELATION, PRECOMPUTE, or STATUS keywords. For example, to get information about the first RELATION statement in an aggmap, use an INTEGER with a value of 1 as the rel-pos argument. To get information about the fourth RELATION statement in an aggmap, use the INTEGER 4, and so on. You may use any INTEGER between 1 and the total number of RELATION statements in an aggmap specification. You can use the NUMRELS keyword to obtain the total number of RELATION statements for an aggmap object.

Examples

Return Value

The same data type as the aggregated variable.

Syntax

AGGREGATE (var ... [USING aggmap] -      [FROM fromspec|FROMVAR textvar] [FORCECALC FORCEORDER] [COUNTVAR countvar])

Parameters

var

The name of the variable whose data is calculated (if necessary) and returned.

USING

This keyword indicates that the aggregation is performed using the specified aggmap.

aggmap

The name of a previously-defined aggmap that specifies how the data is aggregated. For information about aggmaps, see DEFINE AGGMAP.

FROM

This keyword indicates that the detail data is obtained from a different object. A FROM clause is only one way in which you can specify the variable from which detail data should be obtained when performing aggregation. See "Ways of Specifying Where to Obtain Detail Data for Aggregation".

fromspec

An arbitrarily dimensioned variable, formula, or relation from which the detail data for the aggregation is obtained.

FROMVAR

This keyword indicates that the detail data is obtained from different objects to perform a capstone aggregation. A FROMVAR clause is only one way in which you can specify the variable from which detail data should be obtained when performing aggregation. See "Ways of Specifying Where to Obtain Detail Data for Aggregation".

textvar

An arbitrarily dimensioned variable used to resolve any leaf nodes. Specify NA to indicate that a node does not need detail data to calculate the value.

FORCECALC

Specifies that any value that is not specified in a PRECOMPUTE clause of a RELATION statement that is in the aggmap should be recalculated, even when there is a value stored in the desired cell. Use the FORCECALC keyword when you want users to be able to change detail data cells and see the changed values reflected in dynamically-computed aggregate cells.

Note:

You can also set an $AGGREGATE_FORCECALC property on a variable to specify this behavior as the default aggregation behavior. In this case, you do not have to include the FORCECALC keyword with the AGGREGATE function.

FORCEORDER

Specifies that the calculation must be performed in the order in which the RELATION statements are listed in the aggmap. Use this option when you have changed some values calculated by the AGGREGATE command. Otherwise, the optimization methods used by the AGGREGATE function may cause the modified values to be ignored. FORCEORDER slows performance.

Note:

You can also set an $AGGREGATE_FORCEORDER property on a variable to specify this behavior as the default aggregation behavior. In this case, you do not have to include the FORCEORDER keyword with the AGGREGATE function.

COUNTVAR countvar

Indicates that Oracle OLAP should use the user-defined variable specified by countvar to store the non-NA counts of the number of leaf nodes that contributed to aggregate values calculated for RELATION (for aggregation) statements that have an AVERAGE, HAVERAGE, HWAVERAGE, or WAVERAGE operator.

Note:

Typically, you do not use a user-defined Countvar variable to store the counts for average aggregations. Instead, you use an Oracle OLAP-created Aggcount variable. You must use an Aggcount variable when the aggregation specification includes a RELATION (for aggregation) statement with an average operator that is for a compressed composite.

For more information on Aggcount variables, see "Aggcount Variables".

The countvar variable must be an INTEGER variable with the same dimensions in the same order as the dimensions of the variable specified by var. When you aggregate several variables together, you must define an INTEGER variable for each one to record the results.

Usage Notes

Steps for Supporting Run-Time Calculations

Follow these steps when combining pre-aggregation with run-time aggregation:

1. Create an aggmap that limits the amount of data to be precalculated.

2. Execute the AGGREGATE command with the FUNCDATA argument.

3. When you have made any changes after executing the AGGREGATE command (see "Compiling the Aggmap"), recompile the aggmap with a COMPILE statement.

4. Add an $AGGREGATE_FROM property to the data variables (see "Using NA Values to Trigger Run-Time Calculations").

5. UPDATE and COMMIT the analytic workspace.

Compiling the Aggmap

Be sure to compile the aggmap at the time you load data, either with an explicit COMPILE statement or with the FUNCDATA argument to the AGGREGATE command. Otherwise, the aggmap is recompiled at run time for each session in which the AGGREGATE function is used. Perform other calculations (such as calculating models) before you compile the aggmap.

You must recompile the aggmap after maintaining any of the dimensions in the aggmap definition or any of the relations that are included in the text of the aggmap.

Run-Time Changes to Data Values

When users are able to change data values at run time, then the data may get out of synchronization. You can prevent this problem in the following ways:

• Use an ALLOCATE statement to distribute the data in a new aggregate to the contributing values lower in the hierarchy.

• Do not precalculate the data that is subject to run-time changes because the stored aggregates cannot be altered to reflect changes made at run time to the contributing values.

Using NA Values to Trigger Run-Time Calculations

By adding an $NATRIGGER property to a variable, you can implicitly call the AGGREGATE function each time the data is queried. The following statements cause sales data to be aggregated using the sales.aggmap aggmap.

CONSIDER sales
PROPERTY '$NATRIGGER' 'AGGREGATE(sales USING sales.aggmap)'

From now on, a statement such as REPORT SALES executes the AGGREGATE function, so that computed values are returned instead of NAs.

Using the AGGREGATE Function after Partial Rollups

When your batch window is not sufficiently long to preaggregate all of the data to generate, you can perform the aggregation in stages on consecutive days and use the AGGREGATE function to calculate the balance. For each stage, you must do the following:

1. Change the PRECOMPUTE phrase of the RELATION statement in the aggmap so that new data is aggregated.

2. Execute the AGGREGATE command with the FUNCDATA keyword.

3. Verify that the $NATRIGGER property is set on the variables so that the AGGREGATE function calculates the balance of the data.

Using Multiple Aggmaps

Whenever possible, use only one aggmap to rollup a variable. However, in some situations, a variable requires multiple aggmaps to roll up the data in the desired manner. When a variable requires multiple aggmaps to rollup data problems are created when some data is calculated on the fly, because the metadata retained for the AGGREGATE function corresponds to the last aggmap. The AGGREGATE function needs metadata that is the union of all of the aggmaps used by the AGGREGATE command. The solution is to create an additional aggmap for use by the AGGREGATE function that correctly identifies the NA values. Be sure to compile this aggmap.

Do not use the AGGREGATE function with multiple aggmaps unless you feel comfortable answering the following question:

• When the aggmap is compiled for use by the AGGREGATE function, does the status that results from each PRECOMPUTE clause accurately define the nodes within that dimension at which data has been pre-computed?

When you cannot answer "yes" to this question with confidence, do not use the AGGREGATE function with multiple aggmaps.

Examples

This section contains several examples of using the AGGREGATE function. For additional aggregation examples, see the examples for the AGGMAP command.

Syntax

AGGREGATION(dimval-list)

Parameters

dimval-list

A list of one or more dimension values to include in the custom aggregation. The specified values must belong to the same dimension to which the target dimension value belongs. You must specify each dimension value as a text literal. That is, they cannot be represented by a text expression such as a variable.

Examples

Return Value

TEXT

Syntax

AGGROPS

Example

Return Value

TEXT

Syntax

ALLOCOPS

Examples

Return Value

DECIMAL

Syntax

ANTILOG(n)

Parameters

n

The power of e to be returned by the ANTILOG function.

Examples

Return Value

DECIMAL

Syntax

ANTILOG10(n)

Parameters

n

The power of 10 to be returned by the ANTILOG10 function.

 

Examples

Return Value

BOOLEAN.

Syntax

ANY(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, ANY returns a single YES or NO value. When you indicate one or more dimensions for the result, ANY 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 ANY

ANY is affected by the NASKIP option. When NASKIP is set to YES (the default), ANY ignores NA values and returns YES when any of the values of the expression that are not NA are TRUE and returns NO when none of the values are TRUE. When NASKIP is set to NO, ANY returns NA when any value of the expression is NA. When all the values of the expression are NA, ANY returns NA for either setting of NASKIP.

Data with a Type of DAY, WEEK, MONTH, QUARTER, or YEAR

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

For each time period in the related dimension, Oracle OLAP tests the data values for all the source time periods that end in the target time period. This method is used regardless of which dimension has the more aggregate time periods.

Examples

Return Value

NUMBER

Syntax

ARCCOS(expression)

Parameters

expression

An expression that contains the decimal value of a cosine.

Usage Notes

Invalid Cosine Values

When you provide an ineligible value for the cosine expression (that is, a value greater than 1 or less than -1), ARCCOS returns a value of NA.

Examples

Return Value

NUMBER

Syntax

ARCSIN(expression)

Parameters

expression

An expression that contains the decimal value of a sine.

Usage Notes

Invalid Sine Values

When you provide an ineligible value for the sine expression (that is, a value greater than 1 or less than -1), ARCSIN returns a value of NA.

Examples

Return Value

NUMBER

Syntax

ARCTAN(expression)

Parameters

expression

An expression that contains the decimal value of a tangent.

Examples

Return Value

NUMBER

Syntax

ARCTAN2 (n  / m)

Parameters

n

A numeric expression that specifies one component of the ratio. The argument n can be in an unbounded range.

m

A numeric expression that specifies the other component of the ratio.

Examples

Return Value

TEXT

Syntax

ARG(n)

Parameters

n

The number by position of the argument whose value you want to reference. ARG(1) returns the first argument to the program, ARG(2) returns the second argument, and so forth. When the program is called with fewer than n arguments, ARG returns a null value. ARG also returns a null value when n is zero or negative.

Examples

Return Value

INTEGER

Syntax

ARGCOUNT

Examples

Return Value

TEXT

Syntax

ARGFR(n)

Parameters

n

The number by position of the first argument in the group of arguments you want to reference. ARGFR(1) returns the first argument and all subsequent arguments, ARGFR(2) returns the second argument and all subsequent arguments, and so forth. When there are fewer than n arguments, ARGFR returns a null value. ARGFR also returns a null value when n is 0 (zero) or negative.

Examples

Return Value

TEXT

When no arguments have been specified for the program, ARGS returns a null value

Syntax

ARGS

Examples

Return Value

INTEGER

Syntax

ASCII (text-exp)

Parameters

text-exp

A text expression.

Usage Notes

Returning EBCDIC Values

When your database character set is 7-bit ASCII, then this function returns an ASCII value. When your database character set is EBCDIC Code, then this function returns an EBCDIC value. There is no corresponding EBCDIC character function

Examples

Returns

NTEXT

Syntax

ASCIISTR(text-exp)

Parameters

text-exp

A text expression.

Usage Notes

How ASCIISTR Converts Non-ASCII Characters

The ASCIISTR function converts non-ASCII characters to \xxxx, where xxxx represents a UTF-16 code unit.

Return Value

DECIMAL

Syntax

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

Parameters

expression

The expression whose values are to be averaged.

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, AVERAGE returns a single value. When you indicate one or more dimensions for the result, AVERAGE 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 AVERAGE

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

Averaging Over a Dimension of Type DAY, WEEK, MONTH, QUARTER, or YEAR

When expression is dimensioned by a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, you can specify any other dimension that has one of these types as a related dimension. Oracle OLAP uses the implicit relation between the two dimensions. To control the mapping of one of these types of dimensions 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 AVERAGE function.

For each time period in the related dimension, Oracle OLAP averages the data for all the source time periods that end in the target time period. This method is used regardless of which dimension has the more aggregate time periods. To control the way in which data is aggregated or allocated between the periods of two dimensions, you can use the TCONVERT function.

Examples

Return Value

The return value depends on the keyword you specify, as described in the following table.

Syntax

AW(keyword [workspace])

Parameters

keyword

Indicates the specific information you want. The keywords that you can use with the AW function are listed in the following table with the data type of the value they return and the meaning of the information.

Table 7-2 Keywords for AW Function

Keyword	Data Type	Information Returned
ACQUIRED	TEXT	When an analytic workspace is attached in multiwriter mode, returns the names of any acquired variables, relations, valuesets, dimension names, or partitions, in the analytic workspace
AGGMAP	TEXT	A list of all aggmap objects in the workspace. When there are several, Oracle OLAP returns a multiline text value with each object name on a separate line.
ALIASLIST	TEXT	A list of currently assigned aliases for the workspace. When there are several, Oracle OLAP returns a multiline text value with each alias on a separate line.
ATTACHED	BOOLEAN	Indicates whether the specified workspace is attached. The workspace argument is required.
CHANGED	BOOLEAN	When you have read/write access to the workspace, indicates whether you have made changes since the last time the workspace was updated. When you have read-only access to the workspace, indicates whether another user has updated the workspace and committed the changes since you attached it.
COMPOSITE	TEXT	A list of all named composite objects in the specified workspace.
DATE	DATE	The date of your most recent update in the current session. When you have not updated in the current session, it returns the date of the most recent commit before you attached the workspace. When you have attached a shared workspace as read-only, DATE does not take into account any updates or commits that have occurred since you attached the workspace.
DIMENSION	TEXT	A list of all the dimensions defined in the workspace. When there are several dimensions, Oracle OLAP returns a multiline text value with each dimension name on a separate line.
EXISTS	BOOLEAN	Indicates whether the specified analytic workspace has been defined in the Oracle Database instance.
FORMULA	TEXT	A list of all the formulas defined in the workspace. When there are several formulas, Oracle OLAP returns a multiline text value with each formula name on a separate line.
FROZEN	Boolean	TRUE if the specified analytic workspace is currently frozen, or FALSE if it is not.
FULLNAME	TEXT	The full name of the specified workspace. The full name includes the schema that contains the workspace.
ISUPDATED	TEXT	When the specified analytic workspace is not attached in multiwriter mode, returns TRUE when the workspace is updated but not committed. When he specified analytic workspace is attached in multiwriter mode, returns TRUE when at least one variable or dimension belonging to the workspace is updated but not committed.
LIST	TEXT	A list of all currently attached workspaces. Each line of the multiline text value contains the name of an analytic workspace.
LISTNAMES	TEXT	A list of all the objects defined in the workspace. Each line of the multiline text value contains the name of an analytic workspace object.
MODEL	TEXT	A list of all the models defined in the workspace. When there are several models, Oracle OLAP returns a multiline text value with each model name on a separate line.
MULTI	TEXT	Indicates if you have multi-writer access to the analytic workspace.
NAME	TEXT	The name of the current workspace.
OPTION	TEXT	A list of all the Oracle OLAP options defined in the EXPRESS workspace. When the workspace is not EXPRESS, AW(OPTION) returns NA, because options are defined only in the EXPRESS workspace. For the EXPRESS workspace, AW(OPTION) returns a multiline text value with each option name on a separate line.
PAGESIZE	INTEGER	The size of the page, in bytes.
PROGRAM	TEXT	A list of all the programs defined in the workspace. When there are several programs, Oracle OLAP returns a multiline text value with each program name on a separate line.
READERS	INTEGER	The total number of current users of the database who have read-only access.
RELATION	TEXT	A list of all the relations defined in the workspace. When there are several relations, Oracle OLAP returns a multiline text value with each relation name on a separate line
RO	TEXT	Indicates whether you have read-only access to the workspace.
RW	TEXT	Indicates whether you have read/write access to the workspace.
SEGMENTSIZE	DECIMAL	The current maximum segment size for the workspace. It is the most recent value specified using an AW SEGMENTSIZE statement.
SHARED	BOOLEAN	Indicates whether the workspace is being shared by other users.
TIME	ID	The time of your most recent update in the current session. When you have not updated in the current session, it returns the time of the most recent commit before you attached the workspace. When you have attached a shared workspace as read-only, TIME does not take into account any updates or commits that have occurred since you attached the workspace.
VALUSET	TEXT	A list of all the valuesets that are defined in the workspace. When there are several valuesets, Oracle OLAP returns a multiline text value with each valueset name on a separate line.
VARIABLE	TEXT	A list of all the variables defined in the workspace. When there are several variables, Oracle OLAP returns a multiline text value with each variable name on a separate line.
WRITERS	INTEGER	The number of current users of the database who have write access.

workspace

A text expression that indicates the name of the workspace for which you want information. When you do not specify this argument, the AW function ordinarily returns information about the current workspace. The ATTACHED, LIST, and NAME keywords are exceptions to this rule.

Usage Notes

Analytic Workspace Status Information

You can use the SHARED, CHANGED, RO, and RW keywords to get information about the current status of a shared workspace. You can check if SHARED, RO, and CHANGED are TRUE to find out if another user has updated an analytic workspace since you attached it.

Examples

Return Value

TEXT

Syntax

BACK

Examples

Return Value

TEXT

Syntax

BASEDIM(concatdim [LEAF])

Parameters

concatdim

Specifies the concat dimension for which you want the names of the base or component dimensions. The data type of the values returned is TEXT.

LEAF

The LEAF keyword causes BASEDIM to return the names of the component dimensions of the concatdim dimension. The base dimensions of a concat dimension are the simple, conjoint, or other concat dimensions that you specify with the basedimlist argument when you define the concat. Simple dimensions and conjoint dimensions are the bottom-level components, or leaves, of a concat dimension.

When you specify a concat dimension as a base dimension when defining a concat, then the base dimensions of that inner concat are component dimensions of the outer concat. Using the LEAF keyword results in BASEDIM returning the names of the component simple and conjoint dimensions of the inner concat dimension.

When the base dimensions are all simple dimensions or conjoint dimensions, then the base dimensions are the bottom-level components and therefore BASEDIM returns the names of those dimensions whether or not you use the LEAF keyword.

When the base dimensions are all simple dimensions or conjoint dimensions, then the base dimensions are the bottom-level components and therefore BASEDIM returns the names of those dimensions whether or not you use the LEAF keyword.

Examples

Return Value

The following are the rules that determine the data types of the values returned by BASEVAL:

• The data type of the return value is NTEXT when any of the component dimensions of concatdim is of type NTEXT, or when any component dimension is a conjoint that uses a simple dimension of type NTEXT.

• The data type of the return value is the data type of the component dimensions when all of the component dimensions have the same data type and when none of the component dimensions is a conjoint.

• The data type of the return value is TEXT in all other cases.

Syntax

BASEVAL(concatdim)

Parameters

concatdim

Specifies the concat dimension for which you want the base values. The data types of the values returned depend on the data types of the base dimensions of the concat dimension.

Examples

Return Value

DATE-only or text

When all the values of the expression are NA, BEGINDATE returns NA.

Syntax

BEGINDATE(expression)

Parameters

expression

The expression must have exactly one dimension that has a type of DAY, WEEK, MONTH, QUARTER, or YEAR.

Examples

Return Values

NUMBER

Syntax

BIN_TO_NUM(expression [, expression ]... )

Parameters

expression

An expression that evaluates to either 0 (zero) or 1 (one) which is the value of a bit in the bit vector.

Examples

Return Value

INTEGER

Syntax

BITAND (argument1 , argument2)

Parameters

argument1

A nonnegative INTEGER expression.

argument2

A nonnegative INTEGER expression.

Examples

See Example 7-65.

Return Value

TEXT or NTEXT

Syntax

BLANKSTRIP(text-expression [TRAILING|LEADING|BOTH])

Parameters

text-expression

A text expression from which to remove blank spaces. When you specify a TEXT expression, the return value is TEXT. When you specify an NTEXT expression, the return value is NTEXT.

TRAILING

Removes blank spaces at the end of the text.

LEADING

Removes blank spaces at the beginning of the text.

BOTH

Removes both leading and trailing spaces.

Examples

Return Value

TEXT

The return value of CALLTYPE is:

• FUNCTION when the program was invoked as a function that returns a value.

• COMMAND when the program was invoked as a command.

• CALL when the program was invoked using a CALL statement.

• TRIGGER when the program is a trigger program (that is, when a TRIGGER command associated the program with an object event) was invoked in response to an OLAP DML statement.

Syntax

CALLTYPE

Examples

Return Value

DECIMAL

Syntax

CATEGORIZE(expression {values|group-expression})

where values has the following syntax:

     bottom-value [next-lowest-break-value] top-value

Parameters

expression

The numeric expression whose values are to be categorized.

bottom-value

A number that specifies the lowest number in the series and sets the bottom limit of category 1.

next-lowest-break-value

A number that specifies the beginning of the range of the next category.

top-value

A number that specifies the highest number in the series and sets the upper limit of the highest category.

group-expression

A one-dimensional numeric expression that defines the break values for the categories.

Examples

Return Value

NUMBER

Syntax

CEIL(n)

Parameters

n

A number (NUMBER data type) that you specify.

Examples

Return Value

TEXT

Syntax

CHANGEBYTES(text-expression oldtext newtext [number])

Parameters

text-expression

A TEXT expression in which bytes are to be changed. When text-expression is a multiline TEXT expression, CHANGEBYTES preserves the line breaks in the returned value.

oldtext

A TEXT expression that contains one or more bytes that to be changed.

newtext

A TEXT expression that contains one or more bytes that to replace oldtext.

number

An INTEGER that represents the number of times oldtext should be replaced with newtext when oldtext appears more than once in text-expression. The default is to change all occurrences of oldtext.

Examples

Return Value

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

CHANGECHARS(text-expression oldtext newtext [number] [UPCASE])

Parameters

text-expression

The TEXT or NTEXT expression in which characters are to be changed. When text-expression is a multiline text value, CHANGECHARS preserves the line breaks in the returned value.

oldtext

A TEXT or NTEXT expression that contains one or more characters to be changed.

newtext

A TEXT or NTEXT expression that contains one or more characters to replace oldtext.

number

An INTEGER that represents the number of times oldtext should be replaced with newtext when oldtext appears more than once in text-expression. The default is to change all occurrences of oldtext.

UPCASE

Specifies that CHANGECHARS should uppercase text-expression and oldtext before trying to find a match. CHANGECHARS does not uppercase the return value.

Examples

Return Value

BOOLEAN.

TRUE when changes have occurred, FALSE when they have not, or NA when the function cannot determine if changes have occurred.

Syntax

CHANGEDRELATIONS( variable [ [(PARTITION partition [,PARTITION partition]...) ] aggmap] )

Parameters

variable

The name of the variable whose aggmap object you want to check for changes.

partition

The name of one or more partitions of variable, separated by commas, whose aggmap you want to check for changes.

aggmap

The name of the aggmap object you want to check for changes. When you do not specify a value for aggmap, the function uses the aggmap specified in the $AGGMAP property for variable, if any.

Return Value

BOOLEAN unless you specify NUMBER for returntype.

When the function returns a BOOLEAN value, that value is TRUE when any value has changed since the variable was last aggregated, FALSE when no values have changed, or NA when the function cannot determine if any values have changed or not.

When the function returns a NUMBER value, that value is the number of values that have changed since the variable was last aggregated.

Syntax

CHANGEDVALUES ( variable [(PARTITION partition [,PARTITION partition]...)] [returntype] )

Parameters

variable

The name of the variable to check for changed values.

partition

The name of one or more partitions of variable, separated by commas, to check for changed values.

returntype

NUMBER when you want the function to return a numeric value that is the number of values that have changed. When you want the function to return whether or not any value has changed since the last aggregation, specify BOOLEAN or leave this argument empty as BOOLEAN is the default value for returntype.

Return Value

NTEXT when the expression is NTEXT; otherwise, TEXT.

Syntax

CHARLIST(expression [dimensions])

Parameters

expression

The expression to be transformed into a multiline text value. When the expression has a data type other than TEXT or NTEXT, CHARLIST automatically converts the expression to TEXT.

dimensions

The dimensions of the return value. When you do not specify a dimension, CHARLIST returns a single value. When you provide one or more dimensions for the return value, CHARLIST returns a multiline text value for each value in the current status list of the specified dimension. Each dimension must be an actual dimension of the expression; it cannot be a related or base dimension.

Examples

Return Value

ROWID

Syntax

CHARTOROWID(char)

Parameters

char

A text expression to convert.

Examples

Return Value

Data type of the original expression.

Syntax

CHGDIMS (expression, limit-type)

where limit-type is one of the following:

• [CACHE] LIMITSAVE valueset-list
• [CACHE] LIMIT valueset-list
• TO dimension-list
• ADD dimension-list

Parameters

expression

The expression you want to modify.

CACHE

Specifies that Oracle OLAP caches the result of the limit and saves it for use in subsequent executions of CHGDIMS until the OLAP DML statement that called CHGDIMS finishes execution.

LIMITSAVE

Specifies that Oracle OLAP sets the value of dimension status for expression to the position before the CHGDIMS command executed (that is, specifying LIMITSAVE does not change the current dimension status value). For example, you specify CHGDIMS with LIMITSAVE if expression is the LAG function so that the lag is from the current value; or if you are coding CHGDIMS inside of an outer loop, like a SQL SELECT statement, and you want to keep the dimension status value set by the outer loop.

LIMIT

Specifies the Oracle OLAP sets the value of dimension status for expression to the first position in the new status before evaluating expression in much the same way as if a LIMIT TO command was issued just before evaluating expression.

valueset-list

The name of a valueset or a LIMIT function.

TO dimension-list

Specifies that Oracle OLAP evaluate expression as though the dimensions of expression are the dimensions specified by dimension-list.

ADD dimension-list

Specifies that Oracle OLAP evaluateexpression as though the dimensions of expression are the dimensions of expression plus the dimensions specified by dimension-list

Examples

Assume that you have the following objects in your analytic workspace.

DEFINE PRODUCT DIMENSION TEXT
DEFINE GEOG DIMENSION TEXT
DEFINE SALES VARIABLE INTEGER <PRODUCT GEOG>
 

Assume, also, that the sales variable has the following values.

               -------------------SALES-------------------
               ------------------PRODUCT------------------
GEOG            Trousers    Skirts    Dresses     Shoes
-------------- ---------- ---------- ---------- ----------
USA                    13         20         32         18
Canada                 17         32         15         28
 

The following lines of code show how the value returned by a TOTAL(sales) expression varies depending on how you qualify that expression.

"Total over all dims with standard status
SHOW TOTAL(sales)
175
 
"Total over all dims using new status for product
SHOW CHGDIMS(TOTAL(sales) LIMIT LIMIT(product TO FIRST 2))
82
 
"Total just over product
SHOW TOTAL(CHGDIMS(sales TO product))
83

Return Value

A text value. For single-byte character sets, if number > 256, the function returns the binary equivalent of number MOD 256. For multibyte character sets, number must resolve to one entire code point. Invalid code points are not validated, and the result of specifying invalid code points is indeterminate.

Syntax

CHR(number [ USING NCHAR_CS ])

Parameters

number

An integer value, or any value that can be implicitly converted to an integer value.

See Also:

"Automatic Conversion of Numeric Data Types"

USING NCHAR_CS

Specifies that the function returns the value in the national character set. When you do not specify this clause, the function returns the value in the database character set.

Examples

Return Value

Data type of the first argument.

Syntax

COALESCE (expr [, expr]...)

Parameters

expr

An expression.

Examples

Return Value

DECIMAL when the selected column contains numeric or Boolean data; NA when the column (n) contains only a TEXT or ID value; or an error when the specified column is the current column, a column to the right of the current column, or a nonexistent column

Syntax

COLVAL(n)

Parameters

n

The number of the column in the current row whose value you want; n can be any INTEGER expression.

Use a positive number to identify an absolute column number (counting left to right from the left margin of the report). In figuring an absolute column number, you must count all columns shown in the report. For example, when you are using a REPORT command that produces a column of labels down the left side of the report, you count this column of labels as column 1.For example, COLVAL(2) identifies the second column from the left margin of the report.

Use a negative number to identify a relative column number (counting right to left from the current column). For example, COLVAL(-2) identifies the column that is two columns to the left of the current column.

Examples

Return Value

The data type of the return value of the CONTEXT function depends on the arguments you provide. When you use the CONTEXT function without supplying any arguments, it returns a multiline text value that contains the names of all the contexts in the current session.

Syntax

CONTEXT ([context-name [UPDATE|name]])

Parameters

context-name

A text expression that contains the name of the context. Using the CONTEXT function with only the context-name returns a multiline text value that contains the names of all the objects saved in that context.

UPDATE

When you specify UPDATE with the CONTEXT function, the return value is the number of times values have been saved or dropped from the context.

name

The name of an object whose value is saved in the context. When you specify name with the CONTEXT function, the return value is the saved status or value of that object.

Examples

Return Value

The return value depends on the value of the type argument.

Syntax

CONVERT(expression, type [argument...])

Parameters

expression

The expression or variable to be converted.

type

The type of data to which you want to convert expression. The keywords that represent the types are described in the following table:

Table 7-3 Keywords for the type Parameter of the CONVERT Function

Keyword	Description
BINARY	Does not indicate conversion to a standard Oracle data type but allows additional conversion capabilities. BINARY does no conversion. The internal representation of every value, regardless of data type, is returned as a text value. For TEXT data types, the result is the value itself and is, therefore, of variable length. For ID and DECIMAL data types, the result is 8 bytes long; ID values is blank filled, when necessary. For BOOLEAN or INTEGER, the default result is 2 or 4 bytes long respectively (see the arguments explanation for an additional argument that lets you vary the width slightly). For all other data types, the result is 4 bytes long. See "PACKED and BINARY Conversion".
BOOLEAN	Conversion to Oracle OLAP BOOLEAN data type.
BYTE	Converts a single character into an ASCII INTEGER value in the range 0 to 255. Or BYTE converts an INTEGER within this range into a character. An INTEGER outside this range is taken modulo 256 and then converted; that is, 256 is subtracted from the INTEGER until the remainder is less than 256, and that within-range remainder is then converted into a character.
DATE	Conversion to Oracle OLAP DATE data type.
DATETIME	Conversion to Oracle OLAP DATETIME data type.
DECIMAL	Conversion to Oracle OLAP DECIMAL data type.
DSINTERVAL	Conversion to Oracle OLAP DML DSINTERVAL data type.
ID	Conversion to Oracle OLAP ID data type.
INFILE	Encloses an ID, TEXT, DATE, or RELATION value within single quotes, so that it can be read with an INFILE statement. Consequently, expression must have ID, TEXT, DATE, or RELATION value values. In the case of TEXT values with no alphanumeric equivalent, INFILE converts them to the correct escape sequences.
INTEGER	Conversion to Oracle OLAP INTEGER data type.
LONGINTEGER	Conversion to Oracle OLAP LONGINTEGER data type.
NTEXT	Conversion to standard Oracle OLAP data types. Corresponds to the NCHAR and NVARCHAR2 SQL data types. An NTEXT character is encoded in UTF8 Unicode. This encoding might be different from the NCHAR character set of the database, which can be UTF16. A conversion from NTEXT to TEXT can result in data loss when the NTEXT value cannot be represented in the database character set.
NUMBER [(p,[s])]	Conversion to Oracle OLAP NUMBER data type.
PACKED	Converts a number to a decimal value and then to packed format -- a text value 8 bytes long containing 15 digits and a plus or minus sign. Fractions cannot be represented in packed numbers; therefore the conversion process rounds decimal numbers to the nearest INTEGER. See "PACKED and BINARY Conversion".
ROWID	Converts a text value to a ROWID value.
SHORTDECIMAL	Conversion to Oracle OLAP SHORTDECIMAL data type.
SHORTINTEGER	Conversion to Oracle OLAP SHORTINTEGER data type.
TEXT	Conversion to standard Oracle OLAP data types. Corresponds to CHAR and VARCHAR2 data types in SQL. A TEXT character is encoded in the database character set.
TIMESTAMP	Conversion to Oracle OLAP DML TIMESTAMP data type.
TIMESTAMP_LTZ	Conversion to Oracle OLAP DML TIMESTAMP_LTZ data type.
TIMESTAMP_TZ	Conversion to Oracle OLAP DML TIMESTAMP_TZ data type.
UROWID	Converts a text value to a UROWID value.
YMINTERVAL	Conversion to Oracle OLAP DML YMINTERVAL data type.

argument

When you specify TEXT, NTEXT, ID, DATE, or INFILE for the type, you can specify additional arguments to determine how the conversion should be done as outlined in the following table:

Table 7-4 Syntax for Specifying Conversion to TEXT, NTEXT, ID, DATE, and INFILE

Keyword for type argument	When Converting From	Syntax for All Parameters
TEXT	Any numeric	TEXT [decimal-int|DECIMALS [comma-bool|COMMAS [paren-bool|PARENS]]]
NTEXT	Any numeric	NTEXT [decimal-int|DECIMALS [comma-bool|COMMAS [paren-bool|PARENS]]]
ID	Any numeric	ID [decimal-int|DECIMALS]
TEXT, NTEXT, or ID	Any datetime	ID|TEXT|NTEXT ['date_format']
TEXT, NTEXT, or ID	DATE	ID|TEXT|NTEXT ['dateformat']
ID or TEXT for a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR with VNF	DATE	ID [dwmqy-dimension]|TEXT [dwmqy-dimension|'vnf ']
DATE	TEXT, NTEXT, or ID	DATE [date-order|dwmqy-dimname]
NTEXT	TEXT	NOXLATE
TEXT	NTEXT	NOXLATE
INFILE		INFILE [width-exp|LSIZE [escape-int|0]]
IBINARY with BOOLEAN or INTEGER		BINARY [width-exp]

decimal-int

An INTEGER expression that controls the number of decimal places to be used when converting numeric data to TEXT or ID values. When this argument is omitted, CONVERT uses the current value of the DECIMALS option (the default is 2).

comma-bool

A Boolean expression that determines whether commas are used to mark thousands and millions in the text representation of the numeric data. When the value of the expression is YES, commas are used. When this argument is omitted, CONVERT uses the current value of the COMMAS option (the default is YES).

paren-bool

A Boolean expression that determines whether negative values are enclosed in parentheses in the text representation of the numeric data. When the value of the expression is YES, parentheses are used; when the value is NO, a minus sign precedes negative values. When this argument is omitted, CONVERT uses the current value of the PARENS option (the default is NO).

date_format

A text expression that specifies the template to use when converting a datetime expression to text. The valid formats for each date field are the same as the formats that you can specify using the DATE_FORMAT command.

When you do not include the date_format argument, the format of the result is determined by the default date format for the session as described in "Default Datetime Format Template".

dateformat

A text expression that specifies the template to use when converting a DATE-only expression to text. The template can include format specifications for any of the four components of a date (day, month, year, and day of the week). Each component in the template must be preceded by a left angle bracket (<)and followed by a right angle bracket (>). You can include additional text before, after, or between the components.

The valid formats for each date component are the same as the formats allowed in the DATEFORMAT option.

In the following statement, CONVERT returns today's date as a text value that is formatted by a dateformat argument.

SHOW CONVERT(TODAY TEXT '<MM>-<DD>-<YY>')

In this example, today's date is March 31, 1998, and the SHOW statement presents it in the following format.

03-31-98

When you do not include the dateformat argument, the format of the result is determined by the current setting of the DATEFORMAT option.

dwmqy-dimension

The name of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. Oracle OLAP uses the VNF of dwmqy-dimension when converting a DATE-only value to a TEXT or an ID value. When you have not specified the VNF of dwmqy-dimension, Oracle OLAP uses its default VNF.

In the following statement, CONVERT returns today's date as a text value that is formatted by the VNF of the YEAR dimension.

show convert(today text year)

In this example, today's date is March 31, 1998, and the SHOW statement presents it in the following format.

YR98

vnf

A text template that specifies the value name format to use when converting values of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR to text. The template can include format specifications for any of the components of a time period. Time period components include all the components of a date (day, month, year, and day of the week), plus the fiscal year and period components. The template can also include the name of the DAY, WEEK, MONTH, QUARTER, or YEAR dimension as a component. Each component in the template must be preceded by a left angle bracket and followed by a right angle bracket. You can include additional text before, after, or between the components.

The vnf argument to the CONVERT function is similar to the template in a VNF command. However, a VNF command template must be designed for precise and unambiguous interpretation of input, while the vnf argument is not so constrained. Therefore, the format styles allowed in the vnf argument are more extensive than those allowed in a VNF command template.

Valid format styles for a vnf argument include all the format styles allowed in the template of a VNF command, plus all the format styles allowed in a DATEFORMAT template. DATEFORMAT provides the following format styles that are not allowed in VNF command templates but that are valid in the vnf argument to the CONVERT function:

• Ordinal styles for the day of the month (DT and DTL)

• First-letter style for the month (MT)

• Styles for the day of the week (W, WT, WTXT, WTXTL, WTEXT, and WTEXTL)

Append a B code to any of these formats to indicate that you want to display the beginning day or month of the period, rather than the final day or month.

You can use any combination of VNF and DATEFORMAT format styles with for any dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. This syntax contrasts with the template in a VNF command, in which only certain format combinations are valid for each type of dimension.

In the following statement, CONVERT returns the current value of the MONTH dimension as a text value that is formatted by a vnf argument.

SHOW CONVERT(month TEXT '<MTEXTL>, <YYYY>')

In this example, the first MONTH value in status is DEC97, and the SHOW statement presents it in the following format.

December, 1997

When you do not include the vnf argument, the format of the result is determined by the VNF of the dimension whose values you are converting. When the dimension has no VNF, the result is formatted according to the default VNF for the type of dimension being converted.

date-order

A text expression that specifies how to interpret the specified text value as a DATE-only value when the order of the text value's components (month, day, and year) is ambiguous. The expression can be one of the following: 'MDY', 'DMY', 'YMD', 'YDM', 'MYD', or 'DYM'. Each letter represents a component of the date: M stands for month, D stands for day, and Y stands for year.

When you do not include the date-order or dwmqy-dimname argument, any ambiguity in the interpretation of a text expression is resolved by the current setting of the DATEORDER option. Refer to the DATEORDER option for a complete description of DATE-only values and how they are interpreted.

dwmqy-dimname

The name of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR whose VNF or default date-order determines how to interpret the specified text value as a DATE-only value when the order of the text value's components is ambiguous.

When you do not include the date-order or dwmqy-dimname argument, any ambiguity in the interpretation of a text expression is resolved by the current setting of the DATEORDER option. Refer to the DATEORDER option for a complete description of DATE-only values and how they are interpreted.

width-exp

An INTEGER expression that indicates the width of the output from CONVERT. The minimum width is 7. The default width is the current value of the LSIZE option. This argument is required when you specify the escape-int argument.

escape-int

Indicates whether escape sequences are to be used in the output. For this argument you can specify a value listed in the following table:

Table 7-5 Values for escape-int Parameter of the CONVERT

Value	Description
-1	Do not use escapes. Precede -1 with a comma (,-1) so that Oracle OLAP does not subtract 1 from a preceding WIDTH argument.
0	(Default) Use escapes for unprintable characters.
1	Use escapes for all characters.

For more information on escape sequences in the OLAP DML, see "Escape Sequences".

width-exp

An INTEGER expression that controls the width of the converted result. It can evaluate to 1, 2, or 4 bytes. The default width is 2 for BOOLEAN, or 4 for INTEGER. When an INTEGER value is too large to fit in the specified width, the result is NA. When the width is invalid or specified for some other data type, an error occurs.

NOXLATE

A keyword indicating that no character set conversion should be performed. Instead, Oracle OLAP only tags the converted value with the target data type, leaving the data as it was before the CONVERT function was called. Use this keyword only when it is necessary to store binary data in a TEXT or NTEXT variable.

Usage Notes

INFILE Conversion

The maximum number of characters in a line is 4,000. An error occurs when you try an INFILE conversion that produces a line with more than 4,000 characters. This type of error can occur when the source line exceeds 99 characters and enough of them need escape sequences.

Converting DATE-only Values to Numeric Values

The result of converting a value that has the DATE-only data type to a value with any numeric data type is the sequence number that represents the date (the sequence number 1 represents January 1, 1900).

Oracle OLAP first converts the DATE-only value to an INTEGER value that is the sequence number that represents the DATE-only value. When the target data type is a numeric data type other than INTEGER, Oracle OLAP then converts that INTEGER value to the specified numeric data type.

The value 32,767 is the largest possible value for a SHORTINTEGER, and (as an INTEGER value) represents the date September 17, 1992. Therefore, CONVERT returns NA when you attempt to convert any DATE-only later than September 17, 1992 to a SHORTINTEGER value.

Converting Numeric Values to DATE-only Values

The result is the DATE-only whose sequence number matches the specified number (January 1, 1900 is represented by the sequence number 1); or NA, when the result is outside the range of valid dates. Valid dates range from January 1, 1900 (sequence number 1) to December 31, 9999 (sequence number 2,958,464).

When the numeric data type is an INTEGER data type, Oracle OLAP converts the INTEGER value directly to the DATE-only value whose sequence number matches the specified number. When the numeric data type is not INTEGER, Oracle OLAP first converts the numeric value to an INTEGER value and then converts that INTEGER value to a DATE-only value.

Converting DATE-only Dimension Values to ID Values

When the result is more than eight characters long, the result is truncated.

Converting Relation Values to INTEGER Values

The result is an INTEGER value that represents the position of the value in the relation's dimension. This behavior reflects the fact that the values of a relation are dimension values, not TEXT values.

Converting Values From One Numeric Data Type to Another

The result is the value in the specified data type; or NA when the value is outside the range of valid values for the target data type.

Thus, when you try to convert an INTEGER value that is larger than 32,767 or smaller than -32,767 to a SHORTINTEGER value, CONVERT returns NA.

String-to-Datetime Conversion Rules

The following formatting rules apply when converting string values to datetime values:

• You can omit punctuation included in the format string from the datetime string if all the digits of the numeric format elements, including leading zeros, are specified. In other words, specify 02 and not 2 for two-digit format elements such as MM, DD, and YY.

• You can omit time fields found at the end of a format string from the datetime string.

• When a match fails between a datetime format element and the corresponding characters in the date string, then Oracle attempts alternative format elements, as shown in the following table:

'MM'

Converting Null and Blank Text Values to BYTE Values

CONVERT returns the same value for a null string ('') as it does for a blank string (' '). In both cases, you get a result of 32.

PACKED and BINARY Conversion

The PACKED and BINARY types are useful for creating binary files that contain PACKED and BINARY data. To create such a file, use FILEOPEN statement with the BINARY keyword to open the file and FILEPUT to write values to it. You can use the ROW function as an argument to the FILEPUT statement to help format the file.

Examples

Return Value

DECIMAL

Syntax

CORRELATION(expression1 expression2 [PEARSON|SPEARMAN|KENDALL] -      [BASEDON dimension-list])

Parameters

expression1

A dimensioned numeric expression with at least one dimension in common with expression2.

expression2

A dimensioned numeric expression with at least one dimension in common with expression1.

PEARSON

Calculates the Pearson product-moment correlation coefficient. Use this method when the data is interval-level or ratios, such as units sold and price for each unit, and the data values in the expressions have a linear relationship and are distributed normally.

SPEARMAN

Calculates Spearman's rho correlation coefficient. Use this nonparametric method when the expressions do not have a linear relationship or a normal distribution. In computing the correlation coefficient, this method ranks the data values in expression1 and in expression2 and then compares the rank of each element in expression1 to the corresponding element in expression2. This method assumes that most of the values in the expressions are unique.

KENDALL

Calculates Kendall's tau correlation coefficient. This nonparametric method is similar to the SPEARMAN method in that it also first ranks the data values in expression1 and in expression2. The KENDALL method, however, compares the ranks of each pair to the successive pairs. Use this method when few of the data values in expression1 and in expression2 are unique.

BASEDON dimension-list

An optional list of dimensions along which CORRELATION computes the correlation coefficient. Both expression1 and expression2 must be dimensioned by all of the dimension-list dimensions. CORRELATION correlates the data values of expression1 to those of expression2 along all of the dimension-list dimensions. CORRELATION returns an array that contains one correlation coefficient for each cell that is dimensioned by all of the dimensions of expression1 and expression2 except those in dimension-list.

When you do not specify a dimension-list argument, then CORRELATION computes the correlation coefficient over all of the common dimensions of expression1 and expression2. When all of the dimensions of the two expressions are the same, then CORRELATION returns a single correlation coefficient. When either expression contains dimensions that are not shared by the other expression, then CORRELATION returns an array that contains one correlation coefficient for each cell that is dimensioned by the dimensions of the expressions that are not shared.

Usage Notes

The Effect of NASKIP on CORRELATION

CORRELATION is affected by the NASKIP option. When NASKIP is set to YES (the default), then CORRELATION ignores NA values. When NASKIP is set to NO, then an NA value in the expressions results in a correlation coefficient of NA.

Examples

Return Value

NUMBER

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

Syntax

COS(angle-expression)

Parameters

angle-expression

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

Examples

Return Value

NUMBER

Syntax

COSH(expression)

Parameters

expression

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

Examples

Return Value

INTEGER

Syntax

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

Parameters

boolean-expression

The Boolean expression whose TRUE values are to be counted.

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, COUNT returns a single YES or NO value. When you indicate one or more dimensions for the result, COUNT 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 COUNT

COUNT is affected by the NASKIP option. When NASKIP is set to YES (the default), COUNT returns the number of TRUE values of the Boolean expression, regardless of how many other values are NA. When NASKIP is set to NO, COUNT returns NA when any value of the expression is NA. When all the values of the expression are NA, COUNT returns NA for either setting of NASKIP.

Examples

Return Value

DECIMAL

Syntax

CUMSUM(cum-expression [STATUS] total-dim [reset-dim] [INSTAT])

Parameters

cum-expression

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

STATUS

When cum-expression is multidimensional, CUMSUM creates a temporary variable to use while processing the function. When you specify the STATUS keyword, CUMSUM uses the current status instead of the default status of the dimensions for calculating the size of this temporary variable. When the dimensions of the expression are limited to a few values and are physically fragmented, you can improve the performance of CUMSUM by specifying STATUS.

When you use CUMSUM with the STATUS keyword in an expression that requires going outside of status for results (for example, with the LEAD or LAG functions or with a qualified data reference), the results outside of status are returned as NA.

Note:

When you specify the STATUS keyword when the data being totaled is one-dimensional, an error results

total-dim

The dimension of cum-expression over which you want to total.

reset-dim

Specifies that the cumulative totals in a series should start over with each new reset dimension value, for example at the start of each new year. The reset dimension can be any of the following:

• Any dimension related to total-dim through an explicitly defined relation.

• Any dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR, when total-dim also has a type of DAY, WEEK, MONTH, QUARTER, or YEAR. CUMSUM uses the implicit relation between the two dimensions, so they do not have to be related through an explicit relation. See "Overriding an Implicit Relation".

• A relation dimensioned by total-dim. CUMSUM uses the related dimension as the reset dimension which enables you to choose which relation is used when there are multiple relations.

INSTAT

Specifies that CUMSUM uses only the values of total-dim that are currently in status. When you do not specify INSTAT, CUMSUM produces a total for all the values of total-dim, independent of its current status. See "INSTAT Ignores Current Status By Default".

Usage Notes

Overriding an Implicit Relation

When you specify dimensions with a type of DAY, WEEK, MONTH, QUARTER, or YEAR for both the total-dim argument and the reset-dim argument, CUMSUM uses the implicit relation between the two dimensions even when an explicit relation exists. However, you can override the default and use the explicit relation by specifying the name of the relation for the reset-dim argument.

INSTAT Ignores Current Status By Default

Unless you specify the INSTAT keyword, CUMSUM ignores the current status in calculating totals. Suppose MONTH is the dimension being totaled over (and INSTAT has not been specified). The CUMSUM total for a given month uses the values for all preceding months, even when some are not in the status. When a reset dimension is specified, the total for a given month uses the values for all preceding months that correspond to the same value of the reset dimension (for example, all preceding months in the same year). To calculate year-to-date totals, specify YEAR as the reset dimension.

Examples

The totals for CUMSUM(UNITS, MONTH) include values for all months beginning with the first month, JAN95. The totals for CUMSUM(UNITS, MONTH YEAR) include only the values starting with JAN96.

Return Values

DATETIME

Syntax

CURENT_DATE

Examples

Return Values

TIMESTAMP_TZ

Syntax

CURRENT_TIMESTAMP [ (precision) ]

Parameters

precision

The fractional second precision of the time value returned. When you omit this argument, then the function uses a default value of 6.

Examples

Return Value

INTEGER

Syntax

DAYOF(date-expression)

Parameters

date-expression

An expression that has the DATE data type, or a text expression that specifies a date. Instead of a DATE expression, you can specify a text expression that has values that conform to a valid input style for dates. DAYOF automatically converts the values of the text expression to DATE values, using the current setting of the DATEORDER option to resolve any ambiguity.

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 database time zone value in the most recent CREATE DATABASE or ALTER DATABASE statement.

Syntax

DBTIMEZONE

Examples

Return Value

INTEGER

Syntax

DDOF(date-expression)

Parameters

date-expression

An expression that has the DATE data type, or a text expression that specifies a date. See "Date-only Input Values" for valid formats for a text expression.

Examples

Return Value

The data type of the first result argument.

Syntax

DECODE (expr , search, result [, search , result]... [, default])

Parameters

expr

The expression to be searched. The function automatically converts expr to the data type of the first search value before comparing

search

An expression to search for. The function automatically each search value to the data type of the first search value before comparing

result

The expression to return when expression equals search.

default

An expression to return when expression is not equal to search.

Usage Notes

Order of Value Evaluation

The search, result, and default values can be derived from expressions. The function evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, the function never evaluates a search when a previous search equals expr.

Examples

Return Value

DECIMAL

The return value is dimensioned by all the dimensions of start-exp.

Syntax

DEPRDECL(start-exp end-exp n [STATUS] [decline-factor [ {FULL|HALF|portion-exp}[time-dimension] ] ])

Parameters

start-exp

A numeric expression that contains the starting values of the assets. The start-exp expression must be dimensioned by a time dimension. For each value of the time dimension, start-exp contains the initial value of the assets acquired during that time period. In addition to a time dimension, start-exp can also have non-time dimensions.

end-exp

A numeric expression that contains the ending values of the assets. The end-exp expression must be dimensioned by the same dimensions as start-exp. For each value of the time dimension, end-exp contains the final (or salvage) value for the assets acquired during that time period. Each value of start-exp must have a corresponding end-exp value. For example, when the assets acquired in 1996 have a salvage value of $200, then the value of end-exp for 1996 is $200.

n

An INTEGER expression that contains the number of periods for the depreciation life of the assets. The n expression can have any of the non-time dimensions of start-exp, but it cannot have a time dimension.

STATUS

Specifies that DEPRDECL should use the current status list (that is, only the dimension values currently in status in their current status order) when computing the depreciation expenses. By default DEPRDECL uses the default status list.

decline-factor

A numeric expression that gives the declining balance rate to use for calculating the depreciation expenses. The decline-factor expression can have any of the non-time dimensions of start-exp, but it cannot have a time dimension.

A factor of 2 indicates a double declining balance. The default is 2.

FULL

(Default) Specifies that the full amount of a time period's depreciation expense is charged to the time period in which assets were acquired. Charges the full amount to all of the assets in the series.

HALF

Specifies that half of the full amount of a time period's depreciation expense is charged to the time period in which assets were acquired. Charges half the full amount to all of the assets in the series. When you specify HALF as the portion of depreciation expenses to charge to the period of acquisition, the HALF factor is applied to each period. Half of each period's full depreciation is rolled to the next period, and the final half period of depreciation takes place in the time period n + 1. You might want to use HALF when assets are acquired during the second half of the time period.

portion-exp

When you want to charge the full amount for some assets and half the amount for other assets, you can supply a portion-exp expression that is dimensioned by any of the non-time dimensions of start-exp. The portion-exp expression must be a text expression with values of FULL or HALF.

time-dimension

The name of the time dimension by which start-exp and end-exp are dimensioned. When the time dimension has a type of DAY, WEEK, MONTH, QUARTER, or YEAR, the time-dimension argument is optional.

Usage Notes

Calculation Method Used by DEPRDECL

DEPRDECL calculates the depreciation expense for a given time period as the sum of that period's depreciation expenses for all assets in the series that are not yet fully depreciated. The first period of depreciation for an asset is the period in which it was acquired.

For each time period, DEPRDECL calculates the declining balance depreciation expense by multiplying the current value of an asset by the decline-factor and dividing the result by the number of periods in the lifetime of an asset. However, when the calculation for a specific time period results in an asset's current value going below the ending value, then the depreciation expense is adjusted. In this instance, the depreciation expense is calculated as the current value minus the ending value.

Low Ending Value

When the ending value specified for an asset is low enough that the depreciation expense for the last period does not have to be adjusted, then the total depreciation expense over all the periods is typically less than the starting value minus the specified ending value.

High Ending Value

When the ending value specified for an asset is relatively high, then an asset might be totally depreciated in fewer periods than were specified for the lifetime of the depreciation. In this instance, when you want the depreciation expense applied across the specified lifetime of the depreciation, you can lower the decline-factor.

DEPRDECL and NA Values

When a value of start-exp is NA and the corresponding value of end-exp is not NA, an error occurs. Similarly, when a value of end-exp is NA and the corresponding value of start-exp is not NA, an error occurs.

DEPRDECL is affected by the NASKIP option when a value of start-exp and the corresponding value of end-exp are both NA. When NASKIP is YES (the default), DEPRDECL treats the values as zeros when calculating the depreciation expenses. When NASKIP is NO, DEPRDECL returns NA for all affected time periods.

Examples

Return Value

DECIMAL, dimensioned by all the dimensions of start-exp.

Syntax

DEPRDECLSW(start-exp end-exp n [STATUS]      [decline-factor [{FULL|HALF| portion-exp}  [switch-period [time-dimension]]]])

Parameters

start-exp

A numeric expression that contains the starting values of the assets. The start-exp expression must be dimensioned by a time dimension. For each value of the time dimension, start-exp contains the initial value of the assets acquired during that time period. In addition to a time dimension, start-exp can also have non-time dimensions.

end-exp

A numeric expression that contains the ending value of the assets. The end-exp expression must be dimensioned by the same dimensions as start-exp. For each value of the time dimension, end-exp contains the final (or salvage) value for the assets acquired during that time period. Each value of start-exp must have a corresponding end-exp value. For example, when the assets acquired in 1990 have a salvage value of $200, then the value of end-exp for 1990 is $200.

n

An INTEGER expression that contains the number of periods for the depreciation life of the assets. The n expression can have any of the non-time dimensions of start-exp, but it cannot have a time dimension.

STATUS

Specifies that DEPRDECLSW should use the current status list (that is, only the dimension values currently in status in their current status order) when computing the depreciation expenses. By default DEPRDECLSW uses the default status list.

decline-factor

A numeric expression that gives the declining balance rate to use for calculating the depreciation expenses. The decline-factor expression can have any of the non-time dimensions of start-exp, but it cannot have a time dimension.

A factor of 2 indicates a double declining balance. The default is 2.

FULL

(Default) Specifies that the full amount of a time period's depreciation expense is charged to the time period in which assets were acquired. Charges the full amount to all of the assets in the series. This argument is optional; however, when you include it, you must also include the preceding optional arguments.

HALF

Specifies that half of the full amount of a time period's depreciation expense is charged to the time period in which assets were acquired. Charges half the full amount to all of the assets in the series. You might want to use HALF when assets are acquired during the second half of the time period. When you specify HALF as the portion of depreciation expenses to charge to the period of acquisition, the HALF factor is applied to each period. Half of each period's full depreciation is rolled to the next period, and the final half period of depreciation takes place in the time period n + 1. This argument is optional; however, when you include it, you must also include the preceding optional arguments.

portion-exp

When you want to charge the full amount for some assets and half the amount for other assets, you can supply a portion-exp expression that is dimensioned by any of the non-time dimensions of start-exp. The portion-exp expression must be a text expression with values of FULL or HALF. This argument is optional; however, when you include it, you must also include the preceding optional arguments.

switch-period

An INTEGER expression that indicates the time period in which the calculation should switch to the straight-line method. This argument is optional; however, when you include it, you must also include the preceding optional arguments.

A common accounting practice is to switch to a straight-line method in the first period for which straight-line depreciation over the remaining periods exceeds the declining-balance depreciation. You can specify this behavior by not specifying the switch-period argument.

When the switch-period argument is not specified or has a value of NA or 0, the calculation switches from the declining method to the straight-line method in the first period for which straight-line depreciation over the remaining periods exceeds the declining-balance depreciation. In this case, the DEPRDECLSW function behaves just like the DEPRDECL function.

When you want to specify different switch periods for different assets, you can supply an expression that is dimensioned by any of the non-time dimensions of start-exp.

time-dimension

The name of the time dimension by which start-exp and end-exp are dimensioned. When the time dimension has a type of DAY, WEEK, MONTH, QUARTER, or YEAR, the time-dimension argument is optional. When you include this argument, you must also include the preceding optional arguments

Usage Notes

Calculation Method Used by DEPRDECLSW

DEPRDECLSW calculates the depreciation expense for a given time period as the sum of that period's depreciation expenses for all assets in the series that are not yet fully depreciated. The first period of depreciation for an asset is the period in which it was acquired.

For each time period in which DEPRDECLSW is calculating depreciation according to the declining balance method, it calculates the depreciation expense by multiplying the current value of an asset by the decline-factor and dividing the result by the number of periods in the lifetime of the asset. When DEPRDECLSW switches to the straight-line method, it subtracts the depreciation expense (from previous periods) from the value of an asset and divides the resulting amount by the number of periods left in the lifetime of the asset. However, when the depreciation expense calculated for a specific time period would result in an asset's current value going below its ending value, then the depreciation expense is adjusted. In this instance, the depreciation expense is calculated as the current value minus the ending value.

The straight-line method as used by DEPRDECLSW differs from the traditional straight-line method as used by DEPRSL. Unlike other methods of depreciation, the declining-balance methods of depreciation ignore the salvage value for an asset until the period in which the calculated depreciation would exceed the remaining depreciable value. Even DEPRDECLSW ignores the salvage value in this manner after it switches from the declining-balance method to the straight-line method. For example, suppose the beginning value for an asset is 16,000 and the salvage value is 1,000 over 5 periods. The total depreciation through the periods using declining balance method (here the first three) is 11,544. The straight-line calculations for the remaining periods would be based on the overall remaining value of 16,000 minus 11,544 (3,456), rather than the overall value minus the salvage value (2,456). Thus the depreciation for the last two periods would be 1,728; but for the very last period the salvage value is subtracted out and thus is 728.

Unexpected-Balance Method

When the ending value specified for an asset is relatively high, then an asset might be totally depreciated in fewer periods than were specified for the lifetime of the depreciation. In this instance, when you want the depreciation expense applied across the specified lifetime of the depreciation, you can lower the decline-factor.

DEPRDECLSW and NA Values

When a value of start-exp is NA and the corresponding value of end-exp is not NA, an error occurs. Similarly, when a value of end-exp is NA and the corresponding value of start-exp is not NA, an error occurs.

DEPRDECLSW is affected by the NASKIP option when a value of start-exp and the corresponding value of end-exp are both NA. When NASKIP is YES (the default), DEPRDECLSW treats the values as zeros when calculating the depreciation expenses. When NASKIP is NO, DEPRDECLSW returns NA for all affected time periods.

Examples

Return Value

DECIMAL, dimensioned by all the dimensions of start-exp.

Syntax

DEPRSL(start-exp end-exp n [STATUS] [{FULL|HALF| portion-exp}  [time-dimension]])

Parameters

start-exp

A numeric expression that contains the starting values of the assets. The start-exp expression must be dimensioned by a time dimension. For each value of the time dimension, start-exp contains the initial value of the assets acquired during that time period. In addition to a time dimension, start-exp can also have non-time dimensions.

end-exp

A numeric expression that contains the ending values of the assets. The end-exp expression must be dimensioned by the same dimensions as start-exp. For each value of the time dimension, end-exp contains the final (or salvage) value for the assets acquired during that time period. Each value of start-exp must have a corresponding end-exp value. For example, when the assets acquired in 1995 have a salvage value of $200, then the value of end-exp for 1995 is $200.

n

An INTEGER expression that contains the depreciation lifetime of the assets. The n expression can have any of the non-time dimensions of start-exp, but it cannot have a time dimension.

STATUS

Specifies that DEPRSL should use the current status list (that is, only the dimension values currently in status in their current status order) when computing the depreciation expenses. By default DEPRSL uses the default status list.

FULL

(Default) Specifies that the full amount of a time period's depreciation expense is charged to the time period in which assets were acquired. Charges the full amount to all of the assets in the series.

HALF

Specifies that half of the full amount of a time period's depreciation expense is charged to the time period in which assets were acquired. Charges half the full amount to all of the assets in the series. When you specify HALF as the portion of depreciation expenses to charge to the period of acquisition, the HALF factor is applied to each period. Half of each period's full depreciation expense is rolled to the next period, and the final half period of depreciation takes place in the time period n + 1. You might want to use HALF when assets are acquired during the second half of the time period.

portion-exp

When you want to charge the full amount for some assets and half the amount for other assets, you can supply a portion-exp expression that is dimensioned by any of the non-time dimensions of start-exp. The portion-exp expression must be a text expression with values of FULL or HALF.

time-dimension

The name of the time dimension by which start-exp and end-exp are dimensioned. When the time dimension has a type of DAY, WEEK, MONTH, QUARTER, or YEAR, the time-dimension argument is optional.

Usage Notes

DEPRSL Calculation Method

DEPRSL calculates the depreciation expense for a given time period as the sum of that period's depreciation expenses for all assets in the series that are not yet fully depreciated. The first period of depreciation for an asset is the period in which it was acquired.

DEPRSL and NA Values

When a value of start-exp is NA and the corresponding value of end-exp is not NA, an error occurs. Similarly, when a value of end-exp is NA and the corresponding value of start-exp is not NA, an error occurs.

DEPRSL is affected by the NASKIP option when a value of start-exp and the corresponding value of end-exp are both NA. When NASKIP is YES (the default), DEPRSL treats the values as zeros when calculating the depreciation expenses. When NASKIP is NO, DEPRSL returns NA for all affected time periods.

Examples

Return Value

DECIMAL, dimensioned by all the dimensions of start-exp.

Syntax

DEPRSOYD(start-exp end-exp n [STATUS] [{FULL|HALF| portion-exp} [time-dimension]])

Parameters

start-exp

A numeric expression that contains the starting values of the assets. The start-exp expression must be dimensioned by a time dimension. For each value of the time dimension, start-exp contains the initial value of the assets acquired during that time period. In addition to a time dimension, start-exp can also have non-time dimensions.

end-exp

A numeric expression that contains the ending values of the assets. The end-exp expression must be dimensioned by the same dimensions as start-exp. For each value of the time dimension, end-exp contains the final (or salvage) value for the assets acquired during that time period. Each value of start-exp must have a corresponding end-exp value. For example, when the assets acquired in 1995 have a salvage value of $200, then the value of end-exp for 1995 is $200.

n

An INTEGER expression that contains the depreciation lifetime of the assets. The n expression can have any of the non-time dimensions of start-exp, but it cannot have a time dimension.

STATUS

Specifies that DEPRSOYD should use the current status list (that is, only the dimension values currently in status in their current status order) when computing the depreciation expenses. By default DEPRSOYD uses the default status list.

FULL

(Default) Specifies that the full amount of a time period's depreciation expense is charged to the time period in which assets were acquired. Charges the full amount to all of the assets in the series.

HALF

Specifies that half of the full amount of a time period's depreciation expense is charged to the time period in which assets were acquired. Charges half the full amount to all of the assets in the series. When you specify HALF as the portion of depreciation expenses to charge to the period of acquisition, the HALF factor is applied to each period. Half of each period's full depreciation expense is rolled to the next period, and the final half period of depreciation expense takes place in the n + 1 time period. You might want to use HALF when assets are acquired during the second half of the time period.

portion-exp

When you want to charge the full amount for some assets and half the amount for other assets, you can supply a portion-exp expression that is dimensioned by any of the non-time dimensions of start-exp. The portion-exp expression must be a text expression with values of FULL or HALF.

time-dimension

The name of the time dimension by which start-exp and end-exp are dimensioned.When the time dimension has a type of DAY, WEEK, MONTH, QUARTER, or YEAR, the time-dimension argument is optional.

Usage Notes

Calculation Method Used by DEPRSOYD

DEPRSOYD calculates the depreciation expense for a given time period as the sum of that period's depreciation expenses for all assets in the series that are not yet fully depreciated. The first period of depreciation for an asset is the period in which it was acquired.

For each time period in the lifetime of an asset, DEPRSOYD bases the depreciation expense calculation on a specific cut of the total amount to be depreciated. The value of the cut is such that the full depreciation expense can be achieved over the lifetime of an asset by multiplying the cut by the number of time periods not yet depreciated.

For example, when the lifetime of an asset is 5 years, then DEPRSOYD calculates the cut, x, as follows.

5x + 4x + 3x + 2x + 1x = total depreciation 

In this case, the cut is 1/15th of the total depreciation. When the initial asset is $1,000 and its salvage value is $100, then the total depreciation is $900.00, and x is $60 ($900/15). For the first time period, the depreciation is $300 ($60 x 5). For the second time period, the depreciation is $240 ($60 x 4) and so on.

DEPRSOYD and NA Values

When a value of start-exp is NA and the corresponding value of end-exp is not NA, an error occurs. Similarly, when a value of end-exp is NA and the corresponding value of start-exp is not NA, an error occurs.

DEPRSOYD is affected by the NASKIP option when a value of start-exp and the corresponding value of end-exp are both NA. When NASKIP is YES (the default), DEPRSOYD treats the values as zeros when calculating the depreciation expenses. When NASKIP is NO, DEPRSOYD returns NA for all affected time periods.

Examples

Return Value

DATE-only or text

Syntax

ENDDATE(expression)

Parameters

expression

The expression must have exactly one dimension that has the type of DAY, WEEK, MONTH, QUARTER, or YEAR. When all the values of the expression are NA, ENDDATE returns NA.

Examples

Return Value

DATE-only or text

Syntax

ENDOF(dwmqy-dimension)

Parameters

dwmqy-dimension

A dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. When you have explicitly defined your own relation between dimensions of this type, you can use the name of this time relation here.

Examples

Return Value

BOOLEAN

Syntax

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

Parameters

boolean-expression

The Boolean expression whose values are 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, EVERY returns a single YES or NO value. When you indicate one or more dimensions for the result, EVERY 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 EVERY

EVERY is affected by the NASKIP option. When NASKIP is set to YES (the default), EVERY ignores NA values and returns YES when every value of the expression that is not NA is TRUE and returns NO when any values are not TRUE. When NASKIP is set to NO, EVERY returns NA when any value of the expression is NA. When all the values of the expression are NA, EVERY returns NA for either setting of NASKIP.

Examples

Return Value

BOOLEAN

Syntax

EXISTS(name-expression)

Parameters

name-expression

A text expression that specifies the name you want to test.

Usage Notes

Specifying More Than One Name

When name-expression contains multiple object names, EXISTS returns NO even when all the objects specified by name-expression exist in attached workspaces.

Examples

Return Value

NUMBER

Syntax

EXP (n)

Parameters

n

The power by which you want to raise e.

Examples

Return Value

TEXT

Syntax

EVERSION

Usage Notes

EVERSION and Major Releases

The build number in the output of the EVERSION function is not the Oracle Database version number. The EVERSION value does not change only with major releases of the database.

Examples

Return Value

TEXT

Syntax

EXTBYTES(text-expression [start [length]])

Parameters

text-expression

A TEXT expression from which a portion is to be extracted. When text-expression is a multiline TEXT value, EXTBYTES preserves the line breaks in the returned value.

start

An INTEGER that represents the byte position at which to begin extracting. The position of the first byte in text-expression is 1. When you omit this argument, EXTBYTES starts with the first byte.

length

An INTEGER that represents the number of bytes to be extracted. When length is not specified, or exceeds the number of bytes from start to the end of text-expression, the part from start to the end of text-expression is extracted.

Examples

Return Value

TEXT or NTEXT

Syntax

EXTCHARS(text-expression [start [length]])

Parameters

text-expression

A TEXT or NTEXT expression from which a portion is to be extracted. When text-expression is a multiline text value, EXTCHARS preserves the line breaks in the returned value.

start

An INTEGER that represents the character position at which to begin extracting. The position of the first character in text-expression is 1. When you omit this argument, EXTCHARS starts with the first character.

length

An INTEGER that represents the number of characters to be extracted. When length is not specified, or exceeds the number of characters from start to the end of text-expression, the part from start to the end of text-expression is extracted.

Examples

Return Value

TEXT or NTEXT

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

Syntax

EXTCOLS(text-expression [start [numcols]])

Parameters

text-expression

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

start

An INTEGER, between 1 and 32767, that represents the column position at which to begin extracting. 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 empty.

numcols

An INTEGER that represents the number of columns to be extracted. When you do not specify numcols, EXTCOLS extracts all the characters from the starting column to the end of each line. 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 existing characters. EXTCOLS does not return spaces at the end of the line to fill in the missing columns.

Examples

Return Value

TEXT or NTEXT

Syntax

EXTLINES(text-expression [start [numlines]])

Parameters

text-expression

A multiline TEXT or NTEXT expression from whose values one or more lines are to be extracted.

start

An INTEGER that represents the line number at which to begin extracting. The position of the first line in text-expression is 1. When you omit this argument, EXTLINES begins with line 1.

numlines

An INTEGER representing the number of lines to be extracted. When you do not specify numlines, or when you specify a number greater than the number of lines from start to the end of text-expression, all the lines from start to the end of text-expression are copied.

Examples

Return Values

The value returned varies:

• When extracting from a datetime with a time zone value, the function returns a value in UTC.

• When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the function returns a text string that is the appropriate time zone name or abbreviation.

• When you extract any of the other values, the function returns a value in the Gregorian calendar.

• When the values you specify results in an ambiguity, the function returns NA.

Syntax

EXTRACT(time |timezone_hour_or_nimute |timezone_regn_or_abbr FROM datetime_exp| interval_exp )

Parameters

time

One of the following keywords: YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND which specify the portion of the time that you want the function to return.

timezone_hour_or_minute

One of the following keywords: TIMEZONE_HOUR or TIMEZONE_MINUTE which specify that you want the function to return either the hour or minute portion of a TIMESTAMP_TZ expression.

timezone_regn_or_abbr

One of the following keywords: TIMEZONE_REGION or TIMEZONE_ABBR which specify that you want the function to return a string that is either the region name or its abbreviation.

datetime_exp

A DATETIME, TIMESTAMP, TIMESTAMP_TZ, or TIMESTAMP_LTZ expression. See "Datetime Expressions" for information on how to specify these expressions.

interval_exp

A DSINTERVAL or YMINTERVAL expression. See "Interval Expressions" for information on how to specify these expressions.

Usage Notes

The value you are extracting must be a value of the appropriate datetime_exp or interval_exp. For example, you can extract only YEAR, MONTH, and DAY from a DATETIME value. Likewise, you can extract TIMEZONE_HOUR and TIMEZONE_MINUTE only from the TIMESTAMP_TZ data type.

Examples

Return Value

INTEGER

Syntax

FCOPEN(text-expression [prototype-handle])

Parameters

text-expression

The name of the forecasting context.

prototype-handle

An INTEGER expression that is the handle to a different forecasting context that was previously-created using the FCOPEN function. Oracle OLAP initializes the new forecasting context with the same options as the forecasting context specified by this parameter. (See the FCSET command for descriptions of the options that specify the characteristics of a forecasting context.)

Examples

For an example of a forecasting program, see Example 9-119.

Return Value

The return value depends on the option that you use as described in the tables for this entry.

Syntax

FCQUERY(HANDLELIST|handle-expression option -      [TRIAL trial-num] [CYCLE cycle-num])

Parameters

HANDLELIST

When you specify the HANDLELIST keyword, the FCQUERY function returns a multiline text expression that is a list of the handles to forecasting contexts that are currently open.

handle-expression

An INTEGER expression that is the handle to forecast context that you want to query and that was previously opened using the FCOPEN function.

option

The specific information to retrieve:

• When you want information about the options specified for the entire forecast, do not use the TRIAL keyword. In this case, option can be any of the options that you can specify using the FCSET command and any of the options listed in the following table.

  Table 7-7 Options That You Can Specify for the Entire Forecast

  Keyword	Return type	Description
  HANDLEID	TEXT	The name of the forecasting context when a value was specified when the forecasting context was opened using the FCOPEN command; or NA when no name was specified at that time.
  TRIALSRUN	INTEGER	The number of trials for which data is available; or NA when no trials were run.

• When you want information about a specific trial, use the TRIAL trial-num phrase. In this case, option can be any of the options listed in the following table.

  Table 7-8 Options That You Can Specify for an Individual Trial

  Option	Return Value	Description
  ALLOCLAST	BOOLEAN	Indicates whether the risk of over-adjustment should be reduced by allocating, instead of forecasting, the last cycle.
  ALPHA	DOUBLE	The value of Alpha for this trial of the forecast. Alpha is the level or baseline parameter that is used for the Single Exponential Smoothing, Double Exponential Smoothing, and Holt-Winters forecasting methods.
  BETA	DOUBLE	The value of Beta for this trial of the forecast. Beta is the trend parameter that controls the estimate of the trend. Beta is used for the Double Exponential Smoothing and Holt-Winters forecasting methods.
  COMPSMOOTH	BOOLEAN	Indicates whether optimization should be done on the median smoothed data series.
  CYCDECAY	DOUBLE	The value of the cyclic decay parameter for this trial of the forecast. Cyclical decay pertains to how seriously Oracle OLAP considers deviations from baseline activity when it performs linear and nonlinear regressions.
  GAMMA	DOUBLE	The value of Gamma for this trial of the forecast. Gamma is the seasonal parameter that is used for the Holt-Winters forecasting method.
  HISTUSED	INTEGER	The number of historical periods actually used, after all leading NA values are bypassed.
  MAD	DOUBLE	The mean absolute deviation (MAD) for this trial of the forecast.
  MAPE	DOUBLE	The mean average percent error (MAPE) for this trial of the forecast.
  MAXFCFACTOR	DECIMAL	The upper bound of the forecast data.
  METHOD	TEXT	The forecasting method that Oracle OLAP used for this trial of the forecast. See the METHOD option of the FCSET command for descriptions of the various methods.
  MINFCFACTOR	DECIMAL	The lower bound of the forecast data.
  MPTDECAY	DOUBLE	The value of the parameter that Oracle OLAP used when it adjusted the decay of estimates of base values that were used when it unraveled the predictions on the moving periodic total (MPT) series for this trial of the forecast.
  NCYCLES	INTEGER	The number of cycles specified using the PERIODICITY argument to FCSET.
  PERIODICITY	INTEGER	The length, in periods, of one or more cycles. The return value depends on the way you call the FCQUERY function: When you specify the CYCLE argument, PERIODICITY returns the number of periods in the specified cycle. When you do not specify the CYCLE argument and FCSET ALLOCLAST is NO, PERIODICITY returns the product of all cycle lengths. When you do not specify the CYCLE argument and FCSET ALLOCLAST is YES, PERIODICITY returns the product of all cycle lengths leaving out the length of the last (least aggregate) cycle.
  RMSE	DOUBLE	The root mean squared error (RMSE) for this trial of the forecast.
  SMOOTHING	BOOLEAN	Indicates whether Oracle OLAP smoothed the data for this trial of the forecast. YES indicates that Oracle OLAP smoothed the data; NO indicates that Oracle OLAP did not smooth the data.
  TRANSFORM	TEXT	The data filter that Oracle OLAP used for this trial of the forecast. See the TRANSFORM option of the FCSET command for descriptions of the various filters.
  TRENDHOLD	DOUBLE	The value of the trend hold parameter for this trial of the forecast. trend hold parameter that indicates trend reliability in Double Exponential Smoothing and Holt-Winters forecasting methods.

trial-num

An INTEGER expression that is the number of the trial for which you want to retrieve information.

cycle-num

An INTEGER expression that specifies a cycle for which you want information from the PERIODICITY option (see Table 7-8). When you specified a series of cycles using the PERIODICITY argument in the FCSET command, then the value of cycle-num indicates the position of the cycle of interest in the specified series. For example, assume that FCSET PERIODICITY <52,7> was specified. In this case, a cycle-num of 1 returns 52 and a cycle-num of 2 returns 7. When you did not specify a series of cycles using the PERIODICITY argument in the FCSET command, then it is unnecessary to specify this argument.

Usage Notes

Using Options

You can retrieve information about the options specified for the entire forecast or information about a specific trial.

• When you want information about the options specified for the entire forecast, do not use the TRIAL keyword. In this case, option can be HANDLEID, TRIALSRUN, or any of the options that you can specify using the FCSET command.

• When you want information about a specific trial, use the TRIAL trial-num phrase. In this case, option can be ALPHA, BETA, CYCDECAY, GAMMA, MAD, MAPE, METHOD, MPTDECAY, RMSE, SMOOTHING, TRANSFORM, or TRENDHOLD.

Accessing Dimensioned Data

When multiple time series are in status when the FCEXEC command executes, then the TRIALSRUN and the NTRIAL-dimensioned data are also be dimensioned by the extra dimensions of the time-series expression. Although Oracle OLAP treats the value returned by the FCQUERY function as a scalar expression, you can access its dimensioned data in any of the following ways:

• In a FOR loop, FCQUERY returns data for the current values of the FOR dimensions

• In a QUAL function, FCQUERY returns data for the specified values of the qualified dimensions.

• In all other cases, FCQUERY returns data for the first value in status of each of its dimensions.

Examples

Return Value

Varies depending on the specified keyword.

Syntax

FILEERROR (TYPE|POSITION|WIDTH|VALUE|DIMENSION)

Parameters

TYPE

Returns a text expression that specifies the type of error that has occurred. The types of errors and their meanings are listed in the following table:

Table 7-9 Types of Errors Returned by FILEERROR

Return Value	Meaning
DIMENSION	The data reading statements tried to set the status of a dimension (through an implicit or explicit MATCH attribute), but the specified position or value did not exist.
NA	No error occurred in the processing of the current record.
POSITION	The data reading program tried to read from an invalid location in the record. A POSITION error can occur when the field or column is before the beginning of the record or when the field extends past the end of the record. An error beyond the end of the record occurs only for binary or packed data; for symbolic (textual) data, the data reading statements pad short records with blanks.
VALUE	The value could not be converted to the requested data type. For packed data, the record had an invalid hexadecimal digit.
WIDTH	The data reading statements specified an invalid field width. Invalid widths depend on the format of the data, which can be symbolic, packed, or binary: For symbolic format, the width is invalid when it is less than 1 or when it is NA. Note that NA is acceptable for ID data. For packed format, the width is invalid when it is less than 1, greater than 8, or NA. For binary format, the width requirement depends on whether the data is INTEGER or DECIMAL (floating-point). Integer data must have a width of 1, 2, or 4. Decimal data must have a width of 4 or 8.

POSITION

Returns an INTEGER that is the column number (for RULED records) or field number (for STRUCTURED records) when the error occurred.

WIDTH

Returns an INTEGER that is the current field width. It returns NA when NA was specified as the width or the error was a POSITION error. A POSITION error stops processing before the width can be evaluated.

VALUE

When the error type is VALUE, it returns a text expression that is the value that could not be converted. When the data is packed, the invalid value is shown as hexadecimal escapes. When the error type is DIMENSION, it returns the value that did not match any existing dimension value. For other error types, it returns NA.

DIMENSION

When the error type was DIMENSION, it returns a text expression that is the name of the dimension that had no matching dimension values. For other error types, it returns NA.

Usage Notes

Flow of Control

When an error occurs in FILEREAD or FILEVIEW, processing of the current record stops and Oracle OLAP displays an appropriate error message. Then, when your program has a trap label, control branches to the label where you might call FILEERROR to investigate the problem. When you branch back to a FILEREAD or FILENEXT function, processing continues with the next record. When there are more errors in the record, those errors are not evaluated.

Displaying Error Messages in the Current Outfile

Set ECHOPROMPT to YES in your data reading program when you want error messages to be displayed in the current outfile. When the error occurred during FILEREAD or FILEVIEW, any evaluation by FILEERROR occurs after the error message.

Examples

Return Value

TEXT

Syntax

FILEGET(fileunit [LENGTH int-expression])

Parameters

fileunit

An INTEGER value that was assigned to a file opened for reading in a previous call to the FILEOPEN function.

LENGTH int-expression

An INTEGER expression specifying the number of bytes FILEGET returns from the file. When an end-of-line character is reached in the input file, FILEGET simply starts a new line in the result it is constructing. When LENGTH is omitted, FILEGET reads one line or record regardless of how many bytes it contains.

Usage Notes

Difference Between Number of Bytes Read and Number of Bytes Returned

The value specified by LENGTH refers to the number of bytes that the FILEGET function returns, not to the number of bytes that it reads. In some cases, these values may differ. For example, when the file being read contains a tab character, the number of bytes returned by FILEGET includes the bytes for tab expansion (if any); consequently, the number of bytes returned by FILEGET could be larger than the number of bytes read by FILEGET.

Examples

Return Value

BOOLEAN

Syntax

FILENEXT(fileunit)

Parameters

fileunit

An INTEGER value that is assigned to a file that is opened for reading in a previous call to the FILEOPEN function or by the OUTFILE command.

Usage Notes

Opening and Closing Files

Before you can get records from a file with FILENEXT, use the FILEOPEN function to open the file for reading (READ mode). When you are finished, close the file with a FILECLOSE statement.

Processing Data

After reading a record with FILENEXT, use a FILEVIEW statement to process the record. FILEVIEW processes input data and assigns the data to analytic workspace objects or local variables according to a description of each field. You can call FILEVIEW more than once for continued processing of the same record. To process another record, call FILENEXT again.

Automatic Looping

When all the records are being processed in essentially the same way, the FILEREAD command is easier to use because it loops over the records in a file automatically.

Writing Records

To write selected records to an output file, see the FILEPUT command.

Record Numbers

Use the RECNO function to get the current record number for any file that is opened for read-only access.

Reading Binary and Text Files

When you did not specify BINARY for the file when you opened it, FILENEXT reads data up to and including the next newline character. When you specified BINARY for the file when you opened it, you must use FILESET to set LSIZE to the appropriate record length before using the FILENEXT function. Then, FILENEXT reads data one record at a time.

Examples

Return Value

INTEGER

Syntax

FILEOPEN(file-name {READ|WRITE|APPEND} [BINARY]) [NLS_CHARSET charset-exp]

Parameters

file-name

A text expression specifying the name of the file you want to open. Unless the file is in the current directory, you must include the name of the directory object in the name of the file.

Note:

Directory objects are defined in the database, and they control access to directories and file in those directories. You can use a CDA statement to identify and specify a current directory object. Contact your Oracle DBA for access rights to a directory object where your database user name can read and write files.

READ

(Abbreviated R) Opens the file for reading.

WRITE

(Abbreviated W) Opens the file for writing. File access begins at the top of the file. Therefore, opening an existing file in WRITE mode erases its contents completely even before anything is written to the file.

APPEND

Opens the file for writing. File access begins at the end of the file, and data is added to the existing contents.

BINARY

Opens a binary-format file (a file with packed or binary data). When you specify BINARY, Oracle OLAP considers every character in the file to be data. Rather than using newline characters to tell when records end, it assumes records of a fixed length, which you can set with FILESET(...LSIZE). The default record length is 80.

NLS_CHARSET charset-exp

Specifies the character set that Oracle OLAP uses when reading data from the file specified by file-name. When this argument is omitted, then Oracle OLAP handles the data in the file as having the database character set, which is recorded in the NLS_LANG option.

Usage Notes

Multiple File Units

You can open as many files at the same time as your operating system allows.

Access Modes

The mode of access, READ, WRITE, or APPEND, must be appropriate to the file.

Examples

Return Value

The data type of the return value depends on the attribute you specify. See Table 7-10 for more information.

Syntax

FILEQUERY(file-id attrib-arg)

Parameters

file-id

A fileunit number or a file name.

• A fileunit number is a number that Oracle OLAP assigned to a file you opened through a previous call to the FILEOPEN function or through the OUTFILE command. You can use the return value of the FILEOPEN function or the value of the OUTFILEUNIT option.

• A file name is a text expression specifying the name of the file you want to move or rename. Unless the file is in the current directory, you must include the name of the directory object in the name of the file.

  Note:

  Directory objects are defined in the database, and they control access to directories and file in those directories. You can use a CDA statement to identify and specify a current directory object. Contact your Oracle DBA for access rights to a directory object where your database user name can read and write files.

Some attributes require that you specify a fileunit number; others require the file name. In many cases, you can specify either. See Table 7-10 for more information.

attrib-arg

Specifies the type of information you want to retrieve about the file. The data type of FILEQUERY's return value depends on the attribute you specify. The attribute you specify must be appropriate for the file; otherwise, an error occurs. The following table lists the valid keywords for attrib-arg and, for each keyword, provides a description and indicates whether you specify a file-unit-number of a file-name for the file-id argument.

Table 7-10 File Attributes Returned by FILEQUERY

Keyword	Return Values	Return Data Type	file-id Parameter
APPEND	TRUE when the file is open for writing at the end (that is, TRUE for APPEND and WRITE); FALSE when it is not.	BOOLEAN	Fileunit number
BMARGIN	The number of blank lines that form the bottom margin.	INTEGER	Fileunit number
CHANGED	TRUE when the file's archive bit is set; FALSE when it is not.	BOOLEAN	Fileunit number or file name
EOF	TRUE when end-of-file has been reached; FALSE when it is not.	BOOLEAN	Fileunit number
EXISTS	TRUE when the file exists; FALSE when it is not.	BOOLEAN	Fileunit number or file name
FILENAME	The file name associated with the fileunit.	TEXT	Fileunit number
LINENUM	The current line number. Resets after each page break when PAGING is on; keeps incrementing when PAGING is off. When file is currently open in READ mode, returns the current record number.	INTEGER	Fileunit number
LINESLEFT	The number of lines left on the page.	INTEGER	Fileunit number
LSIZE	For a file that is open for writing, the line length for the standard Oracle OLAP page heading. (See the STDHDR program.) For a fileunit that is open for reading, specifies the record length for binary input files.	INTEGER	Fileunit number
NLS_CHARSET	The character set being used for this fileunit. See the FILEOPEN function for more information.	TEXT	Fileunit number
NUMBYTES	The size of the file in bytes.	INTEGER	Fileunit number or file name
ORIGIN	The type of computer on which the file was created. The ORIGIN attribute, which is relevant only for files that are open for reading, is set when you issue a FILESET statement.	TEXT	Fileunit number
PAGENUM	The current page number. See "Paging Attributes".	INTEGER	Fileunit number
PAGEPRG	The Oracle OLAP program or statement that produces headings when output is paged. See "Paging Attributes".	TEXT	Fileunit number
PAGESIZE	The number of lines on each page. See "Paging Attributes".	INTEGER	Fileunit number
PAGING	TRUE when the output is formatted in pages; FALSE when it is not. See "Paging Attributes".	BOOLEAN	Fileunit number
PAUSEATPAGEEND	TRUE when Oracle OLAP pauses after each page; FALSE when it does not. See "Paging Attributes".	BOOLEAN	Fileunit number
R[EAD]	TRUE when the file is open for reading; FALSE when it is not.	BOOLEAN	Fileunit number
RO	TRUE when the file's read-only attribute is set; FALSE when it is not.	BOOLEAN	Fileunit number or file name
TABEXPAND	TRUE when the tab characters are expanded when the file is read by FILEGET or FILEREAD; FALSE when they are not. See "Tab Treatment".	BOOLEAN	Fileunit number or file name
TMARGIN	The number of blank lines that form the top margin.	INTEGER	Fileunit number
UNIT	The file unit for the specified file name.	INTEGER	File name
W[RITE]	TRUE when the file is open for writing; FALSE when it is not.	BOOLEAN	Fileunit number

Usage Notes

Tab Treatment

When you want tab characters in the source file to be expanded when read by FILEGET or FILEREAD, you can specify the TABEXPAND attribute with the FILESET command. When TABEXPAND is zero, tab characters are not expanded. A value greater than 0 indicates the distance, in bytes, between tab stops. The default value of TABEXPAND is 8.

Paging Attributes

The paging attributes apply only to files that currently, unless otherwise noted, have PAGING set to YES and are open in WRITE mode -- such as files opened with FILEOPEN(...WRITE) or FILEOPEN(...APPEND). You can set any of the paging attributes with the FILESET command.

Wildcard Characters

(UNIX only) When querying for UNIX file names, wildcard characters (that is, * ?) are allowed when searching with the EXISTS attribute argument.

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

FILTERLINES(source-expression filter-expression)

Parameters

source-expression

A multiline text expression whose lines should be modified according to filter-expression.

filter-expression

An expression to be applied as a filter to each line of source-expression. The terms of the filter expression dictate the processing that FILTERLINES performs on each line of the source expression.

The filter expression may produce NA, which means that there is no line in the resulting text expression corresponding to the current line of the source expression.

You can use the keyword VALUE in your filter expression to represent the current line of the source expression.

Usage Notes

The Result of FILTERLINES

FILTERLINES returns a text expression composed of the lines that result from the action of the filter expression on each line of the source expression. The filter expression may return multiline text for any or all of the input source lines. None of these lines are acted on again by the filter expression.

Examples

Return Value

INTEGER

Syntax

FINDBYTES(text-expression, bytes [starting-pos [LINENUM]])

Parameters

text-expression

The TEXT expression in which you are searching for the specified bytes. The value of text-expression can be a multiline value. In this case, FINDBYTES searches all lines for the specified bytes. The match must be exact, including a match of upper- and lowercase characters.

Tip:

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

bytes

The group of bytes for which you are searching. When bytes is a multiline value, FINDBYTES ignores all lines except the first one.

When bytes is not found in text-expression, FINDBYTES returns zero. When the group of bytes occurs more than once, FINDBYTES returns the position of its first occurrence.

starting-pos

An INTEGER expression that specifies the byte position where the search in text-expression should start. The default is at position 1 (the first byte) in text-expression.

LINENUM

Specifies that FINDBYTES should return the line number instead of the byte position of the beginning of the specified text.

Examples

Return Value

INTEGER

Syntax

FINDCHARS(text-expression, characters [starting-pos [LINENUM]])

Parameters

text-expression

The text expression in which you are searching for the specified characters. Text-expression can be a multiline value. In this case, FINDCHARS searches all lines for the specified characters. The match must be exact, including a match of upper- and lowercase characters.

FINDCHARS accepts TEXT values and NTEXT values as arguments. When only one argument is NTEXT, then FINDCHARS automatically converts the other argument to NTEXT before performing the function operation

characters

The group of characters for which you are searching. When characters is a multiline value, FINDCHARS ignores all lines except the first one.

When characters is not found in text-expression, FINDCHARS returns zero. When the group of characters occurs more than once, FINDCHARS returns the position of its first occurrence.

starting-pos

An INTEGER expression that specifies the character position where the search in text-exp should start. The default is at position 1 (the first character) in text-exp.

LINENUM

Specifies that FINDCHARS should return the line number instead of the character position of the beginning of the specified text.

Examples

Return Value

INTEGER

Syntax

FINDLINES(text-expression, lines)

Parameters

text-expression

A text expression within whose values you want to locate a certain line or group of lines. FINDLINES searches text-expression for the specified lines. The match must be exact, including a match of uppercase and lowercase characters. Also, when you specify two or more lines, FINDLINES searches for all the specified lines as a single continuous block in text-expression. When all the lines occur in text-expression, but are not in a continuous block, FINDLINES returns 0 (not found).

FINDLINES accepts TEXT values and NTEXT values as arguments. When only one argument is NTEXT, then FINDLINES automatically converts the other argument to NTEXT before performing the function operation.

Note that when the value of text-expression is NA, FINDLINES returns NA.

lines

A second text expression containing the line(s) for which you are searching. When lines is not found in text-expression, FINDLINES returns 0. When lines occurs more than once, FINDLINES returns the line number of its first occurrence.