8 OLAP DML Functions: L - Z

Return Value

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

Syntax

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

Parameters

variable

A variable or expression that is dimensioned by dimension.

n

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

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

Note:

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

dimension

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

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

STATUS

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

NOSTATUS

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

limit-clause

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

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

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

Usage Notes

Assigning Results to a Time-Series Variable

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

Examples

DEFINE time DIMENSION TEXT
DEFINE timelevels DIMENSION TEXT
DEFINE timelevelrel RELATION timelevels <time>
DEFINE product DIMENSION TEXT
DEFINE district DIMENSION TEXT
DEFINE sales VARIABLE DECIMAL <time product district>
 

REPORT timelevelrel
 
TIME           TIMELEVELREL
-------------- ----------
AllYears       AllYears
2004           Years
2005           Years
2006           Years
Jan2005        Months
Feb2005        Months
Mar2005        Months
Apr2005        Months
...            ...
Oct2006        Months
Nov2006        Months
Dec2006        Months
 

REPORT product
 
PRODUCT
--------------
TVs
DVDs
Computers
 
REPORT district
 
DISTRICT
--------------
All Districts
Mass
Conn
Boston
Springfield
Hartford
New Haven
 

LIMIT product TO 'TVs'
LIMIT district TO 'Hartford'
LIMIT time TO 'Jan2006' 'Feb2006' 'Mar2006' 'Apr2006' 'May2006' 'Jun2006' 'Jul2006' 'Aug2006' 'Sep2006' 'Oct2006' 'Nov2006' 'Dec2006'
 

REPORT DOWN time HEADING 'Sales 2006' sales HEADING 'Sales 2005' LAG(sales, 12, time, RELATION timelevelrel) 

 
DISTRICT: Hartford
               -------PRODUCT-------
               ---------TVs---------
TIME           Sales 2006 Sales 2005
-------------- ---------- ----------
Jan2006          1,542.91   1,627.51
Feb2006          1,786.07   1,100.13
Mar2006          1,794.43   1,667.61
Apr2006          1,942.92   1,346.66
May2006          1,530.08   1,509.51
Jun2006          1,613.60   1,242.47
Jul2006          1,666.35   1,307.17
Aug2006          1,413.79   1,033.93
Sep2006          1,526.98   1,773.96
Oct2006          1,112.85   1,103.78
Nov2006          1,193.41   1,132.39
Dec2006          1,851.19   1,543.62

Return Value

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

Syntax

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

Parameters

time-series

A variable or expression that is dimensioned by dimension.

n

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

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

dimension

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

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

STATUS

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

NOSTATUS

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

limit-clause

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

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

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

Usage Notes

Formula Used by LAGABSPCT

To obtain its results, LAGABSPCT uses the following formula.

(currentvalue - previousvalue) / ABS(previousvalue)

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

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

Examples

ALLSTAT
LIMIT product TO 'Racquets'
LIMIT district TO 'Dallas'
LIMIT time TO 'Jan2000'
REPORT DOWN time sales -
HEADING 'Last Jan' LAG(sales, 12, time, time LEVELREL timelevelrel)-
HEADING 'Lagdif' LAGDIF(sales, 12, time, time LEVELREL timelevelrel)-
HEADING 'Lagabspct' rset '%' d 0 LAGABSPCT(sales, 12, time, -
                    time LEVELREL timelevelrel) * 100

DISTRICT: Dallas
               ------------------PRODUCT------------------
               -----------------Racquets------------------
TIME           SALES      Last Jan   Lagdif     Lagabspct
-------------- ---------- ---------- ---------- ----------
Jan2000        125,879.86 118,686.75 7,193.11   6% 

Return Value

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

Syntax

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

Parameters

variable

A variable or expression that is dimensioned by dimension.

n

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

dimension

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

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

STATUS

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

NOSTATUS

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

limit-clause

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

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

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

Examples

For an example of using LAGDIF, see Example 8-2, "Using LAGDIF and LAGABSPCT".

Return Value

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

Syntax

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

Parameters

variable

A variable or expression that is dimensioned by dimension.

n

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

dimension

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

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

STATUS

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

NOSTATUS

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

limit-clause

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

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

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

Usage Notes

Formula Used by LAGPCT

To obtain its results, LAGPCT uses the following formula.

(currentvalue - previousvalue) / previousvalue

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

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

Examples

ALLSTAT
LIMIT product TO 'Racquets'
LIMIT district TO 'Dallas'
LIMIT TIME TO '2000'
REPORT DOWN time sales HEADING 'Last Year' -
LAG(sales, 1, time, time LEVELREL timelevelrel)-
HEADING 'LAGPCT (Decimal Format)' -
LAGPCT(sales, 1, time LEVELREL timelevelrel) -
HEADING 'LAGPCT (Percent Format)' rset '%' -
LAGPCT(sales, 1, time LEVELREL timelevelrel) * 100

DISTRICT: Dallas
               ------------------PRODUCT------------------
               -----------------racquets------------------
                                     LAGPCT     LAGPCT
                                     (Decimal   (Percent
TIME           SALES      Last Year  Format)    Format)
-------------- ---------- ---------- ---------- ----------
2000           93,000,003 89,000,891 0.04       4.49%

Return Value

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

Syntax

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

Parameters

expression

The expression whose largest value is to be returned.

CACHE

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

dimension

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

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

Tip:

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

Usage Notes

NA Values and LARGEST

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

Using LARGEST With an Expression Dimensioned by a DWMQY Dimension

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

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

Examples

LIMIT product TO 'Sportswear'
LIMIT month TO 'Jan96' TO 'Jun96'
REPORT HEADING 'Largest Sales' LARGEST(sales district)

                Largest
DISTRICT         Sales
-------------- ----------
Boston          79,630.20
Atlanta        177,967.49
Chicago        112,792.78
Dallas         175,716.31
Denver          97,236.88
Seattle         60,322.88

Return Value

DATETIME

Syntax

LAST_DAY(datetime-expression)

Parameters

datetime-expression

An expression that has the DATETIME data type.

Examples

SHOW JOINCHARS('Days left: ' LAST_DAY(SYSDATE) - SYSDATE)

Days left: 22

Return Value

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

Syntax

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

Parameters

variable

A variable or expression that is dimensioned by dimension.

n

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

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

Note:

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

dimension

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

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

STATUS

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

NOSTATUS

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

limit-clause

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

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

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

Examples

1999
2000
Jan1999
Feb1999
...
Dec1999
Jan2000
Feb2000
...
Dec2000

TIME           TIMELEVELREL
-------------- ------------
1999           Year
2000           Year
Jan1999        Month
Feb1999        Month
...            ...
Dec1999        Month
Jan2000        Month
Feb2000        Month
...            ...
Dec2000        Month

LIMIT product TO 'Racquets'
LIMIT district TO 'Dallas'
LIMIT time TO 'JAN1999' 'FEB1999'
REPORT DOWN time sales HEADING 'Following Year' LEAD(sales, 12, time, time LEVELREL timelevelrel)

DISTRICT: DALLAS
               -------PRODUCT-------
               ------RACQUETS-------
TIME           SALES      Following Year
-------------- ---------- ---------------------
Jan2000        118,686.75 125,879.86
Feb2000        142,305.99 150,833.64

Return Value

The data type of the first expression.

Syntax

LEAST (expr [, expr]...)

Parameters

expr

An expression.

Examples

SHOW LEAST('Harry','Harriot','Harold')
Harold 

SHOW LEAST (5, 3, 18)
3

Return Value

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

Syntax

{ LENGTH | LENGTHB | LENGTHC}(char)

Parameters

char

A text expression.

Return Value

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

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

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

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

Syntax

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

Syntax for Retrieving Values From a LIMIT Command

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

     [limit-clause] [IFNONE label])

Syntax for Retrieving Values From a Dimension Status Stack

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

Parameters

dimension

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

valueset

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

LIMIT_function

Another LIMIT function.

Note:

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

concat-component

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

limit-type

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

limit-clause

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

LIMIT (using values) command

LIMIT using LEVELREL command

LIMIT (using related dimension) command

LIMIT (using parent relation)

LIMIT NOCONVERT command

LIMIT command (using POSLIST)

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

INTEGER

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

STATDEPTH

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

stack-position

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

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

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

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

Usage Notes

Nesting the LIMIT Function

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

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

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

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

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

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

Limiting with a Component of a Concat Dimension

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

LIMIT(reg.dist.ccdim TO district)

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

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

Returning Multidimensional Results

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

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

A REPORT sales statement produces the following output.

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

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

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

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

Examples

LIMIT month TO 'Jan95' 'Feb95'
LIMIT district TO 'Boston' 'Atlanta'
LIMIT product TO ALL
REPORT LIMIT (product TO sales GT 50000)

               --LIMIT (PRODUCT TO--
               ---SALES GT 50000)---
               --------MONTH--------
DISTRICT         JAn95      Feb95
-------------- ---------- ----------
Boston         Canoes     Canoes
               Racquets   Racquets
               Sportswear Sportswear
               Footwear   Footwear
Atlanta        Racquets   Canoes
               Sportswear Racquets
               Footwear   Sportswear
                          Footwear

ALLSTAT
LIMIT month TO LIMIT (LIMIT (month TO LAST 10) KEEP FIRST 3)

The current status of MONTH is:
MAR97 TO MAY97 

Return Value

A TEXT expression.

Syntax

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

Parameters

aw

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

limit-map

The limit map as a text expression.

column-name

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

Examples

MEASURE sales FROM aw_f.sales
DIMENSION et_chan FROM aw_channel WITH
HIERARCHY aw_channel.parent
GID gid_chan FROM aw_channel.gid
DIMENSION et_prod FROM aw_product WITH
HIERARCHY aw_product.parent
GID gid_prod FROM aw_prod.gid
DIMENSION et_geog FROM aw_geography WITH
HIERARCHY aw_geography.parent
GID gid_geog FROM aw_geog.gid
DIMENSION et_time FROM aw_time WITH
HIERARCHY time.parent
GID gid_time FROM aw_time.gid

show LIMITMAPINFO ('myaw', mylimitmap, 'et_prod')  

aw_product  

Return Values

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

Syntax

LNNVL(condition)

Parameters

condition

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

Examples

SHOW LNNVL('apples' EQ 'oranges')
yes

SHOW LNNVL(7 LT 11)
no

SHOW LNNVL('vegetables' EQ NA)
yes

Return Values

TIMESTAMP

Syntax

LOCALTIMESTAMP [ (timestamp-precision) ]

ARGUMENTS

timestamp-precision

Specifies the fractional second precision of the time value returned

Examples

SHOW LOCALTIMESTAMP
13-FEB-07 12.11.33.454834 PM

Return Value

DECIMAL

Syntax

LOG([base,] expression)

Parameters

base

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

expression

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

Examples

DECIMALS = 5
SHOW LOG(4000 + 6000)

9.21034

Return Value

DECIMAL

Syntax

LOG10(expression)

Parameters

expression

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

Examples

SHOW LOG10(1000)

3.00

Return Value

TEXT or NTEXT

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

Syntax

LOWCASE(text-expression)

Parameters

text-expression

The text expression whose characters are to be converted.

Examples

lastname = JOINCHARS(EXTCHARS(lastname, 1, 1), -
           LOWCASE(EXTCHARS(lastname, 2, NUMCHARS(lastname)))) 

Return Value

The data type of text-expression.

Syntax

LOWER(text-expression)

Parameters

text-expression

The text expression whose characters are to be converted.

Return Value

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

Syntax

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

Parameters

text-exp

A text expression to pad.

length

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

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

pad-exp

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

Examples

SHOW LPAD('Page 1',15,'*.') 
*.*.*.*.*Page 1

Return Value

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

Syntax

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

Parameters

text-exp

A text expression that you want trimmed.

trim-exp

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

Examples

SHOW LTRIM('xyxxxyLast Word','xy') 
Last Word

Return Value

DATE or text

Syntax

MAKEDATE(year month day)

Parameters

year

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

month

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

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

day

An INTEGER expression in the range 1 to 31.

Usage Notes

Format of the Result Returned by MAKEDATE

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

Invalid Dates

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

Examples

DATEFORMAT = '<mtextl> <d>, <yyyy>'
SHOW MAKEDATE(97 11 14)

November 14, 1997

SHOW MAKEDATE(YYOF(TODAY) + 1 MMOF(TODAY) DDOF(TODAY))

January 15, 1996

Return Value

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

Syntax

MAX(expression1, expression2)

Parameters

expression1

One expression to be compared.

expression2

The other expression to be compared.

Examples

LIMIT line TO 'Cogs'
LIMIT division TO 'Sporting'
LIMIT month TO 'Jan96' TO 'Jun96'
REPORT DOWN month actual budget MAX(actual budget)

DIVISION: SPORTING
               --------------LINE--------------
               --------------COGS--------------
                                        MAX    
                                      (ACTUAL  
MONTH            ACTUAL     BUDGET    BUDGET)  
-------------- ---------- ---------- ----------
Jan96          287,557.87 279,773.01 287,557.87
Feb96          315,298.82 323,981.56 323,981.56
Mar96          326,184.87 302,177.88 326,184.87
Apr96          394,544.27 386,100.82 394,544.27
May96          449,862.25 433,997.89 449,862.25
Jun96          457,347.55 448,042.45 457,347.55

Return Value

INTEGER

Syntax

MAXBYTES(text-expression)

Parameters

text-expression

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

Examples

SHOW mytext

This is a multiline text variable. 
The longest line is this one in the middle.
The third line is short.

SHOW MAXBYTES(mytext)

43 

Return Value

INTEGER

Syntax

MAXCHARS(text-expression)

Parameters

text-expression

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

Examples

SHOW mytext

This is a multiline text variable. 
The longest line is this one in the middle.
The third line is short.

SHOW MAXCHARS(mytext)

43 

Return Value

DECIMAL

Syntax

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

Parameters

expression

The expression whose median value is to be calculated.

CACHE

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

dimension

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

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

Tip:

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

Usage Notes

NA Values and MEDIAN

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

Examples

LIMIT product TO 'Sportswear'
REPORT W 12 HEADING 'Median Sales' MEDIAN(sales district)

DISTRICT          Median Sales
----------------- ------------
Boston               67,923.05
Atlanta             152,186.52
Chicago              94,372.06
Dallas              160,854.60
Denver               86,745.40
Seattle              53,950.28

Return Value

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

Syntax

MIN(expression1, expression2)

Parameters

expression1

One expression to be compared.

expression2

The other expression to be compared.

Examples

LIMIT line TO 'Cogs'
LIMIT division TO 'Sporting'
LIMIT month TO 'Jan96' TO 'Jun96'
REPORT DOWN month actual budget MIN(actual budget)

DIVISION: SPORTING
               --------------LINE--------------
               --------------COGS--------------
                                        MIN    
                                      (ACTUAL  
MONTH            ACTUAL     BUDGET    BUDGET)  
-------------- ---------- ---------- ----------
Jan96          287,557.87 279,773.01 279,773.01
Feb96          315,298.82 323,981.56 315,298.82
Mar96          326,184.87 302,177.88 302,177.88
Apr96          394,544.27 386,100.82 386,100.82
May96          449,862.25 433,997.89 433,997.89
Jun96          457,347.55 448,042.45 448,042.45

Return Value

INTEGER

Syntax

MMOF(date-expression)

Parameters

date-expression

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

Examples

SHOW MMOF(TODAY)

1

Return Value

DECIMAL

Syntax

MODE(expression [CACHE} [dimensions])

Parameters

expression

The numeric expression whose mode is to be calculated.

CACHE

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

dimensions

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

Usage Notes

The Effect of NASKIP on MODE

MODE is not affected by the NASKIP option.

More Than One Set of Duplicate Values

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

Examples

DEFINE geography DIMENSION TEXT 
MAINTAIN geography ADD 'g1' 'g2' 'g3'
DEFINE items DIMENSION TEXT
MAINTAIN items ADD 'Item1' 'Item2' 'Item3' 'Item4' 'Item5'
DEFINE sales2 DECIMAL <geography items>

               -------------SALES2-------------
               -----------GEOGRAPHY------------
ITEMS              G1         G2         G3
-------------- ---------- ---------- ----------
Item1               30.00      15.00      12.00
Item2               10.00      20.00      18.00
Item3               15.00      20.00      24.00
Item4               30.00      25.00      25.00
Item5                  NA       7.00      21.00

Return Values

Numeric.

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

Syntax

MOD(dividend, divisor)

Parameters

dividend

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

divisor

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

Examples

SHOW MODULO(13,7)
6.00

Return Value

NUMBER

Syntax

MONTHS_BETWEEN(datetime_expression1, datetime_expression2)

Parameters

datetime-expression1

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

datetime-expression2

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

Examples

SHOW months_between('06Jul2005' '17Jul2003')
23.65

SHOW months_between('29Feb2000', '30Sep2000')
-7.00

Return Value

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

Syntax

MOVINGAVERAGE(expression, start, stop, step, -

     [dimension [STATUS|limit-clause]])

Parameters

expression

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

start

stop

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

Note:

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

Tip:

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

step

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

dimension

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

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

STATUS

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

limit-clause

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

Usage Notes

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

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

Examples

LIMIT product TO 'Womens - Trousers'
LIMIT timelevelnames TO 'Quarter'
LIMIT time TO time.levelrels
LIMIT time REMOVE 'Q1-1999' 'Q2-1999' 'Q3-1999'

REPORT DOWN time sales -
HEADING 'Running Yearly\nTotal' MOVINGTOTAL(sales, -4, 0, 1, time, -
     LEVELREL time.levelrels) -
HEADING 'Minimum\nQuarter' MOVINGMIN(sales, -4, 0, 1, time, -
     LEVELREL time.levelrels) -
HEADING 'Maximum\nQuarter' MOVINGMAX(sales, -4, 0, 1, time, -
     LEVELREL time.levelrels) -
HEADING 'Average\nQuarter' MOVINGAVERAGE(sales, -4, 0, 1, time, -
     LEVELREL time.levelrels)

               -----------------------PRODUCT------------------------
               ------------------Womens - Trousers-------------------
                          Running
                          Yearly     Minimum    Maximum    Average
TIME           SALES      Total      Quarter    Quarter    Quarter
-------------- ---------- ---------- ---------- ---------- ----------
Q4-1999         416            1,386        233        480     346.50
Q1-2000         465            1,851        233        480     370.20
Q2-2000         351            1,969        257        480     393.80
Q3-2000         403            2,115        351        480     423.00
Q4-2000         281            1,916        281        465     383.20
Q1-2001         419            1,919        281        465     383.80
Q2-2001         349            1,803        281        419     360.60
Q3-2001         467            1,919        281        467     383.80
Q4-2001         484            2,000        281        484     400.00
Q1-2002         362            2,081        349        484     416.20
Q2-2002         237            1,899        237        484     379.80
Q3-2002         497            2,047        237        497     409.40
Q4-2002         390            1,970        237        497     394.00

Return Value

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

Syntax

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

Parameters

expression

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

start

stop

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

Note:

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

Tip:

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

step

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

dimension

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

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

STATUS

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

Examples

For an example of calculating maximum sales, see Example 8-31, "Calculating a Moving Average".

Return Value

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

Syntax

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

Parameters

expression

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

start

stop

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

Note:

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

Tip:

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

step

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

dimension

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

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

STATUS

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

limit-clause

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

Examples

For an example of calculating minimum sales, see Example 8-31, "Calculating a Moving Average".

Return Value

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

Syntax

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

Parameters

expression

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

start

stop

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

Note:

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

Tip:

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

step

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

dimension

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

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

STATUS

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

limit-clause

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

Examples

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

Return Value

An NA value with the NA flag set to TRUE.

Syntax

NA2

Examples

See Example 9-104, "Defining a Variable with Null Tracking".

Return Value

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

Syntax

NAFILL(source-expression fill-expression)

Parameters

source-expression

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

fill-expression

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

Usage Notes

Functions in the Fill Expression

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

NATRIGGER Takes Precedence Over NAFILL

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

Examples

REPORT AVERAGE(NAFILL(sales 0.0))

Return Values

INTEGER

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

Syntax

NAFLAG (expression)

Parameters

expression

An OLAP DML expression.

Examples

See Example 9-104, "Defining a Variable with Null Tracking".

Return Value

DATETIME

Syntax

NEW_TIME(datetime-exp this_zone new_zone)

Parameters

this_zone

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

new_zone

A text expression that indicates the time zone into which you want to convert datetime-exp. It is the time zone of the return value. It must be a valid time zone, as listed in Table 8-1, "Time Zones".

Examples

SHOW new_time(SYSDATE 'EST' 'PST')

19-OCT-00

SHOW TO_CHAR(NEW_TIME(TO_DATE('11-27-00 22:15:00', 'MM-DD-YY HH24:MI:SS'), -
   'HST' 'PST') 'MM-DD-YY HH24:MI:SS')

11-28-00 00:15:00

NLS_DATE_FORMAT = 'MM-DD-YY HH24:MI:SS'

SHOW NEW_TIME(TO_DATE('11-27-00 22:15:00', 'MM-DD-YY HH24:MI:SS'), -
   'HST' 'PST')

Return Value

DATETIME

Syntax

NEXT_DAY(datetime-expression, weekday)

Parameters

datetime-expression

An expression that has the DATETIME data type.

weekday

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

Examples

SHOW NEXT_DAY(SYSDATE, 'Tues')

11-SEP-00

Return Value

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

Syntax

NLS_CHARSET_ID (charset_name)

Parameters

charset_name

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

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

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

See Also:

Oracle Database Globalization Support Guide for a list of character set names

Return Value

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

Syntax

NSL_CHARSET_NAME (number-exp)

Parameters

number-exp

A number that is the character set ID.

See Also:

Oracle Database Globalization Support Guide for a list of character set identifiers

Return Value

RAW

Syntax

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

Parameters

char

A text expression.

sort

An NLS language.

_ai

Specifies an accent-insensitive sort.

_ci

Specifies a case-insensitive sort.

Examples

show  NLSSORT('Mary Ann' , 'NLS_SORT = German_ai' )
501464820114555500010101010201010100

REPORT NLSSORT(MYname , 'NLS_SORT = German_ci' )
 
MYNAME           NLSSORT(MYNAME , 'NLS_SORT = German_ci')
---------------- ----------------------------------------
Adelaid          1423284B143C23000101010101010100
Maryann          50146482145555000101010101010100
Mary Ann         501464820114555500010101010201010100
Donna            235A55551400010101010100

Return Value

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

Syntax

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

Parameters

boolean-expression

The Boolean expression to be evaluated.

CACHE

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

dimension

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

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

Tip:

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

Usage Notes

The Effect of NASKIP on NONE

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

Examples

LIMIT product TO 'Sportswear'
REPORT NONE(sales LT 50000, district)

               NONE(SALES
               LT 50000,
DISTRICT       DISTRICT)
-------------- ----------
Boston                 NO
Atlanta               YES
Chicago               YES
Dallas                YES
Denver                YES
Seattle                NO

REPORT NONE(sales LT 50000, region)

               NONE(SALES
               LT 50000,
REGION          REGION)
-------------- ----------
East                   NO
Central               YES
West                   NO

Return Value

DECIMAL

Syntax

NORMAL(mean standard-deviation)

Parameters

mean

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

standard-deviation

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

Examples

SHOW NORMAL(0 1)

-0.75

0.87

Return Value

DECIMAL

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

Syntax

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

Parameters

cashflows

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

Note:

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

discount-rate

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

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

time-dimension

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

Usage Notes

NA Discount Rate

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

Cash Flow Timing

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

Examples

DEFINE project DIMENSION TEXT
MAINTAIN project ADD 'a' 'b' 'c' 'd' 'e'
DEFINE cflow VARIABLE DECIMAL <project year>

             ------------------------CFLOW----------------------
             -----------------------PROJECT---------------------
YEAR             a          b          c          d          e
------------ ---------- ---------- ---------- ---------- -------
Yr95           -200.00    -200.00    -300.00    -100.00  -200.00
Yr96            100.00     150.00     200.00      25.00    25.00
Yr97            100.00     400.00     200.00     100.00   200.00

REPORT NPV(cflow, .08, year)

               NPV(CFLOW,
PROJECT        .08, YEAR)
-------------- ----------
a                  -21.67
b                  281.82
c                   56.65
d                    8.88
e                   -5.38

Return Value

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

Syntax

NULLIF (expr1 , expr2)

Parameters

expr1

An expression. The base expression for the comparison.

expr2

An expression to compare to expr1.

Examples

SHOW NULLIF(1, '1')
NA
 
SHOW NULLIF('red', 'Red')
red

Return Value

INTEGER

Syntax

NUMBYTES(text-expression)

Parameters

text-expression

The TEXT expression whose bytes are to be counted.

Examples

firstcol = LARGEST(NUMBYTES(name.product))+1
LIMIT month TO FIRST 3
FOR product
   DO
     ROW WIDTH FIRSTCOL name.product WIDTH 6 ACROSS month - 
     FIRST 3: units
   DOEND

3-Person Tents      200    203    269
Aluminum Canoes     347    400    482
Tennis Racquets     992  1,076  1,114
Warm-up Suits     1,096  1,214  1,294
Running Shoes     2,532  2,405  2,775 

Return Value

INTEGER

Syntax

NUMCHARS(text-expression)

Parameters

text-expression

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

Examples

firstcol = LARGEST(NUMCHARS(name.product))+1
LIMIT month TO FIRST 3
FOR product
   DO
     ROW WIDTH FIRSTCOL name.product WIDTH 6 ACROSS month - 
     FIRST 3: units
   DOEND

3-Person Tents      200    203    269
Aluminum Canoes     347    400    482
Tennis Racquets     992  1,076  1,114
Warm-up Suits     1,096  1,214  1,294
Running Shoes     2,532  2,405  2,775 

Return Value

INTEGER

Syntax

NUMLINES(text-expression)

Parameters

text-expression

The text expression whose lines are to be counted.

Examples

Adamson
Jones
Smith
Taylor

SHOW NUMLINES(lastnames)

4 

Syntax

NUMTODSINTERVAL (number, interval_unit)

Parameters

number

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

interval_unit

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

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

Syntax

NUMTOYMINTERVAL (number, interval_unit)

Parameters

number

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

interval_unit

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

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

Return Value

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

Syntax

NVL (exp , replacement-exp)

Parameters

expr

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

replacement-exp

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

Examples

SHOW NVL('First String', 'Second String')
First String
 
SHOW NVL('', 'Second String')
Second String

Return Value

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

Syntax

NVL2 (expr1 , expr2 , expr3)

Parameters

expr1

The expression whose value this function evaluates.

expr2

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

expr3

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

Usage Notes

Comparing Values of Different Data Types

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

Examples

SHOW NVL2('Which string?', 'First String', 'Second String')
First String
 
SHOW NVL2('', 'First String', 'Second String')
Second String

Return Value

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

Syntax

OBJ(choice [object-name])

Parameters

object-name

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

choice

A keyword or keyword phrase which indicates the type of information you want. See Table 8-2, "Keywords for the choice argument of the OBJ Function" for the syntax, data type of the returned value and description of valid keywords.

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

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

object-name

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

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

Examples

LIMIT NAME TO OBJ(TYPE) EQ 'DIMENSION'
STATUS NAME

The current status of NAME is:
PRODUCT, DISTRICT, DIVISION, LINE, REGION, MARKETLEVEL, MARKET,
MONTH, YEAR, QUARTER

LIMIT NAME TO OBJ(TYPE) EQ 'RELATION'
DESCRIBE

DEFINE REGION.DISTRICT RELATION REGION <DISTRICT>
LD REGION for each DISTRICT
 
DEFINE DIVISION.PRODUCT RELATION DIVISION <PRODUCT>
LD DIVISION for each PRODUCT
 
DEFINE MLV.MARKET RELATION MARKETLEVEL <MARKET>
 
DEFINE MARKET.MARKET RELATION MARKET <MARKET>
LD Self-relation for the Market Dimension 

LIMIT NAME TO OBJ(ISBY 'month')

SHOW OBJ(ISBY 'district' 'proddist')

SHOW OBJ(ISBY 'mydimension' 'myaggmap')

SHOW OBJ(ISBY 'district' 'proddist.mon')
SHOW OBJ(ISBY 'district' 'proddist.sales')

SHOW OBJ(ISBY RECURSIVE 'district' 'proddist.mon')
SHOW OBJ(ISBY RECURSIVE 'district' 'proddist.sales')

DEFINE sales VARIABLE DECIMAL <month product district>
LD Sales Revenue

SHOW OBJ(TYPE 'textvar')
VARIABLE

SHOW OBJ(TYPE textvar)
DIMENSION

LIMIT NAME TO OBJ(TYPE) EQ 'RELATION'
DESCRIBE

DEFINE REGION.DISTRICT RELATION REGION <DISTRICT>
LD REGION for each DISTRICT
 
DEFINE DIVISION.PRODUCT RELATION DIVISION <PRODUCT>
LD DIVISION for each PRODUCT
 
DEFINE MLV.MARKET RELATION MARKETLEVEL <MARKET>
 
DEFINE MARKET.MARKET RELATION MARKET <MARKET>
LD Self-relation for the Market Dimension

SHOW COUNT(OBJ(ISCOMPILED))
SHOW COUNT(NOT OBJ(ISCOMPILED))

REPORT OBJ(REFERS OBJLIST(AW(LIST)))

SHOW OBJ(REFERS 'var1\nvar2\nvar3' 'myprog')

VAR1
VAR3

REPORT OBJ(REFERS 'var1\nvar2\nvar3')

               OBJ(REFERS
                 'var1
                  var2
NAME           var3' )
-------------- ----------
PRODUCT        NA
DISTRICT       NA
DIVISION       NA
LINE           NA
QUARTER        NA
REGION         NA
YEAR           NA
MONTH          NA
   ...
MYPROG         VAR1
               VAR3
VAR1           NA
VAR2           NA
VAR3           NA

CONSIDER actual
PROPERTY 'decplace' 4
LIMIT month TO FIRST 1
LIMIT division TO 'Camping'
REPORT ACROSS month W 20 DECIMAL OBJ(PROPERTY 'decplace' -
'actual') actual 

DIVISION: CAMPING
               -------ACTUAL-------
               -------MONTH--------
LINE                  JAN 95
-------------- --------------------
Revenue                533,362.8800
Cogs                   360,810.6600
Gross.Margin           172,552.2200
Marketing               37,369.5000
Selling                 89,007.3800
R.D                     24,307.5000
Opr.Income              21,867.8400
Taxes                   15,970.3900
Net.Income               5,897.4500

CHGDFN sales SEGWIDTH 150 5000 50
SHOW OBJ(SEGWIDTH ALL 'sales')

 150 MONTH
5000 PRODUCT
  50 DISTRICT

SHOW OBJ(SEGWIDTH 'product' 'sales')

5000

REPORT OBJ(SEGWIDTH 'product')

NAME           OBJ(SEGWIDTH 'product')
-------------- -----------------------
SALES          5000
SALES.FORECAST 5000
SALES.PLAN     5000
SHARE          5000
UNITS          5000
UNITS.M        0
   ...

REPORT OBJ(SEGWIDTH ALL)

NAME           OBJ(SEGWIDTH ALL)
-------------- -----------------
SALES          150 MONTH
               5000 PRODUCT
               50 DISTRICT
 
SALES.FORECAST 150 MONTH
               5000 PRODUCT
               50 DISTRICT
    ...

Return Value

TEXT

Syntax

OBJLIST[(text-expression)]

Parameters

text-expression

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

Examples

SHOW OBJLIST('mycode\nmydata\nmytools')

ACTUAL
ADDFIVE
ADVERTISING
BUDGET
CITYLIST
CITYREPINIT
CITYREPS
    ...
YEAR

SHOW OBJ(REFERS OBJLIST('mycode\nmydata\nmytools') 'myprog')

ACTUAL
BUDGET
 ...
YEAR

Return value

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

Syntax

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

Parameters

keyword

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

• Table 8-3, "Keywords when the top-level-object is a Cube".

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

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

• Table 8-6, "Keywords when the top-level-object is a Cube Dimension".

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

OWNER owner

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

top-level-object

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

sub-object

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

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

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

Examples

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

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

  • ADD_L1_2 program in Example 9-5, "Adding Members to an OLAP Cube Dimension"

  • REMOVE_L2_1 program in Example 10-98, "Removing OLAP Cube Dimension Members From a Hierarchy"

  • SETUP_PRE_MODEL and SETUP_POST_MODEL programs in Example 10-118, "Creating Static and Dynamic Models for an OLAP Cube"

  • ADD_L1_2_DATES program in Example 10-169, "Updating the Attributes of an OLAP Cube Dimension"

• For an example of using OBJORG with the Measure keyword, see the ADD_L1_2 program in Example 9-5, "Adding Members to an OLAP Cube Dimension".

• For an example of using OBJORG with the Parentrel keyword, see the REMOVE_L2-1 program in Example 10-98, "Removing OLAP Cube Dimension Members From a Hierarchy".

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

  • SETUP_POST_MODEL program in Example 10-118, "Creating Static and Dynamic Models for an OLAP Cube"

  • ADD_L1_2_DATES program in Example 10-169, "Updating the Attributes of an OLAP Cube Dimension"

Return Value

TEXT

Syntax

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

Parameters

HASH

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

• Encoded values cannot be restored to their original form.

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

HIDE

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

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

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

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

UNHIDE

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

TEXT

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

seed-exp

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

input-exp

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

Usage Notes

Decoding Text

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

Examples

DEFINE encoded_text VARIABLE TEXT
DEFINE unencoded_text VARIABLE TEXT
 
unencoded_text = 'max'
encoded_text = OBSCURE(HIDE TEXT 'XXXX' unencoded_text)
SHOW encoded_text

c5WF/XfABuY

Return Value

NUMBER

Syntax

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

Parameters

expr

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

max_bucket

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

seed_value

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

Return Value

Text

Syntax

PARTITION (partition_template_values)

Parameters

partition_template_values

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

Examples

DEFINE time DIMENSION TEXT
DEFINE product DIMENSION TEXT
DEFINE time_parentrel RELATION time <time>
DEFINE partition_sales_by_year PARTITION TEMPLATE <time product> -
PARTITION BY LIST (time)(PARTITION time_2003 VALUES -
('2003','Dec2003', 'Jan2003','31Dec2003','01Dec2003','31Jan2003','01Jan2003')-
<time product> PARTITION time_2002 VALUES -
('2002', 'Dec2002', 'Jan2002', '31Dec2002', '01Dec2002','31Jan2002','01Jan2002')-
 <time product>)
DEFINE sales DECIMAL <partition_sales_by_year<time product>>

REPORT DOWN PARTITION(partition_sales_by_year) time product sales
 
PARTITION(PART
ITION_SALES_BY
_YEAR)            TIME     PRODUCT     SALES
-------------- ---------- ---------- ----------
TIME_2003      2003       00001              NA
TIME_2003      Dec2003    00001              NA
TIME_2003      Jan2003    00001              NA
TIME_2003      31Dec2003  00001           14.78
TIME_2003      01Dec2003  00001           15.52
TIME_2003      31Jan2003  00001           13.61
TIME_2003      01Jan2003  00001           10.39
TIME_2003      2003       00002              NA
TIME_2003      Dec2003    00002              NA
TIME_2003      Jan2003    00002              NA
TIME_2003      31Dec2003  00002           16.05
TIME_2003      01Dec2003  00002           12.27
TIME_2003      31Jan2003  00002           10.83
TIME_2003      01Jan2003  00002           11.07
TIME_2002      2002       00001              NA
TIME_2002      Dec2002    00001              NA
TIME_2002      Jan2002    00001              NA
TIME_2002      31Dec2002  00001           18.80
TIME_2002      01Dec2002  00001           13.64
TIME_2002      31Jan2002  00001           12.41
TIME_2002      01Jan2002  00001           16.97
TIME_2002      2002       00002              NA
TIME_2002      Dec2002    00002              NA
TIME_2002      Jan2002    00002              NA
TIME_2002      31Dec2002  00002           17.47
TIME_2002      01Dec2002  00002           16.58
TIME_2002      31Jan2002  00002           18.94
TIME_2002      01Jan2002  00002           18.36
         

SHOW partition_sales_by_year
<2003, 00001>

" Use a QDR to identify the partition of a specific time value
SHOW PARTITION(partition_sales_by_year (time '31Jan2002'))
TIME_2002
 
REPORT DOWN time PARTITION(partition_sales_by_year)
 
               PARTITION(PARTITION_S
               ----ALES_BY_YEAR)----
               -------PRODUCT-------
TIME             00001      00002
-------------- ---------- ----------
2003           TIME_2003  TIME_2003
2002           TIME_2002  TIME_2002
Dec2003        TIME_2003  TIME_2003
Jan2003        TIME_2003  TIME_2003
Dec2002        TIME_2002  TIME_2002
Jan2002        TIME_2002  TIME_2002
31Dec2003      TIME_2003  TIME_2003
01Dec2003      TIME_2003  TIME_2003
31Jan2003      TIME_2003  TIME_2003
01Jan2003      TIME_2003  TIME_2003
31Dec2002      TIME_2002  TIME_2002
01Dec2002      TIME_2002  TIME_2002
31Jan2002      TIME_2002  TIME_2002
01Jan2002      TIME_2002  TIME_2002

Return Value

BOOLEAN.

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

Syntax

PARITITONCHECK (aggmap parttition-template)

Parameters

aggmap

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

partition-template

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

Return Value

DECIMAL

Syntax

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

Parameters

expression

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

CACHE

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

BASEDON dimension-list

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

Usage Notes

The Effect of NASKIP on PERCENTAGE

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

Examples

LIMIT month TO 'Jul96' TO 'Sep96'
LIMIT district TO 'Denver'
REPORT SUBTOTALS W 8 units

DISTRICT: DENVER
               ----------UNITS-----------
               ----------MONTH-----------
PRODUCT         Jul96    Aug96    Sep96
-------------- -------- -------- --------
Tents               608      517      441
Canoes              467      363      411
Racquets          3,006    2,836    2,838
Sportswear        2,395    2,039    2,138
Footwear          1,581    1,532    1,667
-------------- -------- -------- --------
TOTAL DENVER      8,057    7,287    7,495

REPORT SUBTOTALS W 8 DOWN month PERCENTAGE(units BASEDON month)

DISTRICT: DENVER
         -----------PERCENTAGE(UNITS BASEDON MONTH)------------
         -----------------------PRODUCT------------------------
MONTH      Tents      Canoes    Racquets  Sportswear  Footwear
-------- ---------- ---------- ---------- ---------- ----------
Jul96          0.39       0.38       0.35       0.36       0.33
Aug96          0.33       0.29       0.33       0.31       0.32
Sep96          0.28       0.33       0.33       0.33       0.35
-------- ---------- ---------- ---------- ---------- ----------
TOTAL          1.00       1.00       1.00       1.00       1.00
DENVER

REPORT SUBTOTALS W 8 PERCENTAGE(units BASEDON product)

DISTRICT: DENVER
               -PERCENTAGE(UNITS BASEDON-
               ---------PRODUCT)---------
               ----------MONTH-----------
PRODUCT         Jul96    Aug96    Sep96
-------------- -------- -------- --------
Tents              0.08     0.07     0.06
Canoes             0.06     0.05     0.05
Racquets           0.37     0.39     0.38
Sportswear         0.30     0.28     0.29
Footwear           0.20     0.21     0.22
-------------- -------- -------- --------
TOTAL DENVER       1.00     1.00     1.00

REPORT SUBTOTALS W 8 PERCENTAGE(units)

DISTRICT: DENVER
               ----PERCENTAGE(UNITS)-----
               ----------MONTH-----------
PRODUCT         Jul96    Aug96    Sep96
-------------- -------- -------- --------
Tents              0.03     0.02     0.02
Canoes             0.02     0.02     0.02
Racquets           0.13     0.12     0.12
Sportswear         0.10     0.09     0.09
Footwear           0.07     0.07     0.07
-------------- -------- -------- --------
TOTAL DENVER       0.35     0.32     0.33

Return Value

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

Syntax

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

Parameters

expression

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

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

dimname

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

dimexp

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

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

When the dimension being limited is a concat dimension, then dimname and dimexp can be one combination listed in Table 8-10, "Valid dimname and dimexp Combinations for Concat Dimensions".

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

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

Examples

LIMIT month TO 'Jan96' TO 'Jun96'
LIMIT line TO 'Cogs'
LIMIT division TO 'Sporting'
REPORT DOWN month W 11 MAX(actual,budget) W 11 actual W 11 budget

DIVISION: SPORTING
               ---------------LINE----------------
               ---------------COGS----------------
               MAX(ACTUAL,
MONTH            BUDGET)     ACTUAL      BUDGET
-------------- ----------- ----------- -----------
Jan96           287,557.87  287,557.87  279,773.01
Feb96           323,981.56  315,298.82  323,981.56
Mar96           326,184.87  326,184.87  302,177.88
Apr96           394,544.27  394,544.27  386,100.82
May96           449,862.25  449,862.25  433,997.89
Jun96           457,347.55  457,347.55  448,042.45

ALLSTAT
LIMIT month TO 'Jan96' TO 'Jun96'
REPORT HEADING 'For Cogs in Sporting Division' DOWN month -
   W 11 HEADING 'MAX(actual,budget)'-
   QUAL(MAX(actual,budget), line 'Cogs', division 'Sporting')

For Cogs in
Sporting       MAX(actual,
Division         budget)
-------------- -----------
Jan96           287,557.87
Feb96           323,981.56
Mar96           326,184.87
Apr96           394,544.27
May96           449,862.25
Jun96           457,347.55

REPORT HEADING 'For Cogs in Sporting Division' DOWN month -
   W 11 HEADING 'MAX(actual,budget)'-
   MAX(actual,budget) (line cogs, division sporting)

ERROR: A right parenthesis or an operator is expected after LINE.

LIMIT month TO 'Jan96' TO 'Jun96'
LIMIT product TO 'Tents' 'Canoes'
 
" Limit the concat by specifying one of its component dimensions
REPORT W 30 QUAL(rdsales * 2, month 'Feb96', district 'Boston')

                 QUAL(RDSALES * 2, MONTH
PRODUCT          'Feb96', DISTRICT 'Boston')
-------------- ------------------------------
Tents                               69,283.18
Canoes                             164,475.36
 
" Limit the concat by specifying one of its values
REPORT W 30 QUAL(rdsales * 2, month 'Mar96', reg.dist.ccdim  '<district: Boston>')
 
                  QUAL(RDSALES * 2, MONTH
                  'Mar96', REG.DIST.CCDIM
PRODUCT            '<district: Boston>')
-------------- ------------------------------
TENTS                               91,484.42
CANOES                             195,244.56

Return Value

DECIMAL

Syntax

RANDOM([lowbound] [highbound])

Parameters

lowbound

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

highbound

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

Examples

test = RANDOM(100 200)
REPORT test

PRODUCT           TEST
-------------- ----------
Tents              122.93
Canoes             176.69
Racquets           168.32
Sportswear         150.92
Footwear           187.46

Return Value

DECIMAL

Syntax

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

where attributes is one or more of the following:

RESET

NAFIRST

NALAST

LIMITSAVE (limit-expression...)

TIEBREAKERS (target-expression...)

Parameters

expression

The numeric expression for which rankings are to be computed.

method

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

Table 8-11 Methods for Computing RANK

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

RESET

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

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

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

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

NAFIRST

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

Note:

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

NALAST

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

Note:

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

LIMITSAVE

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

limit-expression

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

TIEBREAKERS

Specifies how Oracle OLAP sequences values of equal rank.

tiebreaker-expression

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

Note:

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

BASEDON dimension-list

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

Note:

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

Usage Notes

Monitoring the Behavior of RANK

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

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

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

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

Results of Method Values

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

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

The preceding statements produce the following output.

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

Table 8-12, "Results of Different Methods of Ranking" shows the results of the different methods of ranking that are produced by a statement of the form

REPORT DOWN geography RANK(sales2 MIN BASEDON items)

with the different method keywords substituted for MIN.

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

Examples

DEFINE geography DIMENSION TEXT 
MAINTAIN geography ADD 'g1' 'g2' 'g3'
DEFINE items DIMENSION TEXT
MAINTAIN items ADD 'Item1' 'Item2' 'Item3' 'Item4' 'Item5'
DEFINE sales2 DECIMAL <geography items>

               -------------SALES2-------------
               -----------GEOGRAPHY------------
ITEMS              g1         g2         g3
-------------- ---------- ---------- ----------
Item1               30.00      15.00      12.00
Item2               10.00      20.00      18.00
Item3               15.00      20.00      24.00
Item4               30.00      25.00      25.00
Item5                  NA       7.00      21.00

report rank(sales2 min basedon items)

               -RANK(SALES2 MIN BASEDON ITEMS)-
               -----------GEOGRAPHY------------
ITEMS              g1         g2         g3
-------------- ---------- ---------- ----------
Item1                1.00       4.00       5.00
Item2                4.00       2.00       4.00
Item3                3.00       2.00       2.00
Item4                1.00       1.00       1.00
Item5                  NA       5.00       3.00

REPORT RANK(sales2 MIN BASEDON geography)

               ----RANK(SALES2 MIN BASEDON-----
               -----------GEOGRAPHY)-----------
               -----------GEOGRAPHY------------
ITEMS              g1         g2         g3
-------------- ---------- ---------- ----------
Item1                1.00       2.00       3.00
Item2                3.00       1.00       2.00
Item3                3.00       2.00       1.00
Item4                1.00       2.00       2.00
Item5                  NA       2.00       1.00

REPORT RANK(sales2, MIN)

               -------RANK(SALES2, MIN)--------
               -----------GEOGRAPHY------------
ITEMS              g1         g2         g3
-------------- ---------- ---------- ----------
Item1                1.00      10.00      12.00
Item2               13.00       7.00       9.00
Item3               10.00       7.00       5.00
Item4                1.00       3.00       3.00
Item5                  NA      14.00       6.00

Returns

TEXT

Syntax

RAWTOHEX(raw-exp)

Parameters

raw-exp

A raw expression.

Return Value

INTEGER

Syntax

RECNO(fileunit)

Parameters

fileunit

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

Usage Notes

Opening Files

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

Using RECNO with FILEGET

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

LINENUM Option

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

Records in Text Files

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

Examples

DEFINE dim1 INTEGER DIMENSION
DEFINE textvar TEXT <dim1>
x = FILEOPEN('filename' R)
FILEREAD x APPEND dim1 = RECNO(x) W 8 TEXTVAR

Return value

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

Syntax

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

Parameters

source_char

The text expression for which the function searches.

pattern

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

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

See Also:

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

position

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

match_parameter

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

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

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

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

Return Value

When the pattern is found a NUMBER; otherwise 0.

Syntax

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

Parameters

source_char

The text expression for which the function searches.

pattern

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

position

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

occurrence

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

return_option

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

match_parameter

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

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

Examples

The following statement

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

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

Return Value

VARCHAR2

Syntax

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

Parameters

source_char

The text expression for which the function searches.

pattern

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

replace_string

The text that replaces pattern in source_char.

position

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

occurrence

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

match_parameter

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

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

Example

The following statement

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

eliminates extra spaces and returns the following string

500 Oracle Parkway, Redwood Shores, CA

Return Value

VARCHAR2 | CLOB

Syntax

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

Parameters

source_char

The text expression for which the function searches.

pattern

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

position

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

occurrence

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

match_parameter

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

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

Examples

REGEXP_SUBSTR('7 W 96th St, New York, New York', 'new york', 10, 2, 'i') 

REGEXP_SUBSTR('parsley, sage, rosemary, thyme', ',[^,]+,', 1) 

Return Value

DECIMAL

Syntax

REM(expression1 expression2)

Parameters

expression

REM returns the remainder of expression1 divided by expression2.

Examples

SHOW REM(14 5)

4.00

Return Values

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

Syntax

REMAINDER (diviidend, divisor)

Parameters

dividend

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

divisor

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

Examples

SHOW REMAINDER(18,7)
-3.00

Return Value

TEXT

Syntax

REMBYTES(text-expression start [length])

Parameters

text-expression

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

Tip:

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

start

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

length

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

Examples

SHOW REMBYTES('hellotherejoe', 6, 5)

hellojoe

Return Value

TEXT or NTEXT

Syntax

REMCHARS(text-expression start [length])

Parameters

text-expression

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

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

start

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

length

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

Examples

SHOW REMCHARS('hellotherejoe', 6, 5)

hellojoe

SHOW REMCHARS('hellotherejoe', 6)

helloherejoe 

Return Value

TEXT or NTEXT

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

Syntax

REMCOLS(text-expression start [length])

Parameters

text-expression

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

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

start

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

length

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

Examples

DEFINE citylist VARIABLE TEXT
CITYLIST = 'Boston\nHouston\nChicago\nDenver'

SHOW citylist

Boston
Houston
Chicago
Denver

SHOW REMCOLS(citylist 2 4)

Bn
Hon
Cgo
Dr 

Return Value

TEXT or NTEXT

Syntax

REMLINES(text-expression start [length])

Parameters

text-expression

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

start

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

length

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

Examples

Salespeople
Products
Services

SHOW REMLINES(mktglist, 2)

Salespeople
Services 

Return Value

The same data type as char.

Syntax

REPLACE ( char, search_string [, replacement_string ])

Parameters

char

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

search_string

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

replacement_string

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

Return Value

TEXT

Syntax

REPLBYTES(text-expression replacement [start])

Parameters

text-expression

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

Tip:

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

replacement

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

start

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

Examples

SHOW REPLBYTES('Hello there, Joe', 'Jane', 14)

Hello there, Jane

var1 = JOINLINES('Hello' 'there' 'Joe')
var2 = JOINLINES('Hi' 'Jane')

SHOW REPLBYTES(var1 var2)

Hi
Janehere
Joe

var2 = JOINLINES('Hi' 'Jane       ')

SHOW REPLBYTES(var1 var2)

Hi
Jane 

Return Value

TEXT or NTEXT

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

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

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

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

Syntax

REPLCHARS(text-expression characters [start])

Parameters

text-expression

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

characters

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

start

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

Examples

SHOW REPLCHARS('Hello there, Joe', 'Jane', 14)

Hello there, Jane

var1 = JOINLINES('Hello' 'there' 'Joe')
var2 = JOINLINES('Hi' 'Jane')

show REPLCHARS(var1 var2)

Hi
Janehere
Joe

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

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

var2 = JOINLINES('Hi' 'Jane       ')

The statement

SHOW REPLCHARS(var1 var2)

produces the following output.

Hi
Jane 

Return Value

TEXT or NTEXT

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

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

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

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

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

Syntax

REPLCOLS(text-expression columns [start])

Parameters

text-expression

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

columns

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

start

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

Examples

SHOW citylist

Boston
Houston
Chicago
Denver

SHOW cityreps

Brady
Lopez
Alfonso
Cody

SHOW REPLCOLS(citylist cityreps 11)

Boston    Brady
Houston   Lopez
Chicago   Alfonso
Denver    Cody 

Return Value

TEXT or NTEXT

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

Syntax

REPLLINES(text-expression lines [start])

Parameters

text-expression

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

lines

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

start

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

Examples

Salespeople
Products
Services

SHOW REPLLINES(mktglist, 'advertising', 2)

Salespeople
Advertising
Services 

Return Value

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

Syntax

RESERVED [(word-expression)]

Parameters

word-expression

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

Usage Notes

Other Reserved Words

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

AW ATTACH EXPRESS
LISTNAMES 

NA is Reserved

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

Case-Sensitivity

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

Examples

SHOW RESERVED('update')

YES

Return Value

TEXT

Syntax

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

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

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

The ROW function without any arguments returns a blank line.

Usage Notes

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

Examples

textvar = ROW(OVER '-' UNDER '=' 'This is a Row.')
SHOW textvar

--------------
This is a Row.
==============

LIMIT month TO 'Jan95' 'Feb95'
LIMIT district TO 'Boston' 'Atlanta' 'Chicago'
textvar = NA
textvar = ROW(W 8 SKIP ACROSS month: <month SKIP>)
textvar = JOINLINES(textvar ROW(W 8 SKIP ACROSS month: -
   CENTER <'Sales' 'Plan'>))
FOR district
textvar = JOINLINES(textvar ROW(W 8  district ACROSS month: -
   <sales sales.plan>))
SHOW textvar

OUTPUT:
         Jan95                 Feb95
          Sales       Plan      Sales       Plan
Boston   32,153.52  42,346.89  32,536.30  43,265.50
Atlanta  40,674.20  54,583.41  44,236.55  57,559.87
Chicago  29,098.94  36,834.37  29,010.20  37,667.66

Return Value

TEXT

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

Syntax

ROWIDTOCHAR (rowid)

Parameters

rowid

The value of type ROWID to convert.

Examples

DEFINE myrowid DIMENSION ROWID
DEFINE mytext VARIABLE TEXT
DEFINE myntext VARIABLE NTEXT

MAINTAIN myrowid ADD CHARTOROWID('AAAFd1AAFAAAABSAA/')
mytext = ROWIDTOCHAR (myrowid)
myntext = ROWIDTONCHAR (myrowid)
 
MYROWID
------------------------------
AAAFd1AAFAAAABSAA/

MYTEXT
------------------------------
AAAFd1AAFAAAABSAA/
 
MYNTEXT
------------------------------
AAAFd1AAFAAAABSAA/

Return Value

NCHAR

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

Syntax

ROWIDTONCHAR (rowid)

Parameters

rowid

The value of type ROWID to convert.

Examples

See Example 8-91, "Converting ROWID Data to TEXT or NTEXT Data".

Return Value

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

Syntax

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

Parameters

text-exp

A text expression to pad.

length

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

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

pad-exp

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

Examples

SHOW RPAD('Morrison',12,'ab')
Morrisonabab

Return Value

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

Syntax

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

Parameters

text-exp

A text expression that you want trimmed.

trim-exp

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

Examples

SHOW RTRIM('Last Wordxxyxy','xy') 
Last Word

Return Value

DECIMAL

Syntax

RUNTOTAL(n)

Parameters

n

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

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

Usage Notes

How RUNTOTAL Works

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

Accessing Data from Another Column

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

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

Resetting Subtotals

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

NA Values and RUNTOTAL

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

Examples

ZEROTOTAL ALL
ROW W 4 R 2 RUNTOTAL(1) + COLVAL(1)
ROW W 4 R 5 RUNTOTAL(1) + COLVAL(1)
ROW W 4 R 3 RUNTOTAL(1) + COLVAL(1)

2     2.00
5     7.00
3    10.00

LIMIT product TO 'Tents'
LIMIT month TO 'Jan96' TO 'Jun96'
LIMIT district TO 'Boston' 'Chicago'
REPORT ACROSS district: units -
   DECIMAL 0 TOTAL(units, month)+RUNTOTAL(1)

PRODUCT: TENTS
               --------UNITS--------
               ------DISTRICT-------
                                     TOTAL(UNIT
                                         S,
                                     MONTH)+RUN
MONTH            Boston    Chicago    TOTAL(1)
-------------- ---------- ---------- ----------
Jan96                 307        189        496
Feb96                 209        190        895
Mar96                 277        257      1,429
Apr96                 372        318      2,119
May96                 525        433      3,077
Jun96                 576        466      4,119

Return Values

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

Syntax

SESSIONTIMEZONE

Examples

SHOW SESSIONTIMEZONE
-05:00

Return Value

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

Syntax

SIGN (n)

Parameters

n

A numeric expression.

Examples

SHOW SIGN(-15) 
 -1

Return Value

NUMBER

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

Syntax

SIN(angle-expression)

Parameters

angle-expression

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

Examples

DECIMALS = 5
SHOW SIN(1)

0.84147

SHOW SIN(30 * 2 * 3.14159 / 360)

0.50000

Return Value

NUMBER

Syntax

SINH(expression)

Parameters

expression

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

Examples

DECIMALS = 5
SHOW SINH(1)

1.17520

Return Value

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

Syntax

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

Parameters

expression

The expression whose smallest value is to be returned.

CACHE

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

dimension

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

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

Tip:

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

Usage Notes

NA Values and SMALLEST

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

Examples

LIMIT product TO 'Sportswear'
LIMIT district TO FIRST 3
LIMIT month TO 'Jan96' TO 'Jun96'
REPORT HEADING 'Smallest Sales' SMALLEST(sales district)

                Smallest
DISTRICT         Sales
-------------- ----------
Boston          57,079.10
Atlanta        129,616.08
Chicago         77,489.51

Return Value

DECIMAL

Syntax

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

Parameters

expression

The numeric expression whose values are to be smoothed.

SINGLE

DOUBLE

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

alpha

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

beta

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

m

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

BASEDON dimension-list

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

Usage Notes

The Effect of NASKIP on SMOOTH

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

Results of alpha Values

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

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

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

Examples

LIMIT month TO 'Jan96' TO 'Dec96'
LIMIT product TO 'Tents'
LIMIT district TO 'Boston'
COLWIDTH = 14

REPORT W 6 DOWN month sales

DISTRICT: Boston
       ----SALES-----
       ---PRODUCT----
MONTH      Tents
------ --------------
Jan96       50,808.96
Feb96       34,641.59
Mar96       45,742.21
Apr96       61,436.19
...
Nov96       46,124.99
Dec96       36,938.27

REPORT W 6 DOWN month SMOOTH(sales, SINGLE, .5, BASEDON month)

DISTRICT: Boston
       SMOOTH(SALES,-
       -SINGLE, .5,--
       BASEDON MONTH)
       ---PRODUCT----
MONTH      Tents
------ --------------
Jan96              NA
Feb96       50,808.96
Mar96       42,725.28
Apr96       44,233.74
...
Nov96       77,031.66
Dec96       61,578.33

Return value

The return value varies depending on the use of the function:

• When the SORT function is an argument to an OLAP DML statement (including a user-defined command or function) that expects a valueset, it returns a valueset. When the SORT function returns an empty valueset, it returns it as a valueset with null status.

• When you include the INTEGER keyword, the SORT function returns the position numbers of the values as INTEGERS.

• In all other cases, the SORT function returns either a TEXT value. When it returns a TEXT value that represents empty status, it returns the value as NA.

Syntax

SORT([INTEGER]dimension [byhierarchy] [bycriterion...])

where:

• byhierarchy is an optional phrase that uses a parent relation to arrange the order of values in the current status list of a hierarchical dimension or its dimension surrogate, or to assign values to a valueset, based on family relationships within the hierarchy. You can include only one byhierarchy phrase in a SORT statement. It must be the first phrase in a SORT statement.

  HIERARCHY parent-relation [INVERT] [DEPTH n] [SORTORPHANS]

• bycriterion uses an explicit criterion to arrange the order of values in the current status list of a dimension or its dimension surrogate, or to assign values to a valueset. You can include as many bycriterion phrases as you want in a SORT statement.

  {A|D} [NAFIRST] criterion

Parameters

See the SORT command for a complete description of all arguments except INTEGER.

INTEGER

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

Return Value

TEXT or NTEXT

Syntax

SORTLINES(text-expression [A|D])

Parameters

text-expression

A multiline text expression whose lines SORTLINES sorts. When you specify a TEXT expression, the return value is TEXT. When you specify an NTEXT expression, the return value is NTEXT.

A

D

Specifies whether the sorting order should be ascending, or alphabetical (A), or descending, or reverse alphabetical (D). The default is A (ascending). The sort order is controlled by the NLS_SORT option.

Examples

SHOW mktregions

New York
Boston
Atlanta
San Francisco

SHOW SORTLINES(mktregions)

Atlanta
Boston
New York
San Francisco 

Return Value

The same data type as text_exp.

Syntax

SOUNDEX(text_exp)

Parameters

text_exp

The expression for which you want a phonetic representation. It can be any of the text data types.

Return value

TEXT

Syntax

SQLFETCH (cursorname [COLUMN column-number} {STOPAFTER number-of-rows])

Parameters

cursorname

A TEXT expression that is the name of the SQL cursor. The cursor must be opened and closed by the caller.

column-number

An INTEGER value that is the number of the column. (Default is 1.)

number-of-rows

The number of rows to read. (Default is all remaining rows).

Return Value

DECIMAL

Syntax

SQRT(expression)

Parameters

expression

The numeric expression whose square root is to be computed.

Usage Notes

Negative Expressions

When expression is negative and ROOTOFNEGATIVE is set to NO, an error occurs. When expression is negative and ROOTOFNEGATIVE is set to YES, SQRT returns the value NA.

Examples

SHOW SQRT(144)

12.00

Return Value

DATE or text

Syntax

STARTOF(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.

Usage Notes

How STARTOF Works

STARTOF returns the first date of the time period that is first in the current status list of the dimension.

Phased or Multiple Periods

STARTOF is particularly useful when the dimension has a phase that differs from the default or when the time periods are formed from multiple weeks or years. For example, when the dimension has four-week time periods, the STARTOF function identifies the starting date of a particular four-week period.

Format of the Result Returned by STARTOF

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

Retrieving the Last Valid Date of a Time Period

The ENDOF function, which returns the last date of a time period.

Examples

DEFINE fyear DIMENSION YEAR ENDING June
VNF 'FY<ff>'
MAINTAIN fyear ADD '30JUN96' '30JUN98'
REPORT W 14 STARTOF(fyear)

FYEAR          STARTOF(FYEAR)
---------      --------------
FY96           01JUL95
FY97           01JUL96
FY98           01JUL97

Return Value

BOOLEAN

Syntax

STATALL(dimension)

Parameters

dimension

A text expression whose value is the name of a dimension or dimension surrogate.

Usage Notes

STATALL Compared to STATLIST

STATALL provides an alternative to running the STATLIST program to determine if the status of a specified dimension is ALL.

Examples

SHOW STATALL(month)

Return Value

The data type of the returned value varies depending on the data type of the dimension.

Syntax

STATCURR (dimension, integer)

Parameters

dimension

The name of the dimension for which the function searches for status lists.

integer

Specifies the position in the status list stack of the status list the function returns.

Value	Specifies
0	The current value of the dimension.
-1	Returns the current value of status for the previous status list in the status list stack for the dimension.
1	Returns the current value of the status list on the bottom of the status list stack for the dimension.