# 6 OLAP DML Functions: A - K

This chapter contains the following topics:

Reference topics for the remaining OLAP DML functions appear in alphabetical order in Chapter 7, "OLAP DML Functions: L - Z".

For other OLAP DML reference topics, see Chapter 4, "OLAP DML Properties", Chapter 5, "OLAP DML Options", Chapter 8, "OLAP DML Commands: A-G", and Chapter 9, "OLAP DML Commands: H-Z".

OLAP functions work in much the same way as functions work in other programming languages. They initiate action and return a value. The one exception is the looping nature of OLAP DML functions as discussed in "OLAP DML Statements Apply to All of the Values of a Data Object".

Most of the OLAP DML functions are standard text and calculation functions. Other OLAP DML functions return more complex information. For example, the OLAP DML provides the AW and OBJ functions that you can use to retrieve many different types of information about an analytic workspace and its objects and the AGGREGATE function that you can use to calculate aggregate data on-the-fly at user request.

Additionally, you can augment the functionality of the OLAP DML by writing an OLAP DML program for use as a function.

Tip:

Many OLAP DML statements can be coded as a 3-character abbreviation that consists of the first letter of the statement plus the next two consonants.

## ABS

The ABS function calculates the absolute value of an expression. Since, the absolute value of a real number is its numeric value without regard to its sign (for example, 3 is the absolute value of both 3 and -3), this function always returns a positive value.

Return Value

DECIMAL.

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

Syntax

ABS(expression)

Arguments

expression

The expression whose absolute value is to be calculated.

Examples

Example 6-1 Finding Values in an Absolute Range

Suppose you are interested in how close your planned 1996 sales figures for sportswear in Boston were to the actual sales. You would like to see those months where budgeted figures are off by more than \$5,000 in either direction. You can use ABS to help you find those months.

```LIMIT product TO 'Sportswear'
LIMIT district TO 'Boston'
LIMIT month TO YEAR 'Yr96'
LIMIT month KEEP ABS(sales - sales.plan) GT 5000
REPORT DOWN month sales sales.plan sales - sales.plan
```

These statements produce the following output.

```DISTRICT: BOSTON
------------PRODUCT-------------
-----------SPORTSWEAR-----------
SALES -
MONTH            SALES    SALES.PLAN SALES.PLAN
-------------- ---------- ---------- ----------
Jun96           79,630.20  73,568.52   6,061.68
Jul96           95,707.30  80,744.18  14,963.12
Aug96           82,004.00  71,811.45  10,192.55
Sep96           89,988.60  78,282.07  11,706.53
Dec96           50,281.40  56,720.87  -6,439.47
```

The ADD_MONTHS function returns the date that is n months after the specified date.

Return Value

DATETIME

Syntax

Arguments

start_datetime

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

n

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

Examples

Example 6-2 End-of-Month Calculation

The following statement displays the date of the day that is one month after January 30, 2000.

```SHOW ADD_MONTHS('30Jan00', 1)
```

Since February 29 was the last day of February 2000, ADD_MONTHS returns February 29, 2000.

```29-Feb-00
```

## AGGCOUNT

The AGGCOUNT function retrieves the values of the Aggcount variable associated with the specified variable. An Aggcount variable is an `INTEGER` variable that Oracle OLAP automatically creates when it creates a variable defined using a DEFINE VARIABLE statement that includes a USING AGGOUNT phrase.

Return Value

`INTEGER`

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

Syntax

AGGCOUNT(variable-name)

Arguments

variable-name

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

Examples

Example 6-3 Reporting on an Aggcount Variable

Assume that within your analytic workspace you have objects with the following definitions.

```DEFINE geog DIMENSION TEXT
DEFINE time DIMENSION TEXT
DEFINE product DIMENSION TEXT
DEFINE cc_geog_product COMPOSITE <geog product> COMPRESSED
DEFINE sales VARIABLE DECIMAL <time cc_geog_product <geog product>> WITH AGGCOUNT
DEFINE geog_parentrel RELATION geog <geog>
DEFINE product_parentrel RELATION product <product>
DEFINE time_parentrel RELATION time <time>
DEFINE aggsales AGGMAP
AGGMAP
RELATION time_parentrel OPERATOR AVERAGE ARGS COUNT YES
RELATION geog_parentrel
RELATION product_parentrel
END

```

Notice that the definition for the `sales` variable includes a request for an Aggcount variable and that, within the `aggsales` aggmap, the RELATION statement for the `time_parentrel` relation incudes an `AVERAGE` operator.

Assume also that when only the base values of the `sales` variable are populated, sales has the following values for Radios and TVs.

```REPORT sales

-------------SALES--------------
--------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine              122.93     176.69         NA
California         168.32     150.92         NA
Quebec                 NA         NA         NA
Ontario            187.46     164.46         NA
USA                    NA         NA         NA
World                  NA         NA         NA

PRODUCT: TV
-------------SALES--------------
--------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine              184.75     135.40         NA
California         139.89     145.71         NA
Quebec                 NA         NA         NA
Ontario            123.63     113.32         NA
USA                    NA         NA         NA
World                  NA         NA         NA

PRODUCT: AV
-------------SALES--------------
--------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine                  NA         NA         NA
California             NA         NA         NA
Quebec                 NA         NA         NA
Ontario                NA         NA         NA
USA                    NA         NA         NA
World                  NA         NA         NA

```

Since no aggregation has occurred, for AV, Oracle OLAP has not yet populated the Aggcount variable and the Aggcount variable for `sales` contains only `NA` values.

Now assume that you aggregate the `sales` variable by issuing the following statement.

```AGGREGATE sales USING aggsales
```

A report of `sales` shows the following values.

```REPORT sales

-------------SALES--------------
--------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine              122.93     176.69     149.81
California         168.32     150.92     159.62
Quebec                 NA         NA         NA
Ontario            187.46     164.46     175.96
USA                291.24     327.61     309.42
World              478.70     492.07     485.38

PRODUCT: TV
-------------SALES--------------
--------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine              184.75     135.40     160.07
California         139.89     145.71     142.80
Quebec                 NA         NA         NA
Ontario            123.63     113.32     118.47
USA                324.64     281.11     302.87
World              448.27     394.42     421.35

PRODUCT: AV
-------------SALES--------------
--------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine              307.67     312.08     309.88
California         308.21     296.63     302.42
Quebec                 NA         NA         NA
Ontario            311.09     277.78     294.43
USA                615.88     608.71     612.30
World              926.97     886.49     906.73
```

A report of the Aggcount variable shows that it is populated with the `INTEGER` values that are needed to aggregate the average sales.

```REPORT AGGCOUNT (sales)

--------AGGCOUNT (SALES)--------
--------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine                  NA         NA          2
California             NA         NA          2
Quebec                 NA         NA         NA
Ontario                NA         NA          2
USA                     2          2          4
World                   3          3          6

PRODUCT: TV
--------AGGCOUNT (SALES)--------
--------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine                  NA         NA          2
California             NA         NA          2
Quebec                 NA         NA         NA
Ontario                NA         NA          2
USA                     2          2          4
World                   3          3          6

PRODUCT: AV
--------AGGCOUNT (SALES)--------
--------------TIME--------------
GEOG              2004       2005     Average
-------------- ---------- ---------- ----------
Maine                   2          2          4
California              2          2          4
Quebec                 NA         NA         NA
Ontario                 2          2          4
USA                     4          4          8
World                   6          6         12
```

## AGGMAPINFO

The AGGMAPINFO function returns information about the specification of an aggmap object in your analytic workspace.

You can get information about an aggregation specification (that is, an aggmap object with a map type of AGGMAP) only after it has been compiled. You can compile an aggregation specification using a COMPILE statement or by including the FUNCDATA keyword when you execute the AGGREGATE command. When an aggregation specification has not been compiled before you use it with the AGGMAPINFO function, then it is compiled by AGGMAPINFO. You do not have to compile an aggmap for use with ALLOCATE.

Return Value

Varies depending on the type of information that is requested. See Table 6-1, "Keywords for the choice Parameter of the AGGMAPINFO function" for more information.

Syntax

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

Arguments

name

The name of the aggmap object.

choice

Specifies the type of information returned. See Table 6-1, "Keywords for the choice Parameter of the AGGMAPINFO function", for details.

Table 6-1 Keywords for the choice Parameter of the AGGMAPINFO function

Keyword Data Type Description

`TEXT`

The models that are currently added to an aggmap using AGGMAP ADD or REMOVE model statements.The names of the models are returned as a multi-line text string.

AGGINDEX

`BOOLEAN`

Indicates the setting for the AGGINDEX statement in the aggmap. A `YES` setting specifies that all possible indexes (composite tuples) are created whenever the aggmap is recompiled. (Applies to AGGMAP type aggmaps only.)

CHILDREN member-name

`TEXT`

The dimension members used in the right-hand side of equations used to calculate temporary calculated members added using MAINTAIN ADD SESSION statements. The names of the members are returned as a multi-line text string.

CUSTOMMEMBERS

`TEXT`

The members added using MAINTAIN ADD SESSION statements. The names of the members are returned as a multi-line text string.

DIMENSION

`TEXT`

The names of the dimensions of the models or relations used by the aggmap. The names of the members are returned as a multi-line text string.

FCACHE

`BOOLEAN`

Indicates whether Oracle OLAP has a cache for the AGGREGATE function. (Applies to AGGMAP type aggmaps only.)

MAPTYPE

`TEXT`

The type of the aggmap.

• Returns AGGMAP for an aggregation specification (that is, when the specification has been entered with an AGGMAP statement). You can use this type of aggmap only with the AGGREGATE command or AGGREGATE function.

• Returns ALLOCMAP for an allocation specification (that is, when the specification has been entered with an ALLOCMAP statement). You can use this type of aggmap only with ALLOCATE.

• Returns `NA` when the aggmap has been defined but a specification has not been entered with an AGGMAP or ALLOCMAP statement.

MODELS

`TEXT`

The models in the aggmap. The names of the models are returned as a multi-line text string.

NUMRELS

`INTEGER`

The total number of RELATION statements in an aggmap specification.

RELATIONS

`TEXT`

The name of relation that is specified by a RELATION statement in the aggmap specification. Each statement is displayed on a separate line.

STORE

`BOOLEAN`

Indicates whether the CACHE statement in the aggmap is set to STORE. A `YES` setting specifies that the data that is calculated on the fly is stored in the cache. (Applies to AGGMAP-type aggmaps only.)

VARIABLES

`TEXT`

The variables for which this aggmap object has been specified as the default aggmap using AGGMAP ADD or REMOVE model statements or the \$AGGMAP property. The names of the variables are returned as a multi-line text string.

choice-at-position

Specifies the exactly which piece of information you want returned.

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

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

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

rel-pos

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

Examples

Example 6-4 Retrieving Information About an Aggmap Object

Suppose an aggmap named `sales.agg` has been defined with the following statement.

```DEFINE sales.agg AGGMAP <time, product, geography>
```

Suppose the following specification has been added to `sales.agg` with an AGGMAP statement.

```AGGMAP
RELATION time.r PRECOMPUTE (time ne 'Year98')
RELATION product.r
RELATION geography.r
CACHE STORE
END
```

Once a specification has been added to the aggmap, you can use AGGMAPINFO to get information about its specification.

To see the names of the hierarchies that are specified by the RELATION statements, use the following statement.

```SHOW AGGMAPINFO(sales.agg RELATIONS)
```

The following results are displayed.

```time.r
product.r
geography.r
```

The following statement and result tell you how many RELATION statements are in the aggmap object.

```SHOW AGGMAPINFO(sales.agg NUMRELS)
3
```

The following statement and result verifies that data that is calculated on the fly is stored in the cache for the session. The result is `YES` because the aggmap contains a CACHE STORE statement.

```show AGGMAPINFO(sales.agg STORE)
YES
```

The following statement displays the relation name that is specified in the second RELATION statement in the aggmap.

```SHOW AGGMAPINFO(sales.agg RELATION 2)
product.r
```

The following statement displays the limit clause that follows the PRECOMPUTE keyword in the first RELATION statement in the aggmap.

```SHOW AGGMAPINFO(sales.agg PRECOMPUTE 1)
time NE 'YEAR98'
```

Suppose the `time` dimension values are `Jan98` to `Dec99`, `Year98`, and `Year99`. The following statement displays the status list for the dimension in the first RELATION statement in the aggmap.

```SHOW AGGMAPINFO(sales.agg STATUS 1)
Jan98 TO Dec99, Year99
```

Because the limit clause in the RELATION statement specifies that the `time` dimension values should not equal `Year98`, all `time` dimension values other than `Year98` are included in its status.

The following statement displays the aggmap type of `sales.agg`.

```SHOW AGGMAPINFO(sales.agg MAPTYPE)
AGGMAP
```

## AGGREGATE function

The AGGREGATE function calculates the data in the variable that is not specified as PRECOMPUTE in the specified aggmap. (For information about specifying precompute data, see PRECOMPUTE statement and the PRECOMPUTE clause of the RELATION (for aggregation) statement.) The aggregation is limited to those values that are currently in status.

Note:

When the variable you want to aggregate has an \$AGGMAP property, you do not have to explicitly use the AGGREGATE function to aggregate nonprecomted data in that variable.

Return Value

The same data type as the aggregated variable.

Syntax

AGGREGATE (var ... [USING aggmap] -

[FROM fromspec|FROMVAR textvar] [FORCECALC FORCEORDER] [COUNTVAR countvar])

Arguments

var

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

USING

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

aggmap

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

FROM

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

fromspec

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

FROMVAR

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

textvar

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

FORCECALC

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

Note:

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

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

Note:

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

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

Note:

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

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

Notes

Steps for Supporting Run-Time Calculations

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

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

2. Execute the AGGREGATE command with the FUNCDATA argument.

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

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

5. UPDATE and COMMIT the analytic workspace.

Compiling the Aggmap

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

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

Run-Time Changes to Data Values

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

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

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

Using NA Values to Trigger Run-Time Calculations

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

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

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

Using the AGGREGATE Function after Partial Rollups

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

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

2. Execute the AGGREGATE command with the FUNCDATA keyword.

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

Using Multiple Aggmaps

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

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

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

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

Examples

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

Example 6-5 Using the AGGREGATE Function as the Formula of an Expression

Example 8-31, "Capstone Aggregation" illustrates performing the final capstone aggregation using an AGGREGATE command. You could also perform the capstone aggregation at run time as the expression of a formula.

Assume that your analytic workspace contains the following object definitions.

```DEFINE GEOG.D DIMENSION TEXT
DEFINE GEOG.PARENTREL RELATION GEOG.D <GEOG.D>
DEFINE TIME.D DIMENSION TEXT
DEFINE TIME.PARENTREL RELATION TIME.D <TIME.D>
DEFINE SALES_JAN76 VARIABLE INTEGER <GEOG.D>
DEFINE SALES_FEB76 VARIABLE INTEGER <GEOG.D>
DEFINE SALES_MAR76 VARIABLE INTEGER <GEOG.D>
DEFINE SALES_CAPSTONE76 VARIABLE INTEGER <GEOG.D TIME.D>
DEFINE CAPSTONE_SOURCE VARIABLE TEXT <TIME.D>
```

Now you create two aggmap objects with the following definitions. Note that in this case the `capstone_aggmap` consists of a RELATION statement with a `PRECOMPUTE NA` clause.

```DEFINE LEAF_AGGMAP AGGMAP
AGGMAP
RELATION geog.parentrel OPERATOR SUM
END

DEFINE CAPSTONE_AGGMAP AGGMAP
AGGMAP
RELATION time.parentrel OPERATOR SUM PRECOMPUTE (NA)
END
```

In Example 8-31, "Capstone Aggregation", the final capstone aggregation is performed using an AGGREGATE command. In this example, the capstone aggregation is defined as a formula named `f_sales_capstone76` that has an AGGREGATE function as the expression of the formula.

```DEFINE F_SALES_CAPSTONE76 FORMULA INTEGER <GEOG.D TIME.D>
EQ AGGREGATE ( sales_capstone76 USING capstone_aggmap fromvar capstone_source)

```

When you report on the unaggregated variables and formulas in your analytic workspace, you see the following results.

```GEOG.D          SALES_JAN76    SALES_FEB76    SALES_MAR76
-------------- -------------- -------------- --------------
Boston                  1,000          2,000          3,000
Medford                 2,000          4,000          6,000
San Diego               3,000          6,000          9,000
Sunnydale               4,000          8,000         12,000
Massachusetts              NA             NA             NA
California                 NA             NA             NA
United States              NA             NA             NA

--------------------F_SALES_CAPSTONE76---------------------
--------------------------TIME.D---------------------------
GEOG.D             Jan76          Feb76          Mar76           76Q1
-------------- -------------- -------------- -------------- --------------
Boston                  1,000          2,000          3,000          6,000
Medford                 2,000          4,000          6,000         12,000
San Diego               3,000          6,000          9,000         18,000
Sunnydale               4,000          8,000         12,000         24,000
Massachusetts              NA             NA             NA             NA
California                 NA             NA             NA             NA
United States              NA             NA             NA             NA

---------------------SALES_CAPSTONE76----------------------
--------------------------TIME.D---------------------------
GEOG.D             Jan76          Feb76          Mar76           76Q1
-------------- -------------- -------------- -------------- --------------
Boston                  1,000          2,000          3,000             NA
Medford                 2,000          4,000          6,000             NA
San Diego               3,000          6,000          9,000             NA
Sunnydale               4,000          8,000         12,000             NA
Massachusetts              NA             NA             NA             NA
California                 NA             NA             NA             NA
United States              NA             NA             NA             NA

```

Now you aggregate the leaf variables using the following AGGREGATE statement.

```AGGREGATE sales_jan76 sales_feb76 sales_mar76 USING leaf_aggmap
```

A report of the leaf variables shows that they are aggregated.

```GEOG.D          SALES_JAN76    SALES_FEB76    SALES_MAR76
-------------- -------------- -------------- --------------
Boston                  1,000          2,000          3,000
Medford                 2,000          4,000          6,000
San Diego               3,000          6,000          9,000
Sunnydale               4,000          8,000         12,000
Massachusetts           3,000          6,000          9,000
California              7,000         14,000         21,000
United States          10,000         20,000         30,000
```

A report of the `f_sales_capstone76` formula shows the aggregated values for `76Q1`.

```--------------------F_SALES_CAPSTONE76---------------------
--------------------------TIME.D---------------------------
GEOG.D             Jan76          Feb76          Mar76           76Q1
-------------- -------------- -------------- -------------- --------------
Boston                  1,000          2,000          3,000          6,000
Medford                 2,000          4,000          6,000         12,000
San Diego               3,000          6,000          9,000         18,000
Sunnydale               4,000          8,000         12,000         24,000
Massachusetts           3,000          6,000          9,000         18,000
California              7,000         14,000         21,000         42,000
United States          10,000         20,000         30,000         60,000
```

While a report of the `sales_capstone76` variable does not show the aggregated values for `76Q1` since they are not stored in the variable.

```---------------------SALES_CAPSTONE76----------------------
--------------------------TIME.D---------------------------
GEOG.D             Jan76          Feb76          Mar76           76Q1
-------------- -------------- -------------- -------------- --------------
Boston                  1,000          2,000          3,000             NA
Medford                 2,000          4,000          6,000             NA
San Diego               3,000          6,000          9,000             NA
Sunnydale               4,000          8,000         12,000             NA
Massachusetts           3,000          6,000          9,000             NA
California              7,000         14,000         21,000             NA
United States          10,000         20,000         30,000             NA
```

Example 6-6 Aggregating Data on the Fly for a Report

The `units` variable is aggregated entirely on the fly using the `tp.agg` aggmap.

This is the object definitions for the variable `units`.

```DEFINE units VARIABLE INTEGER <time product>
```

The parent relation for `time` contains these values.

```---TIME.PARENTREL----
--TIME.HIERARCHIES---
TIME       STANDARD   YTD
---------- ---------- ----------
Jan01      Q1.01      Last.Ytd
Feb01      Q1.01      Last.Ytd
Mar01      Q1.01      Last.Ytd
Q1.01      2001       NA
```

The parent relation for the `product` dimension contains these values.

```PRODUCT.PA
PRODUCT    RENTREL
---------- ----------
Food       Na
Snacks     Food
Drinks     Food
Popcorn    Snacks
Cakes      Snacks
Soda       Drinks
Juice      Drinks
```

In the `units` variable, data is stored only at the lowest level of each dimension hierarchy.

```-------------------UNITS-------------------
-------------------TIME--------------------
PRODUCT     Jan01      Feb01      Mar01      Q1.01
----------- ---------- ---------- ---------- ----------
Food        NA         NA         NA         NA
Snacks      NA         NA         NA         NA
Drinks      NA         NA         NA         NA
Popcorn     2          2          4          NA
Cakes       4          4          2          NA
Soda        7          3          9          NA
Juice       1          3          2          NA
```

The aggmap specifies that all data is calculated on the fly.

```DEFINE tp.agg AGGMAP
LD <time product> Aggmap
AGGMAP
RELATION time.parentrel PRECOMPUTE (NA)
RELATION product.parentrel PRECOMPUTE (NA)
END
```

The following REPORT statement uses the AGGREGATE function to calculate the data.

```REPORT aggregate(units USING tp.agg)

-------AGGREGATE(UNITS USING TP.AGG)-------
-------------------TIME--------------------
PRODUCT     Jan01      Feb01      Mar01      Q1.01
----------- ---------- ---------- ---------- ----------
Food        17         18         20         55
Snacks       9         12          9         30
Drinks       8          6         11         25
Popcorn      2          2          4          8
Cakes        4          4          2         10
Soda         7          3          9         19
Juice        1          3          2          6
```

Example 6-7 Using \$NATRIGGER to Aggregate Data

When the AGGREGATE function is added to `units` in the \$NATRIGGER property, a simple REPORT statement displays aggregated results.

```CONSIDER units
PROPERTY '\$NATRIGGER' 'AGGREGATE(units USING tp.agg)'
REPORT units

-------------------UNITS-------------------
-------------------TIME--------------------
PRODUCT     Jan01      Feb01      Mar01      Q1.01
----------- ---------- ---------- ---------- ----------
Food        17         18         20         55
Snacks       9         12          9         30
```

Example 6-8 Calculating all but one Value on the Fly

The AGGREGATE function calculates the complement of the data specified in the PRECOMPUTE clause of the RELATION statement. It returns those values that are currently in status.

For example, when you are using an aggmap that contains this RELATION statement.

```RELATION letter.letter PRECOMPUTE ('AA')
```

Then the AGGREGATE function calculates all aggregations except `AA`, as shown here.

```REPORT AGGREGATE(units USING letter.aggmap)

AGGREGATE(UNITS
LETTER         USING LETTER.AGGMAP)
-------------- --------------------
A                                 3
AA                               NA
AB                                3
AAB                               2
ABA                               1
ABB                               2
AAAA                              1
AABA                              2
ABAA                              1
ABBB                              1
ABBA                              1
...
```

## AGGREGATION

Within a model, the AGGREGATION function allows you to create a model that represents a custom aggregate. Such an aggmap can be used for dynamic aggregation with the AGGREGATE function.

Note:

Because the AGGREGATION function is intended only for dynamic aggregation, a model that contains such a function cannot be used with the AGGREGATE command.

Syntax

AGGREGATION(dimval-list)

Arguments

dimval-list

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

Examples

Example 6-9 Using the AGGREGATION Function to Create a Custom Aggregate

The following lines of code from a program perform these steps:

1. Add the new dimension value `my_time` to the `time` dimension.

```MAINTAIN time ADD 'My_Time'
```
2. Define the model `mytime_custagg` and set the specification of the model using the AGGREGATION function.

```DEFINE mytime_custagg MODEL
MODEL JOINLINES('DIMENSION time' 'My_Time = AGGREGATION(\'23\' \'24\')')
```

(Note that backslash escape characters are required to include quotation marks within a quoted string.)

3. Define the `sales_aggmap` aggmap.

```DEFINE sales_aggmap AGGMAP <time cpc <customer product channel> >
AGGMAP
RELATION prntrel.time
RELATION prntrel.chan
RELATION prntrel.prod
RELATION prntrel.cust
END
```
4. Add the model `mytime_custagg` to `sales_aggmap`.

```AGGMAP ADD mytime_custagg TO sales_aggmap
```
5. Limit the dimensions to the values of interest and run a report.

```" Run a report
LIMIT time TO 'My_Time' '23' '24'
LIMIT channel TO '5'
LIMIT product TO '70'
LIMIT customer TO '114'
REPORT DOWN time AGGREGATE(sales USING sales_aggmap)
```

The report generates the following output.

```CHANNEL: 5
PRODUCT: 70
--AGGREGATE(SALES---
USING SALES_AGGMAP)-
------CUSTOMER------
TIME                   114
-------------- --------------------
my_time                  682,904.34
23                        84,982.92
24                       597,921.42
```

## AGGROPS

The AGGROPS function returns the keywords for all of the aggregation operators that you can specify in a RELATION (for aggregation) statement, listed one name on each line in a multiline text value.

Return Value

`TEXT`

Syntax

AGGROPS

Example

Example 6-10 Displaying a List of the Aggregation Operators

When you issue a a AGGROPS statement, Oracle OLAP returns a list of all of the aggregation operators.

```SHOW AGGROPS

SUM
WSUM
SSUM
AND
OR
FIRST
LAST
HFIRST
HLAST
AVERAGE
WAVERAGE
HAVERAGE
HWAVERAGE
MIN
MAX
WFIRST
WLAST
HWFIRST
HWLAST
WMIN
WMAX
NOAGG
```

## ALLOCOPS

The ALLOCOPS function returns the keywords for all of the allocation operators that you can specify in a RELATION (for allocation) statement, listed one name on each line in a multiline text value.

Return Value

`TEXT`

Syntax

ALLOCOPS

Examples

Example 6-11 Displaying a List of the Allocation Operators

When you issue a a ALLOCOPS statement, Oracle OLAP returns a list of all of the allocation operators.

```SHOW ALLOCOPS

FIRST
LAST
HFIRST
HLAST
MIN
MAX
EVEN
HEVEN
COPY
HCOPY
PROPORTIONAL
```

## ANTILOG

The ANTILOG function calculates the value of e (the base of natural logarithms) raised to a specific power.

Return Value

DECIMAL

Syntax

ANTILOG(n)

Arguments

n

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

Examples

Example 6-12 Calculating the Value of e Raised to the Second Power

The following function calculates the value of e raised to the second power.

```ANTILOG(2)
```

This function returns the following value.

```7.38906
```

## ANTILOG10

The ANTILOG10 function calculates the value of 10 raised to a specified power.

Return Value

DECIMAL

Syntax

ANTILOG10(n)

Arguments

n

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

Examples

Example 6-13 Calculating the Value of Ten Raised to the Third Power

The following function calculates the value of `10`  raised to the third power.

```ANTILOG10(3)
```

This function returns the following value.

```1,000.00
```

## ANY

The ANY function returns `YES` when any values of a Boolean expression are `TRUE`, or `NO` when none of the values of the expression are `TRUE`.

Return Value

BOOLEAN.

Syntax

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

Arguments

boolean-expression

The Boolean expression to be evaluated

CACHE

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

dimension

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

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

Tip:

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

Notes

The Effect of NASKIP on ANY

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

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

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

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

Examples

Example 6-14 Testing for Any True Values by District

Suppose you want to find out which districts had at least one month with sales greater than \$150,000 for sportswear. You use the ANY function to determine whether the Boolean expression (`sales GT 150000`) is `TRUE` for any month. To have the result dimensioned by `district`, specify `district` as the second argument in the ANY function.

```LIMIT product TO 'SPORTSWEAR'
REPORT HEADING 'High Sales' ANY(sales GT 150000, district)
```

The preceding statements produce the following output.

```DISTRICT       High Sales
-------------- ----------
Boston                 NO
Atlanta               YES
Chicago                NO
Dallas                YES
Denver                 NO
Seattle                NO
```

Example 6-15 Testing for Any True Values by Region

You might also want to find out which regions had at least one month in which at least one district had sportswear sales greater than \$150,000. Since the `region` dimension is related to the `district` dimension, you can specify `region` instead of `district` as a dimension for the results of ANY.

```report heading 'High Sales' any(sales gt 150000, region)
```

The preceding statement produces the following output.

```REGION         High Sales
-------------- ----------
East                  YES
Central               YES
west                   NO
```

## ARCCOS

The ARCCOS function calculates the angle value (in radians) of a specified cosine.

Return Value

NUMBER

Syntax

ARCCOS(expression)

Arguments

expression

An expression that contains the decimal value of a cosine.

Notes

Invalid Cosine Values

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

Examples

Example 6-16 Calculating the Arc of a Cosine

This example calculates the arc of a cosine that has a value of `0.54030`. The statement

```SHOW ARCCOS(.54030)
```

produces the following result.

```1.00
```

## ARCSIN

The ARCSIN function calculates the angle value (in radians) of a specified sine.

Return Value

NUMBER

Syntax

ARCSIN(expression)

Arguments

expression

An expression that contains the decimal value of a sine.

Notes

Invalid Sine Values

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

Examples

Example 6-17 Calculating the Arc of a Sine

This example calculates the arc of a sine that has a value of `0.84147`. The statement

```SHOW ARCSIN(.84147)
```

produces the following result.

```1.00
```

## ARCTAN

The ARCTAN function calculates the angle value (in radians) of a specified tangent.

To retrieve a full-range (`0` - `2` pi) numeric value indicating the arc tangent of a given ratio, use ARCTAN2.

Return Value

NUMBER

Syntax

ARCTAN(expression)

Arguments

expression

An expression that contains the decimal value of a tangent.

Examples

Example 6-18 Calculating the Arc of a Tangent

This example calculates the arc of a tangent that has a value of `1.56`. The statement

```SHOW ARCTAN(1.56)
```

produces the following result.

```1.00
```

## ARCTAN2

The ARCTAN2 function returns a full-range (`0` - `2` pi) numeric value indicating the arc tangent of a given ratio. The function returns values in the range of -pi to pi, depending on the signs of the arguments. The values are expressed in radians.

To calculate the angle value (in radians) of a specified tangent that is not a ratio, use ARCTAN.

Return Value

NUMBER

Syntax

ARCTAN2 (n / m)

Arguments

n

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

m

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

Examples

Example 6-19 Finding the Arc Tanget

The following example returns the arc tangent of`.3` and`.2`.

```SHOW ARCTAN2(.3/.2)

.982793723
```

## ARG

Within an OLAP DML program, the ARG function lets you reference arguments passed to a program. The function returns one argument as a text value.

Note:

Typically users use an ARGUMENT statement to define arguments in a program, thereby negating the need for using the ARG function to reference arguments passed to the program. For more information on how to use ARGUMENT to define arguments that are passed to a program, see "Declaring Arguments that Are Passed Into a Program" .

Return Value

TEXT

Syntax

ARG(n)

Arguments

n

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

Examples

Example 6-20 Assigning Arguments

Suppose you have a program that produces a sales report. You want to be able to produce this report for any two periods of months, so you do not want to limit the `month` dimension to any particular month in the program. Instead, you use ARG functions in the LIMIT command so that the starting and ending months for the two periods can be supplied as arguments when the program is run.

Notice the UPCASE function preceding the ARG functions. UPCASE allows the arguments to be specified in upper- or lowercase, even though dimension values in the analytic workspace are in uppercase. A prefixed `&` (ampersand) would have a similar effect since it tells Oracle OLAP to substitute the values of ARG before the LIMIT command is executed -- in this case, a value of the `month` dimension. However, an `&` (ampersand) has the disadvantage of preventing compilation of program lines in which it appears, and slower execution results.

```DEFINE salesrpt PROGRAM
PROGRAM
PUSH month product district
TRAP ON cleanup
LIMIT month TO UPCASE(ARG(1)) TO UPCASE(ARG(2))
LIMIT product TO 'CANOES'
LIMIT district TO all
REPORT grandtotals DOWN district sales
LIMIT month TO UPCASE(ARG(3)) TO UPCASE(ARG(4))
REPORT grandtotals DOWN district sales
cleanup:
POP month product district
END
```

To run the program, you specify the program name (`salesrpt`) followed by two sets of months to mark the beginning and the end of the two periods of sales to be reported. Then, when the `LIMIT MONTH` statements are executed, Oracle OLAP passes the months specified on the command line as return values for `ARG(1)`, `ARG(2)`, `ARG(3)`, and `ARG(4)` in the LIMIT commands.

```salesrpt 'Jan95' 'Mar95' 'Jan96' 'Mar96'
```

This statement produces the following output.

```PRODUCT: Canoes
------------SALES--------------
------------MONTH--------------
DISTRICT        Jan95       Feb95     Mar95
---------------------------------------------
Boston        66,013.92  76,083.84  91,748.16
Atlanta       49,462.88  54,209.74  67,764.20
Chicago       45,277.56  50,595.75  63,576.53
Dallas        33,292.32  37,471.29  43,970.59
Denver        45,467.80  51,737.01  58,437.11
Seattle       64,111.50  71,899.23  83,943.86
----------  ---------  ---------
303,625.98 341,996.86 409,440.44
========== ========== ==========
PRODUCT: Canoes
------------SALES---------------
------------MONTH---------------
DISTRICT        Jan96      Feb96       Mar96
---------------------------------------------
Boston        70,489.44  82,237.68  97,622.28
Atlanta       56,271.40  61,828.33  77,217.62
Chicago       48,661.74  54,424.94  68,815.71
Dallas        35,244.72  40,218.43  46,810.68
Denver        44,456.41  50,623.19  57,013.01
Seattle       67,085.12  74,834.29  87,820.04
----------  ---------  ---------
322,208.83 364,166.86 435,299.35
========== ========== ==========
```

## ARGCOUNT

Within an OLAP DML program, the ARGCOUNT function returns the number of arguments that were specified when the current program was invoked.

Return Value

`INTEGER`

Syntax

ARGCOUNT

Examples

Example 6-21 Checking the Number of Arguments

In the following example, the program, a user-defined function, verifies that three arguments are passed. When the number of arguments passed is not equal to 3, the program terminates with `-1` as a return value.

```DEFINE threearg PROGRAM INTEGER
LD User-defined function expecting three arguments
PROGRAM
ARGUMENT division TEXT
ARGUMENT product TEXT
ARGUMENT month MONTH
IF ARGCOUNT NE 3
THEN RETURN -1
ELSE
DO
...
```

## ARGFR

Within an OLAP DML program, the ARGFR function lets you reference the arguments that are passed to a program. The function returns a group of one or more arguments, beginning with the specified argument number, as a single text value. You can use ARGFR only within a program that is invoked as a command, not as a user-defined function or with a CALL statement.

Note:

Typically, users use an ARGUMENT statement to define arguments in a program, thereby negating the need for using the ARGFR function to reference arguments passed to the program. For more information on how to use ARGUMENT to define arguments that are passed to a program, see "Declaring Arguments that Are Passed Into a Program" .

Return Value

TEXT

Syntax

ARGFR(n)

Arguments

n

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

Examples

Example 6-22 Passing Arguments Using ARG and ARGFR

Suppose you have a program that produces a sales report. You want to be able to produce this report for any product and any period of months, so you do not want to limit the `product` and `month` dimensions to specific values in the program. Instead, you can use the LIMIT command using ARG for the `product` argument and an ARGFR function for the `month` argument. This way, these items can be specified when the program is run.

When ARGFR is included in the LIMIT command preceded by an ampersand (`&`), Oracle OLAP substitutes the values of `&ARGFR` before the command is executed and, consequently, treats the whole argument as a phrase of the LIMIT command. The `salesrprt` program has a LIMIT command that includes `&ARGFR`.

```DEFINE salesrpt PROGRAM
PROGRAM
PUSH product month district
TRAP ON cleanup
LIMIT product TO UPCASE(ARG(1))
LIMIT month TO &ARGFR(2)
LIMIT district TO ALL
REPORT grandtotals DOWN district sales
cleanup:
POP product month district
END
```

The command line for the `salesrpt` program must include two or more arguments. The first argument is the product for the report, and the second and subsequent arguments are the months. In the `LIMIT month` statement, the `&ARGFR(2)` function returns the months that were specified as arguments on the command line.

The following statement executes the `salesrpt` program, specifying `Jan96`, `Feb96`, `Mar96`, and `Apr96` for the values of `month`.

```salesrpt 'Canoes' 'Jan96' TO 'Apr96'
```

The statement produces the following output.

```PRODUCT: CANOES
-------------------SALES------------------
-------------------MONTH------------------
DISTRICT    Jan96     Feb96      Mar96     Apr96
------- ---------- ---------- ---------- ---------
Boston  70,489.44  82,237.68  97,622.28 134,265.60
Atlanta 56,271.40  61,828.33  77,217.62 109,253.38
Chicago 48,661.74  54,424.94  68,815.71  93,045.46
Dallas  35,244.72  40,218.43  46,810.68  64,031.28
Denver  44,456.41  50,623.19  57,013.01  78,038.13
Seattle 67,085.12  74,834.29  87,820.04 119,858.56
---------- ---------- ---------- ----------
322,208.83 364,166.86 435,299.34 598,492.41
========== ========== ========== ==========
```

The following statement specifies the first three months of 1996.

```salesrpt 'Tents' quarter 'Q1.96'
```

The statement produces the following output.

```PRODUCT: TENTS
-------------SALES-------------
-------------MONTH-------------
DISTRICT          Jan96     Feb96      Mar96
-------------- ---------- ---------- ---------
Boston         50,808.96  34,641.59  45,742.21
Atlanta        46,174.92  50,553.52  58,787.82
Chicago        31,279.78  31,492.35  42,439.52
Dallas         50,974.46  53,702.75  71,998.57
Denver         35,582.82  32,984.10  44,421.14
Seattle        45,678.41  43,094.80  54,164.06
----------  ---------- ---------
260,499.35 246,469.11 317,553.32
========== ========== ==========
```

## ARGS

Within an OLAP DML program, the ARGS function lets you reference the arguments that are passed to a program. The function returns all the arguments as a single text value. You can use the ARGS function only within a program that is be invoked as a command, not as a user-defined function or with a CALL statement.

Note:

Typically, programmers use an ARGUMENT statement to define arguments in a program, thereby negating the need for using the ARGS function to reference arguments passed to the program. For more information on how to use ARGUMENT to define arguments that are passed to a program, see "Declaring Arguments that Are Passed Into a Program" .

Return Value

TEXT

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

Syntax

ARGS

Examples

Example 6-23 Passing Arguments Using ARGS

Assume you have a program that produces a simple sales report. You want to be able to produce this report for any month, so you do not want to limit the `month` dimension to any fixed month in the program. You can use the ARGS function in your LIMIT command so that the months for the report can be supplied as an argument when the program is run.

When ARGS is included in the LIMIT command preceded by an ampersand (`&`), Oracle OLAP substitutes the values of `&ARGS` before the command is executed and, consequently, treats the whole argument as a phrase of the LIMIT command. The `salesreport` program has a LIMIT command that includes `&ARGS`.

```DEFINE salesrpt PROGRAM
PROGRAM
PUSH month product district
TRAP ON cleanup
LIMIT month TO &ARGS
LIMIT product TO 'CANOES'
LIMIT district TO ALL
REPORT grandtotals DOWN district sales
cleanup:
POP month product district
END
```

When you execute the following statement, the LIMIT command uses the values `Jan96` and `Feb96` for the `month` dimension.

```salesrpt 'Jan96' 'Feb96'
```

The statement produces the following output.

```PRODUCT: CANOES
--------SALES--------
--------MONTH--------
DISTRICT       Jan96      Feb96
----------------------------------
Boston       70,489.44  82,237.68
Atlanta      56,271.40  61,828.33
Chicago      48,661.74  54,424.94
Dallas       35,244.72  40,218.43
Denver       44,456.41  50,623.19
Seattle      67,085.12  74,834.29
---------- ---------- --
322,208.83 364,166.86
========== ========== ==
```

## ASCII

The ASCII function returns the decimal representation of the first character of an expression.

Return Value

`INTEGER`

Syntax

ASCII (text-exp)

Arguments

text-exp

A text expression.

Notes

Returning EBCDIC Values

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

Examples

Example 6-24 Finding the ASCII Decimal Equivalent of a Character

The following example returns the ASCII decimal equivalent of the letter "Q".

```SHOW ASCII('Q')
81
```

## ASCISSTR

The ASCIISTR function takes a string in any character set and returns an ASCII version of that string.

Returns

NTEXT

Syntax

ASCIISTR(text-exp)

Arguments

text-exp

A text expression.

Notes

How ASCIISTR Converts Non-ASCII Characters

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

See:

Oracle Database Globalization Support Guide for information on Unicode character sets and character semantics.

## AVERAGE

The AVERAGE function calculates the average of the values of an expression.

Return Value

DECIMAL

Syntax

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

Arguments

expression

The expression whose values are to be averaged.

CACHE

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

dimension

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

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

Tip:

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

Notes

NA Values and AVERAGE

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

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

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

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

Examples

Example 6-25 Calculating Average Monthly Sales

This example shows how to calculate the average monthly sales of sportswear for each sales district.

```LIMIT product TO 'SPORTSWEAR'
REPORT W 14 HEADING 'Average Sales' AVERAGE(sales district)
```

The preceding statements produce the following output.

```DISTRICT    Average Sales
----------- --------------
Boston      69,150.41
Atlanta    151,192.36
Chicago     95,692.99
Dallas     162,242.89
Denver      88,892.72
Seattle     54,092.32
```

You might also want to see the average monthly sales for each region. Since the `region` dimension is related to the `district` dimension, you can specify `region` instead of `district` as a dimension for the results of AVERAGE.

## AW function

The AW function returns information about currently attached workspaces.

Return Value

The return value depends on the keyword you specify, as described in Table 6-2, "Keywords for AW Function".

Syntax

AW(keyword [workspace])

Arguments

keyword

Indicates the specific information you want. The keywords that you can use with the AW function are listed in Table 6-2, "Keywords for AW Function" with the data type of the value they return and the meaning of the information.

Table 6-2 Keywords for AW Function

Keyword Data Type Information Returned

ACQUIRED

TEXT

When an analytic workspace is attached in multiwriter mode, returns the names of any acquired variables and dimensions in the analytic workspace

AGGMAP

TEXT

A list of all aggmap objects in the workspace. When there are several, Oracle OLAP returns a multiline text value with each object name on a separate line.

ALIASLIST

TEXT

A list of currently assigned aliases for the workspace. When there are several, Oracle OLAP returns a multiline text value with each alias on a separate line.

ATTACHED

BOOLEAN

Indicates whether the specified workspace is attached. The workspace argument is required.

CHANGED

BOOLEAN

When you have read/write access to the workspace, indicates whether you have made changes since the last time the workspace was updated. When you have read-only access to the workspace, indicates whether another user has updated the workspace and committed the changes since you attached it.

COMPOSITE

TEXT

A list of all named composite objects in the specified workspace.

DATE

DATE

The date of your most recent update in the current session. When you have not updated in the current session, it returns the date of the most recent commit before you attached the workspace. When you have attached a shared workspace as read-only, DATE does not take into account any updates or commits that have occurred since you attached the workspace.

DIMENSION

TEXT

A list of all the dimensions defined in the workspace. When there are several dimensions, Oracle OLAP returns a multiline text value with each dimension name on a separate line.

EXISTS

BOOLEAN

Indicates whether the specified analytic workspace has been defined in the Oracle Database.

FORMULA

TEXT

A list of all the formulas defined in the workspace. When there are several formulas, Oracle OLAP returns a multiline text value with each formula name on a separate line.

FROZEN

Boolean

`TRUE` if the specified analytic workspace is currently frozen, or `FALSE` if it is not.

FULLNAME

TEXT

The full name of the specified workspace. The full name includes the schema that contains the workspace.

ISUPDATED

TEXT

When the specified analytic workspace is not attached in multiwriter mode, returns TRUE when the workspace is updated but not committed. When he specified analytic workspace is attached in multiwriter mode, returns TRUE when at least one variable or dimension belonging to the workspace is updated but not committed.

LIST

TEXT

A list of all currently attached workspaces. Each line of the multiline text value contains the name of an analytic workspace.

LISTNAMES

TEXT

A list of all the objects defined in the workspace. Each line of the multiline text value contains the name of an analytic workspace object.

MODEL

TEXT

A list of all the models defined in the workspace. When there are several models, Oracle OLAP returns a multiline text value with each model name on a separate line.

MULTI

TEXT

NAME

TEXT

The name of the current workspace.

OPTION

TEXT

A list of all the Oracle OLAP options defined in the `EXPRESS` workspace. When the workspace is not EXPRESS, `AW(OPTION)` returns `NA`, because options are defined only in the `EXPRESS` workspace. For the `EXPRESS` workspace, `AW(OPTION)` returns a multiline text value with each option name on a separate line.

PAGESIZE

INTEGER

The size of the page, in bytes.

PROGRAM

TEXT

A list of all the programs defined in the workspace. When there are several programs, Oracle OLAP returns a multiline text value with each program name on a separate line.

INTEGER

The total number of current users of the Database who have read-only access.

RELATION

TEXT

A list of all the relations defined in the workspace. When there are several relations, Oracle OLAP returns a multiline text value with each relation name on a separate line

RO

TEXT

RW

TEXT

SEGMENTSIZE

DECIMAL

The current maximum segment size for the workspace. It is the most recent value specified using an `AW` `SEGMENTSIZE` statement.

SHARED

BOOLEAN

Indicates whether the workspace is being shared by other users.

TIME

ID

The time of your most recent update in the current session. When you have not updated in the current session, it returns the time of the most recent commit before you attached the workspace. When you have attached a shared workspace as read-only, TIME does not take into account any updates or commits that have occurred since you attached the workspace.

VALUSET

TEXT

A list of all the valuesets that are defined in the workspace. When there are several valuesets, Oracle OLAP returns a multiline text value with each valueset name on a separate line.

VARIABLE

TEXT

A list of all the variables defined in the workspace. When there are several variables, Oracle OLAP returns a multiline text value with each variable name on a separate line.

WORKSHEET

TEXT

A list of all the worksheet objects defined in the workspace. When there are several worksheets, Oracle OLAP returns a multiline text value with each worksheet name on a separate line. This keyword is not available in the Oracle10i release and later.

WRITERS

INTEGER

The number of current users of the Database who have write access.

workspace

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

Notes

Analytic Workspace Status Information

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

Examples

Example 6-26 Ascertaining the Active Workspace

The following program line checks which workspace is currently active so the program can choose the appropriate data to report. With this method, you can use the same report program in several workspaces, each containing different data.

```REPORT IF AW(NAME) EQ 'mysales' THEN mysales ELSE gensales
```

## BACK

The BACK function returns the names of all currently executing programs, listed one name on each line in a multiline text value. When multiple programs are executing, one program has called another in a series of nested executions.

The first name in the return value is that of the program containing the call to BACK. The last name is that of the initial program, which made the first call to another program.

BACK can only be used in a program.

Return Value

TEXT

Syntax

BACK

Examples

Example 6-27 Debugging a Program Using the BACK Function

The following example uses three programs. `program1` calls `program2`, and `program2` calls `program3`.

```DEFINE program1 PROGRAM
PROGRAM
SHOW 'This is program number 1'
CALL program2
END
DEFINE program2 PROGRAM
PROGRAM
SHOW 'This is program number 2'
CALL program3
END
DEFINE program3 PROGRAM
PROGRAM
SHOW 'This is program number 3'
SHOW 'These programs are currently executing:'
SHOW BACK
END
```

Executing `program1` produces the following output.

```This is program number 1
This is program number 2
This is program number 3
These programs are currently executing:
PROGRAM3
PROGRAM2
PROGRAM1
```

## BASEDIM

The BASEDIM function loops over a concat dimension and returns the name of the dimension from which the current value of a concat dimension comes.

Return Value

`TEXT`

Syntax

BASEDIM(concatdim [LEAF])

Arguments

concatdim

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

LEAF

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

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

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

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

Examples

Example 6-28 Returning Base Dimension Names

In this example the `product` dimension is limited to two values, the `district` dimension is limited to its first three values and the `region` dimension has only three values. The example defines a nonunique concat dimension with `region` and `district` as its base dimensions and then defines another nonunique concat dimension with `product` and the first concat dimension as its base dimensions. The example then gets the names of the base dimensions of the outer concat.

```LIMIT district TO 'Boston' TO 'Chicago'
LIMIT product TO 'Tents''Canoes'
DEFINE region.district DIMENSION CONCAT(region district)
DEFINE product.region.district DIMENSION CONCAT(product region.district)
REPORT BASEDIM(product.district.region)
```

The preceding statements return the following.

```PRODUCT
PRODUCT
REGION.DISTRICT
REGION.DISTRICT
REGION.DISTRICT
REGION.DISTRICT
REGION.DISTRICT
REGION.DISTRICT
```

Example 6-29 Returning Component Dimension Names

This example uses the same objects as the previous example. It gets the names of the component dimensions of the concat dimension.

```REPORT BASEDIM(product.region.district LEAF)
```

The preceding statement returns the following.

```PRODUCT
PRODUCT
REGION
REGION
REGION
DISTRICT
DISTRICT
DISTRICT
```

## BASEVAL

The BASEVAL function loops over a concat dimension and returns the values of the base dimensions of a concat dimension. When a base dimension is a concat dimension, then the values of its base dimensions are returned, also.

Return Value

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

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

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

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

Syntax

BASEVAL(concatdim)

Arguments

concatdim

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

Examples

Example 6-30 Returning NTEXT Values

The following example creates two simple dimensions and a nonunique concat dimension, then gets the values of the concat dimension.

```DEFINE textdim DIMENSION TEXT
DEFINE ntextdim DIMENSION NTEXT
DEFINE concatdim DIMENSION CONCAT(textdim ntextdim)
REPORT w 18 BASEVAL(concatdim)
```

The preceding statement returns the following.

```CONCATDIM            BASEVAL(CONCATDIM)
-------------------- ------------------
<textdim: v1>        v1
<textdim: v2>        v2
<ntextdim: n1>       n1
<ntextdim: n2>       n2
```

The data type of the returned values is NTEXT. The BASEVAL function converted the `v1` and `v2` TEXT values into NTEXT values before returning them.

Example 6-31 Returning the Base Values of a Base Concat Dimension

This example defines the simple dimensions `state` and `city` and adds values to them. It defines a nonunique concat dimension, `statecity`, with `state` and `city` as the bases and then defines another nonunique concat dimension, `geog`, with `region`, `district`, and `statecity` as its bases. Finally, the REPORT statement returns the values returned by the BASEVAL function.

```DEFINE city DIMENSION TEXT
DEFINE state DIMENSION TEXT
MAINTAIN city ADD 'Boston' 'Worcester' 'Portsmouth' 'Portland' -
'Burlington' 'Hartford' 'New York' 'Albany'
MAINTAIN state ADD 'MA' 'NH' 'ME' 'VT' 'CT' 'NY'
DEFINE statecity DIMENSION CONCAT(state city)
DEFINE geog DIMENSION CONCAT(region district statecity)
LCOLWIDTH = 20
REPORT W 16 BASEVAL(geog)
```

The preceding statement returns the following.

```GEOG                 BASEVAL(GEOG)
-------------------- ----------------
<region: East>       East
<region: Central>    Central
<region: West>       West
<district: Boston>   Boston
<district: Atlanta>  Atlanta
<district: Chicago>  Chicago
<district: Dallas>   Dallas
<district: Denver>   Denver
<district: Seattle>  Seattle
<state: MA>          MA
<state: NH>          NH
<state: ME>          ME
<state: VT>          VT
<state: CT>          CT
<state: NY>          NY
<city: Boston>       Boston
<city: Worcester>    Worcester
<city: Portsmouth>   Portsmouth
<city: Portland>     Portland
<city: Burlington>   Burlington
<city: Hartford>     Hartford
<city: New York>     New York
<city: Albany>       Albany
```

## BEGINDATE

For dimensions of type DAY, WEEK, MONTH, QUARTER, or YEAR, the BEGINDATE function returns the first date of the first time period in dimension status for which the expression has a non-`NA` value. For example, assume that an expression is dimensioned by `month`, and that `Jan97` is the first dimension value for which the expression has a non-`NA` value. In this case, BEGINDATE returns the date `January 1, 1997`.

Note:

You cannot use this function for time dimensions that are implemented as hierarchical dimensions of type TEXT.

Return Value

DATE-only or text

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

Syntax

BEGINDATE(expression)

Arguments

expression

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

Examples

Example 6-32 Finding the Beginning Date

The following statements limit the values in the `month`, `product`, and `district` dimensions, then send the first date for which the `units` variable contains a non-`NA` value for unit sales of tents in the Chicago district to the current outfile.

```LIMIT month TO ALL
LIMIT product TO 'TENTS'
LIMIT district TO 'CHICAGO'
SHOW BEGINDATE(units)
```

These statements produce the following output.

```01JAN95
```

## BIN_TO_NUM

The BIN_TO_NUM function converts a bit vector to its equivalent number.

BIN_TO_NUM is useful in data warehousing applications for selecting groups of interest using grouping sets.

Return Values

NUMBER

Syntax

BIN_TO_NUM(expression [, expression ]... )

Arguments

expression

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

Examples

Example 6-33 Converting Bit Vectors to a Number

```SHOW BIN_TO_NUM(1,0,1,0)
10.00
```

## BITAND

The BITAND function computes a logical `AND` operation on the bits of two nonnegative values. This function is commonly used with the DECODE function.

An AND operation compares two bit values. When the values are the same, the operator returns 1. When the values are different, the operator returns 0. Only significant bits are compared. For example, an AND operation on the integers 5 (binary 101) and 1 (binary 001 or 1) compares only the rightmost bit, and results in a value of 1 (binary 1).

Return Value

`INTEGER`

Syntax

BITAND (argument1 , argument2)

Arguments

argument1

A nonnegative `INTEGER` expression.

argument2

A nonnegative `INTEGER` expression.

Examples

## BLANKSTRIP

The BLANKSTRIP function removes leading or trailing blank spaces from text values. BLANKSTRIP is useful for such purposes as removing unwanted blank spaces from imported fixed-length fields.

Return Value

TEXT or NTEXT

Syntax

Arguments

text-expression

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

TRAILING

Removes blank spaces at the end of the text.

Removes blank spaces at the beginning of the text.

BOTH

Removes both leading and trailing spaces.

Examples

Example 6-34 Stripping Leading and Trailing Blanks

In this example, we remove both leading and trailing blank spaces from the field `prodlabel` in an imported worksheet and store the results in a variable called `product`.

```product = BLANKSTRIP(prodlabel, BOTH)
```

## CALLTYPE

Within an OLAP DML program, the CALLTYPE function indicates whether a program was invoked as a function, as a command, by using a CALL statement, or triggered by the execution of an OLAP DML statement.

Return Value

TEXT

The return value of CALLTYPE is:

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

• `COMMAND` when the program was invoked as a a command.

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

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

Syntax

CALLTYPE

Examples

Example 6-35 Determining the Calling Method

This sample program, called `myprog`, demonstrates how CALLTYPE returns different values depending on how the program is invoked.

```DEFINE myprog PROGRAM
PROGRAM
SHOW CALLTYPE
RETURN('This is the return value')
END
```

The following statements invoke `myprog`: 1) as command; 2) with a CALL statement; 3) as a function.

```myprog
CALL myprog
SHOW myprog
```

The three statements send the following output to the current outfile. Note that the return value of myprog appears only when the program is called as a function.

```COMMAND
CALL
FUNCTION
This is the return value
```

## CATEGORIZE

The CATEGORIZE function groups the values of a numeric expression into categories. You define the categories by specifying a series of increasing numeric values. The result that CATEGORIZE returns is dimensioned by all the dimensions of expression. For each cell in expression, CATEGORIZE returns one of the following: the category in which the number falls, zero (`0`) for a value below the range of the first category, minus one (`-1`) for a value above the range of the last category, or `NA` for an `NA` value.

Return Value

DECIMAL

Syntax

CATEGORIZE(expression {values|group-expression})

where values has the following syntax:

bottom-value [next-lowest-break-valuetop-value

Arguments

expression

The numeric expression whose values are to be categorized.

bottom-value

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

next-lowest-break-value

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

top-value

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

group-expression

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

Examples

Example 6-36 Specifying Category Range Values

Assume that your analytic workspace contains the following `geography` and `items` dimensions and `sales2` variable.

```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>
```

Assume the `sales2` variable has the following data values.

```-------------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
```

This statement reports the result of categorizing the `sales2` variable.

```REPORT CATEGORIZE(sales2 10 15 20 25)
```

The preceding statement produces the following output.

```-CATEGORIZE(SALES2 10 15 20 25)-
-----------GEOGRAPHY------------
ITEMS              g1         g2         g3
-------------- ---------- ---------- ----------
Item1               -1.00       2.00       1.00
Item2                1.00       3.00       2.00
Item3                2.00       3.00       3.00
Item4               -1.00       3.00       3.00
Item5                  NA       0.00       3.00
```

Example 6-37 Specifying a Group-Expression

These statements define a `groups` dimension and a `groupval` variable.

```DEFINE groups DIMENSION TEXT
MAINTAIN groups ADD 'Grp1' 'Grp2' 'Grp3' 'Grp4'
DEFINE groupvals DECIMAL <groups>
groupvals(groups 'Grp1') = 10
groupvals(groups 'Grp2') = 15
groupvals(groups 'Grp3') = 20
groupvals(groups 'Grp4') = 25
```

This statement reports the result of calling the CATEGORIZE function with the `sales` variable as the expression argument and the `groupvals` variable as the group-expression argument of the call.

```REPORT CATEGORIZE(sales, groupvals)
```

The preceding statement produces the same output as the statement in the "Specifying Category Range Values" .

## CEIL

The CEIL function returns the smallest whole number greater than or equal to a specified number.

Return Value

NUMBER

Syntax

CEIL(n)

Arguments

n

A number (`NUMBER` data type) that you specify.

Examples

Example 6-38 Displaying the Smallest Integer Greater Than or Equal to a Number

The following statements show results returned by CEIL.

• The statement

```SHOW CEIL(15.7)
```

produces the following result

```16
```
• The statement

```SHOW CEIL(-6.457)
```

produces the following result.

```-6
```

## CHANGEBYTES

The CHANGEBYTES function changes one or more occurrences of a specified string in a text expression to another string.

Return Value

TEXT

Syntax

CHANGEBYTES(text-expression oldtext newtext [number])

Arguments

text-expression

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

oldtext

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

newtext

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

number

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

Examples

Example 6-39 Changing Text Values Using Bytes

This example shows how to change one instance of a portion of a text value.

The statement

```SHOW CHANGEBYTES('Hello there, Joe\nHello there, Jane',
'there', - 'to you', 1)
```

produces the following output.

```Hello to you, Joe
Hello there, Jane
```

## CHANGECHARS

The CHANGECHARS function changes one or more occurrences of a specified string in a text expression to another string.

Return Value

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

Syntax

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

Arguments

text-expression

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

oldtext

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

newtext

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

number

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

UPCASE

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

Examples

Example 6-40 Changing the Values of Text Characters

This example shows how to change one instance of a portion of a text value.

The statement

```SHOW CHANGECHARS('Hello there, Joe\nHello there, Jane',
'there', - 'to you', 1)
```

produces the following output.

```Hello to you, Joe
Hello there, Jane
```

## CHANGEDRELATIONS

For a given variable and aggmap object, the CHANGEDRELATIONS function determines if there are any changes in the aggmap and the relations in the aggmap since the last time the variable was aggregated.

Return Value

BOOLEAN.

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

Syntax

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

Arguments

variable

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

partition

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

aggmap

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

## CHANGEDVALUES

The CHANGEDVALUES function identifies if any value in a variable has changed (or the number of values that have changed) since the last time a variable was aggregated.

Return Value

BOOLEAN unless you specify `NUMBER` for returntype.

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

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

Syntax

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

Arguments

variable

The name of the variable to check for changed values.

partition

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

returntype

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

## CHARLIST

The CHARLIST function transforms an expression into a multiline text value with a separate line for each value of the original expression.

Return Value

`NTEXT` when the expression is `NTEXT`; otherwise, `TEXT.`

Syntax

CHARLIST(expression [dimensions])

Arguments

expression

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

dimensions

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

Examples

Example 6-41 Deleting Workspace Objects

You can use CHARLIST with the NAME dimension to create lists of workspace objects. Suppose you want to delete all objects of a certain type in your workspace, for example, all worksheets. You can use CHARLIST and an ampersand (`&`) to do this.

```LIMIT NAME TO OBJ(TYPE) EQ 'WORKSHEET'
DELETE &CHARLIST(NAME)
```

Example 6-42 Creating a List of Top Sales People

Assume you have stored the names of the sales people who sold the most for each product in `product.memo`, a text variable with the dimensions of `product` and . You then want to create a list of top sales people broken out by product. To do this, you can created a variable dimensioned by product and then use CHARLIST with the product to create a separate list of all of the top sales people for each product.

```DEFINE topsales VARIABLE TEXT <product>
topsales = CHARLIST(product.memo product)
```

## CHARTOROWID

The CHARTOROWID function converts a value from a text data type to ROWID data type.

Return Value

`ROWID`

Syntax

CHARTOROWID(char)

Arguments

char

A text expression to convert.

Examples

Example 6-43 Converting a Value from Text to a Rowid

Assume that your analytic workspace contains the `erowid` dimension with the following definition.

```DEFINE erowid DIMENSION ROWID
```

As the following code illustrates, you can add text values to it using the CHARTOROWID function.

```MAINTAIN erowid ADD CHARTOROWID('AAAFd1AAFAAAABSAA/')
REPORT erowid

EROWID
------------------------------
AAAFd1AAFAAAABSAA/
```

## CHGDIMS

The CHGDIMS function changes the dimensionality of an expression or changes the dimension status during the evaluation of expression.

Return Value

Data type of the original expression.

Syntax

CHGDIMS (expression, limit-type)

where limit-type is one of the following:

[CACHE] LIMITSAVE valueset-list

[CACHE] LIMIT valueset-list

TO dimension-list

Arguments

expression

The expression you want to modify.

CACHE

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

LIMITSAVE

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

LIMIT

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

valueset-list

The name of a valueset or the LIMIT function.

TO dimension-list

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

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

Examples

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

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

```

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

```-------------------SALES-------------------
------------------PRODUCT------------------
GEOG            Trousers    Skirts    Dresses     Shoes
-------------- ---------- ---------- ---------- ----------
USA                    13         20         32         18

```

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

```"Total over all dims with standard status
SHOW TOTAL(sales)
175

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

"Total just over product
SHOW TOTAL(CHGDIMS(sales TO product))
83
```

## CHR

The CHR function converts an integer value (or any value that can be implicitly converted to an integer value) into a character.

Note:

Use of this function results in code that is not portable between ASCII- and EBCDIC-based architectures.

Return Value

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

Syntax

CHR(number [ USING NCHAR_CS ])

Arguments

number

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

USING NCHAR_CS

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

Examples

Example 6-44 Converting an Integer Value Into a Character

Assume that you have an ASCII-based system with the WE8ISO8859P1 database character set. In this case, the following statement returns the letter `C`.

```SHOW CHR(67)
C
```

## COALESCE

The COALESCE function returns the first non-`NA` expression in a list of expressions, or `NA` when all of the expressions evaluate to `NA`.

Return Value

Data type of the first argument.

Syntax

COALESCE (expr [, expr]...)

Arguments

expr

An expression.

Examples

Example 6-45 Using COALESCE to Determine the Sales Price of a Product

Assume that you have defined the following objects in your analytic workspace. (Note that the `sale` formula uses the COALESCE function for its calculations.)

```DEFINE product_id DIMENSION TEXT
DEFINE supplier_id DIMENSION TEXT
DEFINE list_price VARIABLE DECIMAL <product_id supplier_id>
DEFINE min_price VARIABLE DECIMAL <product_id supplier_id>

DEFINE sale FORMULA DECIMAL <Product_id supplier_id>
EQ COALESCE(0.9*list_price, min_price, 5)

```

The following code illustrates limiting `supplier_id` to a single value and displaying a report that shows the list price, minimum price, and sale price for the products provided by that supplier.

```LIMIT supplier_id TO '102050'
REPORT DOWN product_id list_price min_price sale

----------SUPPLIER_ID-----------
-------------102050-------------
PRODUCT_ID     LIST_PRICE MIN_PRICE     SALE
-------------- ---------- ---------- ----------
2382               850.00     731.00     765.00
3355                   NA         NA       5.00
1770                   NA      73.00      73.00
2378               305.00     247.00     274.50
1769                48.00         NA      43.20
1660                16.45      16.45      14.80
```

## COLVAL

The COLVAL function returns a numeric value from a column to the left of the current column in the same row of a report. COLVAL can only be used in the ROW command and the REPORT command.

Return Value

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

Syntax

COLVAL(n)

Arguments

n

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

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

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

Examples

Example 6-46 Performing Column Calculations in a Report

Suppose in a report you want to show actual sales and planned sales, along with the difference between the two. You can use the COLVAL function to calculate this difference.

```LIMIT month TO 'Jun96'
LIMIT district TO 'Boston'
FOR product
ROW product sales sales.plan COLVAL(2)-COLVAL(3)
```

These statements produce the following output.

```Tents          95,120.83  80,138.18  14,982.65
Canoes        157,762.08 132,931.39  24,830.69
Racquets       97,174.44  84,758.46  12,415.98
Sportswear     79,630.20  73,568.52   6,061.68
Footwear      153,688.02 109,219.15  44,468.87
```

## CONTEXT function

The CONTEXT function lets you obtain information about object values that are saved in a context. You must first create the context with the CONTEXT command.

Return Value

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

Syntax

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

Arguments

context-name

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

UPDATE

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

name

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

Examples

Example 6-47 Listing Context Names

In the following statement, the CONTEXT function returns the name of the only context in the current session which is the same context used in Example 8-71, "Saving Dimension Status".

```SHOW CONTEXT
```

The statement produces the following output.

```democontext1
```

Example 6-48 Listing Saved Values

In the following statement, the CONTEXT function returns the values of the `product` dimension that are saved in the context named `democontext1`.

```SHOW CONTEXT('democontext1' product)
```

The statement produces the following output.

```Tents
Canoes
```

## CONVERT

The CONVERT function converts values from one type of data to another.

Return Value

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

Syntax

CONVERT(expressiontype [argument...])

Arguments

expression

The expression or variable to be converted.

type

The type of data to which you want to convert expression. The keywords that represent the types are described in Table 6-3, "Keywords for the type Argument of the CONVERT Function".

Table 6-3 Keywords for the type Argument of the CONVERT Function

Keyword Description

BINARY

Does not indicate conversion to a standard Oracle data type but allows additional conversion capabilities. BINARY does no conversion. The internal representation of every value, regardless of data type, is returned as a text value.

• For `TEXT` data types, the result is the value itself and is, therefore, of variable length.

• For `ID` and `DECIMAL` data types, the result is 8 bytes long; `ID` values is blank filled, when necessary.

• For `BOOLEAN` or `INTEGER`, the default result is 2 or 4 bytes long respectively (see the arguments explanation for an additional argument that lets you vary the width slightly).

• For all other data types, the result is 4 bytes long.

BOOLEAN

Conversion to Oracle OLAP `BOOLEAN` data type.

BYTE

Converts a single character into an ASCII `INTEGER` value in the range 0 to 255. Or BYTE converts an `INTEGER` within this range into a character. An INTEGER outside this range is taken modulo 256 and then converted; that is, 256 is subtracted from the INTEGER until the remainder is less than 256, and that within-range remainder is then converted into a character.

DATE

Conversion to Oracle OLAP `DATE` data type.

DATETIME

Conversion to Oracle OLAP `DATETIME` data type.

DECIMAL

Conversion to Oracle OLAP `DECIMAL` data type.

DSINTERVAL

Conversion to Oracle OLAP DML `DSINTERVAL` data type.

ID

Conversion to Oracle OLAP `ID` data type.

INFILE

Encloses an `ID`, `TEXT`, `DATE`, or RELATION value within single quotes, so that it can be read with an INFILE statement. Consequently, expression must have `ID`, `TEXT`, `DATE`, or RELATION value values. In the case of `TEXT` values with no alphanumeric equivalent, INFILE converts them to the correct escape sequences.

INTEGER

Conversion to Oracle OLAP `INTEGER` data type.

LONGINTEGER

Conversion to Oracle OLAP `LONGINTEGER` data type.

NTEXT

Conversion to standard Oracle OLAP data types. Corresponds to the `NCHAR` and `NVARCHAR2` SQL data types. An `NTEXT` character is encoded in `UTF8` Unicode. This encoding might be different from the `NCHAR` character set of the database, which can be `UTF16`. A conversion from `NTEXT` to `TEXT` can result in data loss when the `NTEXT` value cannot be represented in the database character set.

NUMBER [(`p``,[``s``])]`

Conversion to Oracle OLAP `NUMBER` data type.

PACKED

Converts a number to a decimal value and then to packed format -- a text value 8 bytes long containing 15 digits and a plus or minus sign. Fractions cannot be represented in packed numbers; therefore the conversion process rounds decimal numbers to the nearest INTEGER. See "PACKED and BINARY Conversion".

ROWID

Converts a text value to a `ROWID` value.

SHORTDECIMAL

Conversion to Oracle OLAP `SHORTDECIMAL` data type.

SHORTINTEGER

Conversion to Oracle OLAP `SHORTINTEGER` data type.

TEXT

Conversion to standard Oracle OLAP data types. Corresponds to `CHAR` and `VARCHAR2` data types in SQL. A `TEXT` character is encoded in the database character set.

TIMESTAMP

Conversion to Oracle OLAP DML `TIMESTAMP` data type.

TIMESTAMP_LTZ

Conversion to Oracle OLAP DML TIMESTAMP_LTZ data type.

TIMESTAMP_TZ

Conversion to Oracle OLAP DML `TIMESTAMP_TZ` data type.

UROWID

Converts a text value to a `UROWID` value.

YMINTERVAL

Conversion to Oracle OLAP DML `YMINTERVAL` data type.

argument

When you specify TEXT, NTEXT, ID, DATE, or INFILE for the type, you can specify additional arguments to determine how the conversion should be done as outlined in Table 6-4, "Syntax for Specifying Conversion to TEXT, NTEXT, ID, DATE, and INFILE".

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

Keyword for type argument When Converting From Syntax for All Arguments

TEXT

Any numeric

TEXT [decimal-int|DECIMALS [comma-bool|COMMAS [paren-bool|PARENS]]]

NTEXT

Any numeric

NTEXT [decimal-int|DECIMALS [comma-bool|COMMAS [paren-bool|PARENS]]]

ID

Any numeric

ID [decimal-int|DECIMALS]

TEXT, NTEXT, or ID

Any datetime

ID|TEXT|NTEXT ['date_format']

TEXT, NTEXT, or ID

DATE

ID|TEXT|NTEXT ['dateformat']

ID or TEXT for a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR with VNF

DATE

ID [dwmqy-dimension]|TEXT [dwmqy-dimension|'vnf ']

DATE

TEXT, NTEXT, or ID

DATE [date-order|dwmqy-dimname]

NTEXT

TEXT

NOXLATE

TEXT

NTEXT

NOXLATE

INFILE

INFILE [width-exp|LSIZE [escape-int|0]]

IBINARY with `BOOLEAN` or `INTEGER`

BINARY [width-exp]

decimal-int

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

comma-bool

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

paren-bool

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

date_format

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

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

dateformat

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

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

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

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

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

```03-31-98
```

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

dwmqy-dimension

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

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

```show convert(today text year)
```

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

```YR98
```
vnf

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

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

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

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

• First-letter style for the month (MT)

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

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

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

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

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

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

```December, 1997
```

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

date-order

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

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

dwmqy-dimname

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

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

width-exp

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

escape-int

Indicates whether escape sequences are to be used in the output. For this argument you can specify a value listed in Table 6-5, "Values for escape-int Argument of the CONVERT".

Table 6-5 Values for escape-int Argument of the CONVERT

Value Description

-1

Do not use escapes. Precede -1 with a comma (`,-1`) so that Oracle OLAP does not subtract 1 from a preceding WIDTH argument.

0

(Default) Use escapes for unprintable characters.

1

Use escapes for all characters.

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

width-exp

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

NOXLATE

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

Notes

INFILE Conversion

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

Converting DATE-only Values to Numeric Values

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

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

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

Converting Numeric Values to DATE-only Values

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

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

Converting DATE-only Dimension Values to ID Values

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

Converting Relation Values to INTEGER Values

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

Converting Values From One Numeric Data Type to Another

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

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

String-to-Datetime Conversion Rules

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

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

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

• When a match fails between a datetime format element and the corresponding characters in the date string, then Oracle attempts alternative format elements, as shown in Table 6-6, "Oracle Format Matching".

Table 6-6 Oracle Format Matching

Original Format Element Additional Format Elements to Try instead Of the Original
```'MM'
```

`'MON'` and `'MONTH'`

`'MON`

`'MONTH'`

`'MONTH'`

`'MON'`

`'YY'`

`'YYYY'`

`'RR'`

`'RRRR'`

Converting Null and Blank Text Values to BYTE Values

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

PACKED and BINARY Conversion

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

Examples

Example 6-49 Converting Decimal Values to Text

This example shows how to use the JOINCHARS and CONVERT functions to combine some text with the value of the variable `price` for a product and month, and show the price without decimal places.

```LIMIT month TO 'Jul96'
LIMIT product to 'Canoes'
SHOW JOINCHARS('Price of Canoes = \$' CONVERT(price TEXT 0))
Price of Canoes = \$200
```

Example 6-50 Converting Text Values to Escape Sequences

This example shows how to use the CONVERT function with the ESCAPEBASE option to convert a TEXT value from its default decimal escape sequences to hexadecimal escape sequences.

```DEFINE textvar VARIABLE TEXT
textvar = 'testvalue'
SHOW CONVERT(textvar INFILE 9 1)
'\d116\d101\d115\d116\d118\d097\d108\d117\d101'
ESCAPEBASE = 'x'
SHOW CONVERT(textvar INFILE 9 1)
'\x74\x65\x73\x74\x76\x61\x6C\x75\x65'
```

## CORRELATION

The CORRELATION function returns the correlation coefficients for the pairs of data values in two expressions. A correlation coefficient indicates the strength of relationship between the data values. The closer the correlation coefficient is to positive or negative `1`, the stronger the relationship is between the data values in the expressions. A correlation coefficient of `0` (zero) means no correlation and a `+1` (plus one) or `-1` (minus one) means a perfect correlation. A positive correlation coefficient indicates that as the data values in one expression increase (or decrease), the data values in the other expression also increase (or decrease). A negative correlation coefficient indicates that as the data values in one expression increase, the data values in other expression decrease.

Return Value

DECIMAL

Syntax

CORRELATION(expression1 expression2 [PEARSON|SPEARMAN|KENDALL] -

[BASEDON dimension-list])

Arguments

expression1

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

expression2

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

PEARSON

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

SPEARMAN

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

KENDALL

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

BASEDON dimension-list

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

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

Notes

The Effect of NASKIP on CORRELATION

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

Examples

Example 6-51 Correlating with the PEARSON Method

Assume that your analytic workspace contains two variables named `units` and `price`. The two dimensions of the `price` variable, `month` and `product`, are shared by the `units` variable, which has a third dimension, `district`.

The following CORRELATION statement does not specify a dimension-list argument. The output of the CORRELATION function in the statement is one correlation coefficient for each of the dimension values in the dimension that the variables do not have in common.

```REPORT CORRELATION(units price pearson)
```

The preceding statement produces the following output.

```CORRELATION
(UNITS
PRICE
DISTRICT        PEARSON)
-------------- -----------
Boston               -0.75
Atlanta              -0.85
Chicago              -0.83
Dallas               -0.66
Denver               -0.83
Seattle              -0.69
```

The following statements limit the `month` and `product` dimensions.

```LIMIT month to 'Jan96' TO 'Mar96'
LIMIT product TO 'Tents' TO 'Racquets'
```

The following statement reports the correlation coefficient based on the `product` dimension for the limited dimension values that are in status.

```REPORT CORRELATION(units price pearson basedon product)

CORRELATION(UNITS PRICE PEARSON-
--------BASEDON PRODUCT)--------
-------------MONTH--------------
DISTRICT         Jan96      Feb96      Mar96
-------------- ---------- ---------- ----------
Boston              -0.96      -0.90      -0.89
Atlanta             -0.97      -0.97      -0.97
Chicago             -0.96      -0.95      -0.95
Dallas              -0.98      -0.98      -0.99
Denver              -0.97      -0.97      -0.97
Seattle             -0.89      -0.83      -0.83
```

The following statement reports the correlation coefficient based on the `month` dimension for the limited dimension values.

```REPORT CORRELATION(units price pearson basedon month)

CORRELATION(UNITS PRICE PEARSON-
---------BASEDON MONTH)---------
------------PRODUCT-------------
DISTRICT         Tents      Canoes    Racquets
-------------- ---------- ---------- ----------
Boston              -0.59      -0.92      -0.55
Atlanta             -0.73      -0.83       0.03
Chicago             -0.91      -0.84      -0.68
Dallas              -0.86      -0.92       0.31
Denver              -0.98      -0.94      -0.67
Seattle             -0.98      -0.89      -0.70
```

## COS

The COS function calculates the cosine of an angle expression.

Return Value

NUMBER

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

Syntax

COS(angle-expression)

Arguments

angle-expression

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

Examples

Example 6-52 Calculating the Cosine of an Angle in Radians

This example calculates the cosine of an angle of `1` radian. The statements

```DECIMALS = 5
SHOW COS(1)
```

produce the following result.

```0.54030
```

Example 6-53 Calculating the Cosine of an Angle in Degrees

This example calculates the cosine of an angle of 60 degrees. Since 1 degree = `2 * (pi) / 360` radians, 60 degrees is about `60 * 2 * 3.14159 / 360` radians. The statement

```SHOW COS(60 * 2 * 3.14159 / 360)
```

produces the following result.

```0.50000
```

## COSH

The COSH function calculates the hyperbolic cosine of an angle expression.

Return Value

NUMBER

Syntax

COSH(expression)

Arguments

expression

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

Examples

Example 6-54 Calculating the Hyperbolic Cosine of an Angle

This example calculates the hyperbolic cosine of an angle of 1 radian. The statements

```DECIMALS = 5
SHOW COSH(1)
```

produce the following result.

```1.54030
```

## COUNT

The COUNT function counts the number of `TRUE` values of a Boolean expression. It returns `0` (zero) when no values of the expression are `TRUE`.

Return Value

INTEGER

Syntax

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

Arguments

boolean-expression

The Boolean expression whose `TRUE` values are to be counted.

CACHE

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

dimension

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

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

Tip:

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

Notes

The Effect of NASKIP on COUNT

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

Examples

Example 6-55 Counting True Values by District

You can use COUNT to find the number of months in which each district sold more than 2,000 units of sportswear. To obtain a count for each district, specify `district` as the dimension for the result.

```LIMIT product TO 'SPORTSWEAR'
REPORT HEADING 'Count' COUNT(units GT 2000, district)
```

The preceding statement statements produce the following output.

```DISTRICT         Count
-------------- ----------
Boston                  0
Atlanta                23
Chicago                11
Dallas                 24
Denver                  7
Seattle                 0
```

## CUMSUM

The CUMSUM function computes cumulative totals over time or over another dimension. When the data being totaled is one-dimensional, CUMSUM produces a single series of totals, one for all values of the dimension. When the data has dimensions other than the one being totaled over, CUMSUM produces a separate series of totals for each combination of values in the status of the other dimensions.

Return Value

DECIMAL

Syntax

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

Arguments

cum-expression

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

STATUS

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

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

Note:

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

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

reset-dim

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

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

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

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

INSTAT

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

Notes

Overriding an Implicit Relation

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

INSTAT Ignores Current Status By Default

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

Examples

Example 6-56 Multiple CUMSUM Calculations

This example shows cumulative `units` totals for tents and canoes in the Atlanta district for the first six months of 1996. The report shows the units figures themselves, year-to-date totals calculated using `year` as the reset dimension, and totals calculated with no reset dimension using all preceding months. Assume that you issue the following statements.

```LIMIT district TO 'Atlanta'
LIMIT product TO 'Tents' 'Canoes'
LIMIT month TO 'Jan96' TO 'Jun96'
REPORT DOWN month units CUMSUM(units, month year) -
CUMSUM(units, month)
```

The following report is displayed.

```DISTRICT: ATLANTA
------------------------PRODUCT------------------------
---------TENTS------------- ---------CANOES------------
CUMSUM(UNI                   CUMSUM(UNI
TS, MONTH CUMSUM(UNI         TS, MONTH CUMSUM(UNI
MONTH    UNITS    YEAR)   TS, MONTH)  UNITS    YEAR)   TS, MONTH)
-----  -------- --------- ---------- ------- --------- ----------
Jan96      279       279      5,999      281       281      5,162
Feb96      305       584      6,304      309       590      5,471
Mar96      356       940      6,660      386       976      5,857
Apr96      537     1,477      7,197      546     1,522      6,403
May96      646     2,123      7,843      525     2,047      6,928
Jun96      760     2,883      8,603      608     2,655      7,536
```

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

Example 6-57 Resetting for a Quarter

This example shows cumulative totals for the same products and district, for the entire year 1996. Because `quarter` is specified as the reset dimension, totals start accumulating at the beginning of each quarter. The cumulative totals for `Jan96`, `Apr96`, `Jul96`, and `Oct96` are the same as the units figures for those months. Assume that you issue the following statements.

```LIMIT district TO 'Atlanta'
LIMIT product TO 'Tents' 'Canoes'
limit month TO year 'Yr96'
REPORT DOWN month units CUMSUM(units, month quarter)
```

A report displays.

```DISTRICT: ATLANTA
------------------PRODUCT------------------
--------TENTS-------- -------CANOES--------
CUMSUM(UNI          CUMSUM(UNI
TS, MONTH           TS, MONTH
MONTH           UNITS     QUARTER)    UNITS     QUARTER)
------------ ---------- ---------- ---------- ----------
Jan96             279        279        281        281
Feb96             305        584        309        590
Mar96             356        940        386        976
Apr96             537        537        546        546
May96             646      1,183        525      1,071
Jun96             760      1,943        608      1,679
Jul96             852        852        626        626
Aug96             730      1,582        528      1,154
Sep96             620      2,202        520      1,674
Oct96             554        554        339        339
Nov96             380        934        309        648
Dec96             284      1,218        288        936
```

## CURRENT_DATE

The CURRENT_DATE function returns the current date in the session time zone, as a value in the Gregorian calendar.

Return Values

DATETIME

Syntax

CURENT_DATE

Examples

Example 6-58 Retrieving the Current Date

Assume you want to retrieve the date when the date is February 13, 2007.

```SHOW NLS_DATE_FORMAT
DD-MON-RR
SHOW CURRENT_DATE
13-FEB-07
```

## CURRENT_TIMESTAMP

The CURRENT_TIMESTAMP function returns the current date and time in the session time zone, as a value of data type `TIMESTAMP_TZ`. The time zone offset reflects the current local time of the session.

When you want to retrieve the current date and time in the session time zone as a `TIMESTAMP` value, use the LOCALTIMESTAMP function.

Return Values

`TIMESTAMP_TZ`

Syntax

CURRENT_TIMESTAMP [ (precision) ]

Arguments

precision

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

Examples

Example 6-59 Retrieving the Current Timestamp

Assume you want to retrieve the current timestamp.

```SHOW CURRENT_TIMESTAMP
13-FEB-07 09.11.33.454685 AM -08:00
```

## DAYOF

The DAYOF function returns an `INTEGER` in the range of 1 through 7, giving the day of the week on which a specified date falls. A result of `1` refers to Sunday. The result has the same dimensions as the specified DATE expression.

Return Value

INTEGER

Syntax

DAYOF(date-expression)

Arguments

date-expression

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

Examples

Example 6-60 Finding Today's Weekday

The following statement sends the day of the week on which today's date falls to the current outfile.

```SHOW DAYOF(TODAY)
```

When today's date is January 15, 1997, which is a Wednesday, this statement produces the following output.

```4
```

Example 6-61 Finding the Weekday of a Date

The following statement sends the day of the week on which July 4 fell in 1996 to the current outfile.

```SHOW DAYOF('04jul96')
```

This statement produces the following output.

```5
```

## DBTIMEZONE

The DBTIMEZONE function returns the value of the Database time zone.

Return Values

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

Syntax

DBTIMEZONE

Examples

Example 6-62 Retrieving the Database Time Zone

```SHOW DBTIMEZONE
-08:00
```

## DDOF

The DDOF function returns an `INTEGER` in the range of 1 through 31, giving the day of the month on which a specified date falls. The result returned by DDOF has the same dimensions as the specified DATE expression.

Return Value

INTEGER

Syntax

DDOF(date-expression)

Arguments

date-expression

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

Examples

Example 6-63 Finding Today's Day of the Month

The following statement returns the day of the month on which today's date falls.

```SHOW DDOF(TODAY)
```

When today's date is September 8, 2000, this statement produces the following output.

```8
```

## DECODE

The DECODE function compares one expression to one or more other expressions and, when the base expression equals a search expression, returns the corresponding result expression; or, when no match is found, returns the default expression when it is specified, or `NA` when it is not.

Return Value

The data type of the first result argument.

Syntax

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

Arguments

expr

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

search

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

result

The expression to return when expression equals search.

default

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

Notes

Order of Value Evaluation

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

Examples

Example 6-64 Decoding an ID Field

Assume that your analytic workspace contains the following objects. Note that the `inventory_location` formula uses the DECODE function to identify text values that correspond to the `INTEGER` values of `warehouse_id`.

```DESCRIBE
DEFINE product_id DIMENSION TEXT
DEFINE warehouse_id DIMENSION INTEGER
DEFINE inventories VARIABLE DECIMAL <product_id warehouse_id>
DEFINE inventory_location FORMULA TEXT <warehouse_id>
EQ -
DECODE (warehouse_id, 1, 'Southlake', 2, 'San Francisco', 4, 'Seattle', -
'Non domestic')

REPORT inventories
---------------INVENTORIES---------------
---------------PRODUCT_ID----------------
WAREHOUSE_ID                 1770                 1775
-------------------- -------------------- --------------------
1                30.63                79.02
2                71.49                55.83
3                88.71                68.02
4                86.27                41.86

REPORT inventory_location
WAREHOUSE_ID          INVENTORY_LOCATION
-------------------- --------------------
1 Southlake
2 San Francisco
3 Non domestic
4 Seattle

```

The following reports illustrate how you can use `inventory_location` to display the decoded values of `warehouse_id` in a report rather than displaying the actual values of `warehouse_id`.

```LIMIT product_id to '1775'

REPORT DOWN warehouse_id inventories
----INVENTORIES-----
-----PRODUCT_ID-----
WAREHOUSE_ID                 1775
-------------------- --------------------
1                79.02
2                55.83
3                68.02
4                41.86

REPORT DOWN inventory_location inventories
----INVENTORIES-----
-----PRODUCT_ID-----
INVENTORY_LOCATION           1775
-------------------- --------------------
Southlake                           79.02
San Francisco                       55.83
Non domestic                        68.02
Seattle                             41.86
```

Example 6-65 DECODE with BITAND

Assume that you have the following objects with the reported values within your analytic workspace.

```DEFINE order_id DIMENSION TEXT
DEFINE customer_id DIMENSION TEXT
DEFINE order_customer COMPOSITE <order_id customer_id>
DEFINE order_status VARIABLE NUMBER(2) <order_customer<order_id customer_id>>

REPORT DOWN order_customer order_status
ORDER_ID   CUSTOMER_ID  ORDER_STATUS
------------ ------------ ------------
2458         101                  0.00
2397         102                  1.00
2454         103                  1.00
2354         104                  0.00
2358         105                  2.00
2381         106                  3.00
2440         107                  3.00
2357         108                  5.00
2394         109                  5.00
2435         144                  6.00
2455         145                  7.00
2356         105                  5.00
2360         107                  4.00

```

Assume that the value of `order_status` is used as a bitmap where the first three bits hold information about the order and the other bits are always 0:

• The first bit is used for location information:

0 = Post Office, which corresponds to integer values of 0, 2, 4, and 6.

1 = Warehouse, which corresponds to the integer values of 1, 3, 5, and 7.

• The second bit is used for method:

0 = Air, which corresponds to the integer values of 0, 1, 4, and 5.

1 = Ground, which corresponds to the integer values of 2, 3, 6, and 7.

• The third bit is used for receipt:

0 = Certified, which corresponds the integer values of 0, 1, 2, and 3.

1 =Insured, which corresponds to the integer values of 4, 5, 6, and 7.

The following formulas use DECODE to substitute the text values for the bit values.

```DEFINE location FORMULA DECODE(BITAND(order_status, 1), 1, 'Warehouse', 'PostOffice')
DEFINE method FORMULA DECODE(BITAND(order_status, 2), 2, 'Ground', 'Air')
DEFINE receipt FORMULA DECODE(BITAND(order_status, 4), 4, 'Insured', 'Certified')
```

Now, you can issue a report to display the decoded values.

```REPORT DOWN order_customer order_status location method receipt

ORDER_ID   CUSTOMER_ID  ORDER_STATUS   LOCATION      METHOD      RECEIPT
------------ ------------ ------------ ------------ ------------ ------------
2458         101                  0.00 PostOffice   Air          Certified
2397         102                  1.00 Warehouse    Air          Certified
2454         103                  1.00 Warehouse    Air          Certified
2354         104                  0.00 PostOffice   Air          Certified
2358         105                  2.00 PostOffice   Ground       Certified
2381         106                  3.00 Warehouse    Ground       Certified
2440         107                  3.00 Warehouse    Ground       Certified
2357         108                  5.00 Warehouse    Air          Insured
2394         109                  5.00 Warehouse    Air          Insured
2435         144                  6.00 PostOffice   Ground       Insured
2455         145                  7.00 Warehouse    Ground       Insured
2356         105                  5.00 Warehouse    Air          Insured
2360         107                  4.00 PostOffice   Air          Insured
```

## DEPRDECL

The DEPRDECL function calculates the depreciation expenses for a series of assets. DEPRDECL uses the declining balance method, as described in "Calculation Method Used by DEPRDECL", to depreciate the assets over the specified lifetime of the assets. The starting value and ending value are specified for the assets acquired in each time period.

Tip:

The pure declining-balance method of depreciation used by DEPRDECL is not the most widely used form of the declining-balance method. For a more commonly used form of the declining-balance method, see the DEPRDECLSW function, which uses a combination of the declining-balance and straight-line methods.

Return Value

DECIMAL

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

Syntax

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

Arguments

start-exp

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

end-exp

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

n

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

STATUS

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

decline-factor

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

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

FULL

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

HALF

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

portion-exp

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

time-dimension

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

Notes

Calculation Method Used by DEPRDECL

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

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

Low Ending Value

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

High Ending Value

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

DEPRDECL and NA Values

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

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

Examples

Example 6-66 Using DEPRDECL to Calculate Depreciation Expenses for Assets Acquired in a Single Period

This example shows how to use DEPRDECL to calculate depreciation expenses for assets acquired in a single time period.

The following statements create two variables called `assets` and `salvage`.

```DEFINE assets DECIMAL <year>
DEFINE salvage DECIMAL <year>
```

Suppose you assign the following values to the variables `assets` and `salvage`.

```YEAR             ASSETS    SALVAGE
-------------- ---------- ----------
Yr95             1,000.00     100.00
Yr96                 0.00       0.00
Yr97                 0.00       0.00
Yr98                 0.00       0.00
Yr99                 0.00       0.00
Yr00                 0.00       0.00
```

The `assets` variable contains the starting value of the assets acquired in 1995. The `salvage` variable contains the ending value of the assets acquired in 1995.

The following statement reports asset and salvage values, along with depreciation expenses for the assets. Note that the call to DEPRDECL to calculate the depreciation expenses specifies an asset lifetime of 5 periods (in this case, years) and a decline factor of 2 (double-declining balance).

```REPORT assets salvage W 12 HEADING 'Depreciation' -
DEPRDECL(assets salvage 5 2 FULL year)
```

This statement produces the following output.

```YEAR             ASSETS    SALVAGE   Depreciation
-------------- ---------- ---------- ------------
Yr95             1,000.00     100.00       400.00
Yr96                 0.00       0.00       240.00
Yr97                 0.00       0.00       144.00
Yr98                 0.00       0.00        86.40
Yr99                 0.00       0.00        29.60
Yr00                 0.00       0.00         0.00
```

In this example, the depreciation expense for 1999 is adjusted so that the current asset value does not fall below the salvage value. The current asset value is calculated by subtracting the accumulated depreciation expense from the starting asset value. For example, for 1998 the accumulated depreciation expense is \$870.40 (\$400.00 + \$240.00 + \$144.00 + \$86.40 = \$870.40). Thus, the current asset value for 1998 is \$129.60 (\$1,000.00 - \$870.40 = \$129.60). In this example, the depreciation expense is usually calculated by multiplying the current asset value by 2 and then dividing the result by 5. Now, if \$129.60 is multiplied by 2, then divided by 5, the resulting depreciation expense is \$51.84. If this depreciation expense is subtracted from the 1998 current asset value of \$129.60, the current asset value for 1999 would be \$77.76, which is below the salvage value of \$100. Instead of letting the current asset value fall below the salvage value, the DEPRDECL function subtracts the salvage value (\$100.00) from the current asset value (\$129.60) to calculate the depreciation expense (\$29.60).

Example 6-67 Using DEPRDECL to Calculate the Depreciation Expenses for Assets Acquired in Multiple Periods

You can also use DEPRDECL to calculate the depreciation expenses for a series of assets.

Suppose you change the values for the year 1997 in the variables `assets` and `salvage` to the values shown in the following report.

```YEAR             ASSETS    SALVAGE
-------------- ---------- ----------
Yr95             1,000.00     100.00
Yr96                 0.00       0.00
Yr97               500.00      50.00
Yr98                 0.00       0.00
Yr99                 0.00       0.00
Yr00                 0.00       0.00
Yr01                 0.00       0.00
Yr02                 0.00       0.00
```

Now `assets` and `salvage` contain nonzero values for 1995 and for 1997

The following statement reports the values of assets and salvage, and uses DEPRDECL to calculate depreciation expenses for each year, specifying an asset lifetime of 5 years, and a decline factor of 2 (double declining balance).

```REPORT assets SALVAGE W 12 HEADING 'Depreciation'  -
DEPRDECL(assets salvage 5 2 FULL year)
```

This statement produces the following output. (Notice that the depreciation expense increases in 1997 due to the assets acquired in that year.)

```YEAR             ASSETS    SALVAGE   Depreciation
-------------- ---------- ---------- ------------
Yr95             1,000.00     100.00       400.00
Yr96                 0.00       0.00       240.00
Yr97               500.00      50.00       344.00
Yr98                 0.00       0.00       206.00
Yr99                 0.00       0.00       101.00
Yr00                 0.00       0.00        43.20
Yr01                 0.00       0.00        14.80
Yr02                 0.00       0.00         0.00
```

## DEPRDECLSW

The DEPRDECLSW function calculates the depreciation expenses for a series of assets. DEPRDECLSW uses a variation on the declining balance method, as described in "Calculation Method Used by DEPRDECLSW", to depreciate assets over the specified lifetime of the assets. DEPRDECLSW begins by using the declining balance method, then switches over to the straight-line method at one of the following points in the time series:

• The first period for which straight-line depreciation over the remaining periods exceeds the declining balance depreciation for those periods (the default)

• The period specified by the switch-period argument

This variation on the declining-balance method is the most commonly used form of declining-balance depreciation methods.

Return Value

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

Syntax

DEPRDECLSW(start-exp end-exp n [STATUS]

[decline-factor [{FULL|HALF| portion-exp [switch-period [time-dimension]]]])

Arguments

start-exp

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

end-exp

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

n

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

STATUS

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

decline-factor

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

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

FULL

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

HALF

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

portion-exp

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

switch-period

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

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

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

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

time-dimension

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

Notes

Calculation Method Used by DEPRDECLSW

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

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

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

Unexpected-Balance Method

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

DEPRDECLSW and NA Values

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

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

Examples

Example 6-68 Calculating Depreciation Expenses for Assets Acquired in a Single Period

This example shows how to use DEPRDECLSW to calculate depreciation expenses for assets acquired in a single time period. It also shows the behavior of DEPRDECLSW when you do not specify a switch period.

The following statements create two variables called `assets` and `salvage`.

```DEFINE assets DECIMAL <year>
DEFINE salvage DECIMAL <year>
```

Suppose you assign the following values to the variables `assets` and `salvage`.

```YEAR        ASSETS     SALVAGE
------- ---------- -----------
Yr95      1,000.00      100.00
Yr96          0.00        0.00
Yr97          0.00        0.00
Yr98          0.00        0.00
Yr99          0.00        0.00
Yr00          0.00        0.00
```

The variable `assets` contains the starting value of the assets acquired in 1995. `salvage` contains the ending value of the assets acquired in 1995.

The following statement reports the values of assets and salvage, and uses DEPRDECLSW to calculate depreciation expenses for each year, specifying an asset lifetime of 5 years, and a decline factor of 2 (double declining balance). The statement does not specify a switch-period argument. Because of this, DEPRDECLSW uses the default for switch-period, which is to switch from the declining balance method of depreciation in the first period for which straight-line depreciation over the remaining periods exceeds the declining-balance depreciation.

```REPORT assets salvage W 12 HEADING 'Depreciation' -
DEPRDECLSW (assets salvage 5 2 FULL)
```

This statement produces the following report.

```YEAR        ASSETS     SALVAGE   Depreciation
------- ---------- ----------- --------------
Yr95      1,000.00      100.00      400.00
Yr96          0.00        0.00      240.00
Yr97          0.00        0.00      144.00
Yr98          0.00        0.00      108.00
Yr99          0.00        0.00        8.00
Yr00          0.00        0.00        0.00
```

Example 6-69 Specifying the Switch Period

Alternatively, you can specify the period in which the switch occurs.

To switch from the declining balance method to the straight-line method of depreciation in the third year (`Yr97`), specify `3` as the switch period, as shown in the following statement.

```REPORT assets salvage W 12 HEADING 'DEPRECIATION' -
DEPRDECLSW (assets salvage 5 2 FULL 3 year)
```

This statement produces the following report.

```YEAR        ASSETS     SALVAGE   Depreciation
-------- ---------- ----------- --------------
Yr95      1,000.00      100.00      400.00
Yr96          0.00        0.00      240.00
Yr97          0.00        0.00      120.00
Yr98          0.00        0.00      120.00
Yr99          0.00        0.00       20.00
Yr00          0.00        0.00        0.00
```

Example 6-70 Calculating the Depreciation Expenses for Assets Acquired in Multiple Periods

You can use DEPRDECLSW to calculate the depreciation expenses for a series of assets. Suppose you change the values for the year 1997 in the variables `assets` and `salvage` to the values shown in the following report.

```YEAR             ASSETS    SALVAGE
-------------- ---------- ----------
Yr95             1,000.00     100.00
Yr96                 0.00       0.00
Yr97               500.00      50.00
Yr98                 0.00       0.00
Yr99                 0.00       0.00
Yr00                 0.00       0.00
Yr01                 0.00       0.00
Yr02                 0.00       0.00
```

Now `assets` and `salvage` contain nonzero values for 1995 and for 1997.

The following statement reports asset and salvage values along with depreciation expenses for the assets. Note that the call to DEPRDECLSW to calculate the depreciation expenses specifies an asset lifetime of 5 periods (in this case, years) and a decline factor of 2 (double-declining balance). The statement does not specify a switch-period argument. Because of this, DEPRDECLSW uses the default for switch-period, which is to switch from the declining balance method of depreciation in the first period for which straight-line depreciation over the remaining periods exceeds the declining-balance depreciation.

```REPORT assets salvage W 12 HEADING 'Depreciation'  -
DEPRDECLSW(assets salvage 5 2 FULL)
```

This statement produces the following output.

```YEAR             ASSETS    SALVAGE   Depreciation
-------------- ---------- ---------- ------------
Yr95             1,000.00     100. 00       400.00
Yr96                 0.00       0.00        240.00
Yr97               500.00      50.00        344.00
Yr98                 0.00       0.00        228.00
Yr99                 0.00       0.00         80.00
Yr00                 0.00       0.00         54.00
Yr01                 0.00       0.00          4.00
Yr02                 0.00       0.00          0.00
```

Notice that the depreciation expense increases in 1997 due to the assets acquired in that year.

## DEPRSL

The DEPRSL function calculates the depreciation expenses for a series of assets. DEPRSL uses the straight-line method, as described in "DEPRSL Calculation Method", to depreciate the assets over the specified lifetime of the assets. The starting and ending values are specified for the assets acquired in each time period.

Return Value

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

Syntax

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

Arguments

start-exp

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

end-exp

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

n

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

STATUS

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

FULL

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

HALF

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

portion-exp

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

time-dimension

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

Notes

DEPRSL Calculation Method

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

DEPRSL and NA Values

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

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

Examples

Example 6-71 Using DEPRSL to Calculate Depreciation Expenses for Assets Acquired in a Single Period

This example shows how to use DEPRSL to calculate depreciation expenses for assets acquired in a single time period.

The following statements create two variables called `assets` and `salvage`.

```DEFINE assets DECIMAL <year>
DEFINE salvage DECIMAL <year>
```

Suppose you assign the following values to the variables `assets` and `salvage`.

```YEAR               ASSETS    SALVAGE
-------------- ---------- ----------
Yr95             1,000.00     100.00
Yr96                 0.00       0.00
Yr97                 0.00       0.00
Yr98                 0.00       0.00
Yr99                 0.00       0.00
Yr00                 0.00       0.00
```

The variable `assets` contains the starting value of assets acquired in 1995. The variable `salvage` contains the ending value of the assets acquired in 1995.

The following statement reports the values of assets and salvage, and uses DEPRSL to calculate depreciation expenses for each year, specifying an asset lifetime of 5 years.

```REPORT assets salvage W 12 HEADING 'Depreciation' -
DEPRSL(assets salvage 5 FULL year)
```

This statement produces the following output.

```YEAR               ASSETS    SALVAGE Depreciation
-------------- ---------- ---------- ------------
Yr95             1,000.00     100.00       180.00
Yr96                 0.00       0.00       180.00
Yr97                 0.00       0.00       180.00
Yr98                 0.00       0.00       180.00
Yr99                 0.00       0.00       180.00
Yr00                 0.00       0.00         0.00
```

Example 6-72 Using DEPRSL to Calculate the Depreciation Expenses for Assets Acquired in Multiple Periods

You can also use DEPRSL to calculate the depreciation expenses for a series of assets. Suppose you change the values for the year 1997 in the variables `assets` and `salvage` to the values shown in the following report.

```YEAR               ASSETS    SALVAGE
-------------- ---------- ----------
Yr95             1,000.00     100.00
Yr96                 0.00       0.00
Yr97               500.00      50.00
Yr98                 0.00       0.00
Yr99                 0.00       0.00
Yr00                 0.00       0.00
Yr01                 0.00       0.00
Yr02                 0.00       0.00
```

Now `assets` and `salvage` contain nonzero values for 1995 and for 1997.

The following statement reports asset and salvage values along with depreciation expenses for the assets. Note that the call to DEPRSL to calculate the depreciation expenses specifies an asset lifetime of 5 periods (in this case, years).

```REPORT assets salvage W 12 HEADING 'Depreciation' -
DEPRSL(assets salvage 5 FULL year)
```

This statement produces the following report.

```YEAR              ASSETS        SALVAGE         Depreciation
-------------- ---------- ------------- --------------------
Yr95             1,000.00       100.00                180.00
Yr96                 0.00         0.00                180.00
Yr97               500.00        50.00                270.00
Yr98                 0.00         0.00                270.00
Yr99                 0.00         0.00                270.00
Yr00                 0.00         0.00                 90.00
Yr01                 0.00         0.00                 90.00
Yr02                 0.00         0.00                  0.00
```

The assets acquired in 1995 were fully depreciated in 1999. Therefore, for 2000 and 2001, DEPRSL returns a figure that includes the depreciation expense for the assets acquired in 1997 only.

## DEPRSOYD

The DEPRSOYD function calculates the depreciation expenses for a series of assets. DEPRSOYD uses the sum-of-years'-digits method, as described in "Calculation Method Used by DEPRSOYD", to depreciate the assets over the specified lifetime of the assets. The starting and ending values are specified for the assets acquired in each time period.

Return Value

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

Syntax

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

Arguments

start-exp

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

end-exp

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

n

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

STATUS

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

FULL

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

HALF

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

portion-exp

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

time-dimension

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

Notes

Calculation Method Used by DEPRSOYD

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

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

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

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

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

DEPRSOYD and NA Values

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

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

Examples

Example 6-73 Using DEPRSOYD to Calculate Depreciation Expenses for Assets Acquired in a Single Period

This example shows how to use DEPRSOYD to calculate depreciation expenses for assets acquired in a single time period.

The following statements create two variables called `assets` and `salvage`.

```DEFINE assets DECIMAL <year>
DEFINE salvage DECIMAL <year>
```

Suppose you assign the following values to the variables `assets` and `salvage`.

```YEAR               ASSETS    SALVAGE
-------------- ---------- ----------
Yr95             1,000.00     100.00
Yr96                 0.00       0.00
Yr97                 0.00       0.00
Yr98                 0.00       0.00
Yr99                 0.00       0.00
Yr00                 0.00       0.00
```

The variable `assets` contains the starting value of assets acquired in 1995. The variable `salvage` contains the ending value of the assets acquired in 1995.

The following statement reports the values of `assets` and `salvage`, and uses DEPRSOYD to calculate depreciation expenses for each year, specifying an asset lifetime of 5 years.

```REPORT assets salvage W 12 HEADING 'Depreciation' -
DEPRSOYD(assets salvage 5 FULL year)
```

This statement produces the following report.

```YEAR               ASSETS    SALVAGE Depreciation
-------------- ---------- ---------- ------------
Yr95             1,000.00     100.00       380.00
Yr96                 0.00       0.00       240.00
Yr97                 0.00       0.00       180.00
Yr98                 0.00       0.00       120.00
Yr99                 0.00       0.00        60.00
Yr00                 0.00       0.00         0.00
```

Example 6-74 Using DEPRSOYD to Calculate the Depreciation Expenses for Assets Acquired in Multiple Periods

You can also use DEPRSOYD to calculate the depreciation expenses for a series of assets. Suppose you change the values for the year 1997 in the variables `assets` and `salvage` to the values shown in the following report.

```YEAR             ASSETS    SALVAGE
-------------- ---------- ----------
Yr95             1,000.00     100.00
Yr96                 0.00       0.00
Yr97               500.00      50.00
Yr98                 0.00       0.00
Yr99                 0.00       0.00
Yr00                 0.00       0.00
Yr01                 0.00       0.00
Yr02                 0.00       0.00
```

Now `assets` and `salvage` contain nonzero values for 1995 and for 1997.

The following statement reports asset and salvage values along with depreciation expenses for the assets. Note that the call to DEPRSOYD to calculate the depreciation expenses specifies an asset lifetime of 5 periods (in this case, years).

```REPORT assets salvage W 12 HEADING 'Depreciation' -
DEPRSOYD(assets salvage 5 FULL year)
```

This statement produces the following output.

```YEAR             ASSETS    SALVAGE   Depreciation
-------------- ---------- ---------- ------------
Yr95             1,000.00     100.00       300.00
Yr96                 0.00       0.00       240.00
Yr97               500.00      50.00       330.00
Yr98                 0.00       0.00       240.00
Yr99                 0.00       0.00       160.00
Yr00                 0.00       0.00        60.00
Yr01                 0.00       0.00        30.00
Yr02                 0.00       0.00         0.00
```

Notice that as a result of the second asset, the depreciation expenses increase in 1997. The depreciation is the total depreciation of \$180.00 (`\$60` `x` `3`) for the first asset and \$150.00 (`\$30` `x` `5`) for the second asset.

## ENDDATE

For expressions dimensioned by a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, the ENDDATE function returns the final date of the last time period in the dimension status for which the expression has a non-`NA` value. For example, when an expression is dimensioned by a dimension of type MONTH, and when `DEC98` is the last dimension value for which the expression has a non-`NA` value, ENDDATE returns the date `December` `31,` `1998`.

Return Value

DATE-only or text

Syntax

ENDDATE(expression)

Arguments

expression

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

Examples

Example 6-75 Finding the End Date

The following statements limit the values of the dimensions of the `units` variable, then sends the last date associated with a non-`NA` value to the current outfile.

```LIMIT month TO ALL
LIMIT product TO 'Tents'
LIMIT district TO 'Chicago'
SHOW ENDDATE(units)
```

These statements produce the following output.

```31DEC96
```

## ENDOF

For expressions dimensioned by a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, the ENDOF function returns the last date of a time period that is first in the current status list of the dimension.

ENDOF 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 ENDOF function identifies the final date of a particular four-week period.

Return Value

DATE-only or text

Syntax

ENDOF(dwmqy-dimension)

Arguments

dwmqy-dimension

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

Examples

Example 6-76 Finding the Fiscal Year End Date

The following statements define a year dimension (called `taxyear`, for a tax year that begins in July), add dimension values for tax years `1998` through `2000`, and produce a report showing the last date of each tax year.

```DEFINE taxyear DIMENSION YEAR BEGINNING july
VNF 'TY<ffb>'
REPORT W 14 ENDOF(taxyear)
```

These statements produce the following output.

```TAXYEAR        ENDOF(TAXYEAR)
-------------- --------------
TY98           30JUN99
TY99           30JUN00
TY00           30JUN01
```

## EVERY

The EVERY function returns `YES` when every value of a Boolean expression is `TRUE`, or `NO` when any value of the expression is `FALSE`.

Return Value

BOOLEAN

Syntax

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

Arguments

boolean-expression

The Boolean expression whose values are to be evaluated.

CACHE

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

dimension

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

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

Tip:

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

Notes

The Effect of NASKIP on EVERY

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

Examples

Example 6-77 Testing for All-True Values by District

You can use the EVERY function to test whether each district's sales of sportswear have exceeded \$50,000 in every month. To have the results dimensioned by district, specify `district` as the second argument to EVERY.

```LIMIT product TO 'Sportswear'
REPORT HEADING 'Top Sales' EVERY(sales GT 50000, district)
```

The preceding statements produce the following output.

```DISTRICT       Top Sales
-------------- ----------
Boston                 No
Atlanta               Yes
Chicago               Yes
Dallas                Yes
Denver                Yes
Seattle                NO
```

Example 6-78 Testing for All-True Values by Region

You might also want to find out the regions for which every district has sportswear sales that exceed \$50,000 in every month. Since the `region` dimension is related to the `district` dimension, you can specify `region` instead of `district` as a dimension for the results of EVERY.

```REPORT HEADING 'Top Sales' EVERY(sales GT 50000, region)
```

The preceding statement produces the following output.

```REGION         Top Sales
-------------- ----------
East                   No
Central               Yes
West                   NO
```

## EXISTS

The EXISTS function determines whether an object is defined in any attached workspace. The EXISTS function is useful in a program to test whether a definition exists before you try to use it.

Return Value

BOOLEAN

Syntax

EXISTS(name-expression)

Arguments

name-expression

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

Notes

Specifying More Than One Name

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

Examples

Example 6-79 Using EXISTS

This example tests whether the variable `actual` has been defined in any attached workspace. The statement

```SHOW EXISTS('actual')
```

produces the following result.

```YES
```

## EXP

The EXP function returns e raised to the nth power, where e equals `2.71828183...`.

Return Value

`NUMBER`

Syntax

EXP (n)

Arguments

n

The power by which you want to raise e.

Examples

Example 6-80 Raising an Expression to a Power

The following example returns e to the 4th power.

```SHOW EXP(4)

54.59815
```

## EVERSION

The EVERSION function returns a text value that specifies the internal Oracle OLAP build number.

Return Value

TEXT

Syntax

EVERSION

Notes

EVERSION and Major Releases

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

Examples

Example 6-81 Obtaining the Version Number

The following statement produces text output that indicates the Oracle OLAP build number.

```SHOW EVERSION
```

This statement produces output like the following.

```Oracle OLAP Build 80020
```

## EXTBYTES

The EXTBYTES function extracts a portion of a text expression.

Return Value

TEXT

Syntax

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

Arguments

text-expression

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

start

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

length

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

Examples

Example 6-82 Extracting Text Characters Using Bytes

This example shows how to extract portions of text from the TEXT value `'hellotherejoe'`.

• The statement

```SHOW EXTBYTES('hellotherejoe', 6, 5)
```

produces the following output.

```there
```
• The statement

```SHOW EXTBYTES('hellotherejoe', 11)
```

produces the following output.

```joe
```

## EXTCHARS

The EXTCHARS function extracts a portion of a text expression.

Tip:

When you are using a multibyte character set, you can use the EXTBYTES function instead of the EXTCHARS function.

Return Value

TEXT or NTEXT

Syntax

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

Arguments

text-expression

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

start

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

length

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

Examples

Example 6-83 Extracting Text Characters

This example shows how to extract portions of text from the TEXT value `'hellotherejoe'`.

• The statement

```SHOW EXTCHARS('hellotherejoe', 6, 5)
```

produces the following output.

```there
```
• The statement

```SHOW EXTCHARS('hellotherejoe', 11)
```

produces the following output.

```joe
```

## EXTCOLS

The EXTCOLS function extracts specified columns from each line of a multiline text value. The function returns a multiline text value that includes only the extracted columns. Columns refer to the character positions in each line of a multiline text value. The first character in each line is in column one, the second is in column two, and so on.

Return Value

`TEXT` or `NTEXT`

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

Syntax

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

Arguments

text-expression

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

start

An `INTEGER`, between `1` and `4000`, that represents the column position at which to begin extracting. The column position of the first character in each line of text-expression is `1`. When you specify a starting column that is to the right of the last character in a given line in text expression, the corresponding line in the return value is empty.

numcols

An `INTEGER` that represents the number of columns to be extracted. When you do not specify numcols, EXTCOLS extracts all the characters from the starting column to the end of each line. When you specify a length that exceeds the number of characters that follow the starting position in a given line in text expression, the corresponding line in the return value includes only existing characters. EXTCOLS does not return spaces at the end of the line to fill in the missing columns.

Examples

Example 6-84 Extracting Text Columns

In this example, four columns are extracted from each line of `citylist`, starting from the second column.

```DEFINE citylist VARIABLE TEXT
citylist = 'Boston\nHouston\nChicago'
```
• The statement

```SHOW citylist
```

produces the following output.

```Boston
Houston
Chicago
```
• The statement

```SHOW EXTCOLS(citylist 2 4)
```

produces the following output.

```osto
oust
hica
```

## EXTLINES

The EXTLINES function extracts lines from a multiline text expression.

Return Value

`TEXT` or `NTEXT`

Syntax

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

Arguments

text-expression

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

start

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

numlines

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

Examples

Example 6-85 Extracting One Text Line

This example shows how to extract the second line from a multiline text value in a variable called `mktglist`. The `mktglist` variable has the following values.

```Salespeople
Products
Services
```

The statement

```SHOW EXTLINES(mktglist 2 1)
```

produces the following output.

```Products
```

## EXTRACT

The EXTRACT function extracts and returns the value of a specified datetime value from a datetime or interval value expression. This function can be very useful for manipulating datetime values in very large variables.

Return Values

The value returned varies:

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

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

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

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

Syntax

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

Arguments

time

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

timezone_hour_or_minute

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

timezone_regn_or_abbr

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

datetime_exp

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

interval_exp

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

Notes

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

Examples

Example 6-86 Extracting the Hour from a Timestamp

```DEFINE mytimestamptz VARIABLE TIMESTAMP_TZ
REPORT mytimestamptz

MYTIMESTAMPTZ
------------------------------
26-MAR-06 12.00.00 AM -04:00

SHOW EXTRACT (TIMEZONE_HOUR FROM mytimestamptz)
-4.00
```

## FCOPEN

The FCOPEN function creates a forecasting context and returns a handle to this context.

You must use the FCOPEN function in combination with other OLAP DML statements as outlined in "Forecasting Programs".

Return Value

INTEGER

Syntax

FCOPEN(text-expression [prototype-handle])

Arguments

text-expression

The name of the forecasting context.

prototype-handle

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

Examples

For an example of a forecasting program, see Example 8-118, "A Forecasting Program".

## FCQUERY

The FCQUERY function queries the results of a forecast created when the FCEXEC command executed.

You must use the FCQUERY function in combination with other OLAP DML statements as outlined in "Forecasting Programs".

Return Value

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

Syntax

FCQUERY(HANDLELIST|handle-expression option -

[TRIAL trial-num] [CYCLE cycle-num])

Arguments

HANDLELIST

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

handle-expression

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

option

The specific information to retrieve:

• When you want information about the options specified for the entire forecast, do not use the TRIAL keyword. In this case, option can be any of the options that you can specify using the FCSET command and any of the options listed in Table 6-7, "Options That You Can Specify for the Entire Forecast".

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

Keyword Return type Description

HANDLEID

TEXT

The name of the forecasting context when a value was specified when the forecasting context was opened using the FCOPEN command; or `NA` when no name was specified at that time.

TRIALSRUN

INTEGER

The number of trials for which data is available; or `NA` when no trials were run.

• When you want information about a specific trial, use the TRIAL trial-num phrase. In this case, option can be any of the options listed in Table 6-8, "Options That You Can Specify for an Individual Trial".

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

Option Return Value Description

ALLOCLAST

BOOLEAN

Indicates whether the risk of over-adjustment should be reduced by allocating, instead of forecasting, the last cycle.

ALPHA

DOUBLE

The value of Alpha for this trial of the forecast. Alpha is the level or baseline parameter that is used for the Single Exponential Smoothing, Double Exponential Smoothing, and Holt-Winters forecasting methods.

BETA

DOUBLE

The value of Beta for this trial of the forecast. Beta is the trend parameter that controls the estimate of the trend. Beta is used for the Double Exponential Smoothing and Holt-Winters forecasting methods.

COMPSMOOTH

BOOLEAN

Indicates whether optimization should be done on the median smoothed data series.

CYCDECAY

DOUBLE

The value of the cyclic decay parameter for this trial of the forecast. Cyclical decay pertains to how seriously Oracle OLAP considers deviations from baseline activity when it performs linear and nonlinear regressions.

GAMMA

DOUBLE

The value of Gamma for this trial of the forecast. Gamma is the seasonal parameter that is used for the Holt-Winters forecasting method.

HISTUSED

INTEGER

The number of historical periods actually used, after all leading `NA` values are bypassed.

DOUBLE

The mean absolute deviation (MAD) for this trial of the forecast.

MAPE

DOUBLE

The mean average percent error (MAPE) for this trial of the forecast.

MAXFCFACTOR

DECIMAL

The upper bound of the forecast data.

METHOD

TEXT

The forecasting method that Oracle OLAP used for this trial of the forecast. See the METHOD option of the FCSET command for descriptions of the various methods.

MINFCFACTOR

DECIMAL

The lower bound of the forecast data.

MPTDECAY

DOUBLE

The value of the parameter that Oracle OLAP used when it adjusted the decay of estimates of base values that were used when it unraveled the predictions on the moving periodic total (MPT) series for this trial of the forecast.

NCYCLES

INTEGER

The number of cycles specified using the PERIODICITY argument to FCSET.

PERIODICITY

INTEGER

The length, in periods, of one or more cycles. The return value depends on the way you call the FCQUERY function:

When you specify the CYCLE argument, PERIODICITY returns the number of periods in the specified cycle.

When you do not specify the CYCLE argument and FCSET ALLOCLAST is `NO`, PERIODICITY returns the product of all cycle lengths.

When you do not specify the CYCLE argument and FCSET ALLOCLAST is `YES`, PERIODICITY returns the product of all cycle lengths leaving out the length of the last (least aggregate) cycle.

RMSE

DOUBLE

The root mean squared error (RMSE) for this trial of the forecast.

SMOOTHING

BOOLEAN

Indicates whether Oracle OLAP smoothed the data for this trial of the forecast. `YES` indicates that Oracle OLAP smoothed the data; `NO` indicates that Oracle OLAP did not smooth the data.

TRANSFORM

TEXT

The data filter that Oracle OLAP used for this trial of the forecast. See the TRANSFORM option of the FCSET command for descriptions of the various filters.

TRENDHOLD

DOUBLE

The value of the trend hold parameter for this trial of the forecast. trend hold parameter that indicates trend reliability in Double Exponential Smoothing and Holt-Winters forecasting methods.

trial-num

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

cycle-num

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

Notes

Using Options

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

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

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

Accessing Dimensioned Data

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

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

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

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

Examples

Example 6-87 Querying a Forecast

The `autofcst` program illustrated in Example 8-118, "A Forecasting Program" calls a program named `queryall`. The `queryall` program retrieves the characteristics of the trials of the forecast using the following code.

```DEFINE queryall PROGRAM
PROGRAM
VARIABLE numtrials INTEGER
VARIABLE loopindx INTEGER
numtrials = FCQUERY(hndl trialsrun)
row numtrials 'TRIALS'
loopindx = 1
WHILE loopindx LE numtrials
DO
ROW loopindx 'METHOD' FCQUERY(hndl method trial loopindx)
ROW loopindx 'TRANSFORM' FCQUERY(hndl transform trial loopindx)
ROW loopindx 'SMOOTHING' FCQUERY(hndl smoothing trial loopindx)
ROW loopindx 'ALPHA' FCQUERY(hndl alpha trial loopindx)
ROW loopindx 'BETA' FCQUERY(hndl beta trial loopindx)
ROW loopindx 'GAMMA' FCQUERY(hndl gamma trial loopindx)
ROW loopindx 'TRENDHOLD' FCQUERY(hndl trendhold trial loopindx)
ROW loopindx 'CYCDECAY' FCQUERY(hndl cycdecay trial loopindx)
row loopindx 'MPTDECAY' FCQUERY(hndl mptdecay trial loopindx)
ROW loopindx 'MAPE' FCQUERY(hndl mape trial loopindx)
ROW loopindx 'RMSE' FCQUERY(hndl rmse trial loopindx)
loopindx = loopindx + 1
DOEND
END
```

A sample report created from the output of the QUERYALL program follows.

```3 TRIALS
1 METHOD     HOLT/WINTERS
1 TRANSFORM  TRNOSEA
1 SMOOTHING          NO
1 ALPHA             0.2
1 BETA              0.3
1 GAMMA             0.3
1 TRENDHOLD         0.8
1 CYCDECAY           -1
1 MPTDECAY           -1
1 MAPE       23.6192147
1 RMSE        389.40202
2 METHOD     HOLT/WINTERS
2 TRANSFORM  TRNOSEA
2 SMOOTHING          NO
2 ALPHA             0.2
2 BETA              0.3
2 GAMMA             0.2
2 TRENDHOLD         0.8
2 CYCDECAY           -1
2 MPTDECAY           -1
2 MAPE       23.6192147
2 RMSE        389.40202
3 METHOD     HOLT/WINTERS
3 TRANSFORM  TRNOSEA
3 SMOOTHING          NO
3 ALPHA             0.2
3 BETA              0.3
3 GAMMA             0.1
3 TRENDHOLD         0.8
3 CYCDECAY           -1
3 MPTDECAY           -1
3 MAPE       23.6192147
3 RMSE        389.40202
```

## FILEERROR

The FILEERROR function returns information about the first error that occurred when you are processing a record from an input file with the data reading statements FILEREAD and FILEVIEW. It can tell you what type of error occurred and where Oracle OLAP was in the record. The keyword you specify as an argument determines the kind of information that is returned.

Call FILEERROR once to find out the type of error. Then, you can call FILEERROR again to get more details about what caused the error. The return values for the type of error are also FILEERROR keywords. When FILEERROR returns a value other than `NA`, then you would probably call FILEERROR a second time using the return value itself as an argument.

The abbreviation for FILEERROR is FILEERR

Return Value

Varies depending on the specified keyword.

Syntax

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

Arguments

TYPE

Returns a text expression that specifies the type of error that has occurred. The types of errors and their meanings are listed in Table 6-9, "Types of Errors Returned by FILEERROR".

Table 6-9 Types of Errors Returned by FILEERROR

Return Value Meaning

DIMENSION

The data reading statements tried to set the status of a dimension (through an implicit or explicit MATCH attribute), but the specified position or value did not exist.

NA

No error occurred in the processing of the current record.

POSITION

The data reading program tried to read from an invalid location in the record. A POSITION error can occur when the field or column is before the beginning of the record or when the field extends past the end of the record. An error beyond the end of the record occurs only for binary or packed data; for symbolic (textual) data, the data reading statements pad short records with blanks.

VALUE

The value could not be converted to the requested data type. For packed data, the record had an invalid hexadecimal digit.

WIDTH

The data reading statements specified an invalid field width. Invalid widths depend on the format of the data, which can be symbolic, packed, or binary:

• For symbolic format, the width is invalid when it is less than 1 or when it is `NA`. Note that `NA` is acceptable for ID data.

• For packed format, the width is invalid when it is less than 1, greater than 8, or `NA`.

For binary format, the width requirement depends on whether the data is `INTEGER` or `DECIMAL` (floating-point). Integer data must have a width of 1, 2, or 4. Decimal data must have a width of 4 or 8.

POSITION

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

WIDTH

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

VALUE

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

DIMENSION

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

Notes

Flow of Control

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

Displaying Error Messages in the Current Outfile

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

Examples

Example 6-88 Error-Handling with TRAP

This example shows a sample trap label (ERROR:) and the error-handling code that follows it. (For information on error trapping and trap labels, see the TRAP command.) The code checks whether the file has been opened. If so, it checks whether the error that caused the branch is a data reading error. When it is, the program calls FILEERROR in a SHOW command to display information about the error. The body of the program (not shown) contains code that opens the file and assigns a file unit number to the variable `fil.unit`. ERRTYPE is a local variable that is declared at the beginning of the program.

```error:
IF fil.unit EQ NA
THEN DO
POPLEVEL 'save'
RETURN
DOEND
IF ERRORNAME NE 'attn'
THEN DO
ERRTYPE = FILEERROR(TYPE)
IF ERRTYPE NE NA
THEN SHOW JOINCHARS('Error in record ' RECNO(fil.unit) -
' in column ' FILEERROR(POSITION) ': ' -
ERRTYPE ' ' FILEERROR(&ERRTYPE))
TRAP ON ERROR
GOTO NEXT
DOEND
FILECLOSE fil.unit
POPLEVEL 'save'
RETURN
```

## FILEGET

The FILEGET function returns text from a non-binary file that has been opened for reading. When FILEGET reaches the end of the file, it returns `NA`. All text read with FILEGET is translated into the database character set. FILEGET cannot read data that cannot be represented in the database character set.

Return Value

TEXT

Syntax

FILEGET(fileunit [LENGTH int-expression])

Arguments

fileunit

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

LENGTH int-expression

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

Notes

Difference Between Number of Bytes Read and Number of Bytes Returned

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

Examples

Example 6-89 Program for Reading a File

Suppose you have a program called `readfile` that takes a file name as its argument. It opens the file, reads the lines of the file, adds them to a multiline text variable named `wholetext`, then closes it. `readfile` uses local variables to store the fileunit number and each line of the file as it is read.

```DEFINE wholetext VARIABLE TEXT
LD Multiline text variable
LD Program to store data from a file in a multiline text variable
PROGRAM
VARIABLE fil.unit INTEGER  "Local Var To Store File Unit
VARIABLE fil.text TEXT     "Local Var To Store Single Lines
FIL.TEXT = FILEGET(fil.unit)        "Read The First Line
WHILE fil.text NE NA                "Test For End-of-file
DO
wholetext = JOINLINES(wholetext, fil.text)
fil.text = FILEGET(fil.unit)      "Read The Next Line
DOEND
FILECLOSE fil.unit
END
```

## FILENEXT

The FILENEXT function makes a record available for processing by the FILEVIEW command. It returns `YES` when it was able to read a record and `NO` when it reached the end of the file.

Return Value

BOOLEAN

Syntax

FILENEXT(fileunit)

Arguments

fileunit

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

Notes

Opening and Closing Files

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

Processing Data

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

Automatic Looping

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

Writing Records

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

Record Numbers

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

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

Examples

Example 6-90 Program That Uses FILENEXT

Suppose you receive monthly sales data in a file with the following record layout.

```Column        Width         Format             Data

1             1             Text               Division code
2             10            Text               District name
12            10            Text               Product name
30            4             Packed binary      Sales in dollars
34            4             Packed binary      Sales in units
```

You want to process records only for your division, whose code is A. The following program excerpt opens the file, reads the lines of the file, determines if the data is for division A and, if so, reads the sales data, then closes the file. The file name is given as an argument on the statement line after the program name.

```VARIABLE fil.unit INTEGER
. . .
LIMIT month TO &arg(2)

WHILE FILENEXT(fil.unit)
DO
FILEVIEW fil.unit WIDTH 1 rectype
IF rectype EQ 'A'
THEN FILEVIEW fil.unit COLUMN 2 WIDTH 10 district -
WIDTH 10 product -
COLUMN 30 WIDTH 4 BINARY sales -
WIDTH 4 BINARY UNITS
DOEND
FILECLOSE fil.unit
```

## FILEOPEN

The FILEOPEN function opens a file, assigns it a fileunit number (an arbitrary `INTEGER`), and returns that number. You use the fileunit number, rather than a file name, in any further references to the file. When Oracle OLAP cannot open the file, an error occurs.

OUTFILE

Return Value

INTEGER

Syntax

Arguments

file-name

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

Note:

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

(Abbreviated R) Opens the file for reading.

WRITE

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

APPEND

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

BINARY

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

NLS_CHARSET charset-exp

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

Notes

Multiple File Units

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

Access Modes

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

Examples

Example 6-91 FILEOPEN with an Argument Passed into a Program

The following line from a program opens a file whose name was specified as a program argument and saves the fileunit number in the variable `fil.unit`.

```fil.unit = FILEOPEN(ARG(1), READ)
```

Example 6-92 FILEOPEN with a Binary File

The following statements open a binary file and set the record length.

```VARIABLE filenum INTEGER
FILESET filenum LSIZE 132
```

## FILEQUERY

The FILEQUERY function returns information about a file. The attribute argument you specify in your FILEQUERY function call determines the type of information that is returned.

Return Value

The data type of the return value depends on the attribute you specify. See Table 6-10, "File Attributes Returned by FILEQUERY" for more information.

Syntax

FILEQUERY(file-id attrib-arg)

Arguments

file-id

A fileunit number or a file name.

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

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

Note:

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

Some attributes require that you specify a fileunit number; others require the file name. In many cases, you can specify either. See Table 6-10, "File Attributes Returned by FILEQUERY" for more information.

attrib-arg

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

Table 6-10 File Attributes Returned by FILEQUERY

Keyword Return Values Return Data Type file-id Argument

APPEND

`TRUE` when the file is open for writing at the end (that is, `TRUE` for APPEND and WRITE); `FALSE` when it is not.

`BOOLEAN`

Fileunit number

BMARGIN

The number of blank lines that form the bottom margin.

`INTEGER`

Fileunit number

CHANGED

`TRUE` when the file's archive bit is set; `FALSE` when it is not.

`BOOLEAN`

Fileunit number or file name

EOF

`TRUE` when end-of-file has been reached; `FALSE` when it is not.

`BOOLEAN`

Fileunit number

EXISTS

`TRUE` when the file exists; `FALSE` when it is not.

`BOOLEAN`

Fileunit number or file name

FILENAME

The file name associated with the fileunit.

`TEXT`

Fileunit number

LINENUM

The current line number. Resets after each page break when PAGING is on; keeps incrementing when PAGING is off. When file is currently open in READ mode, returns the current record number.

`INTEGER`

Fileunit number

LINESLEFT

The number of lines left on the page.

`INTEGER`

Fileunit number

LSIZE

For a file that is open for writing, the line length for the standard Oracle OLAP page heading. (See the STDHDR program.) For a fileunit that is open for reading, specifies the record length for binary input files.

`INTEGER`

Fileunit number

NLS_CHARSET

The character set being used for this fileunit. See the FILEOPEN function for more information.

`TEXT`

Fileunit number

NUMBYTES

The size of the file in bytes.

`INTEGER`

Fileunit number or file name

ORIGIN

The type of computer on which the file was created. The ORIGIN attribute, which is relevant only for files that are open for reading, is set when you issue a FILESET statement.

`TEXT`

Fileunit number

PAGENUM

The current page number. See "Paging Attributes".

`INTEGER`

Fileunit number

PAGEPRG

The Oracle OLAP program or statement that produces headings when output is paged. See "Paging Attributes".

`TEXT`

Fileunit number

PAGESIZE

The number of lines on each page. See "Paging Attributes".

`INTEGER`

Fileunit number

PAGING

`TRUE` when the output is formatted in pages; `FALSE` when it is not. See "Paging Attributes".

`BOOLEAN`

Fileunit number

PAUSEATPAGEEND

`TRUE` when Oracle OLAP pauses after each page; `FALSE` when it does not. See "Paging Attributes".

`BOOLEAN`

Fileunit number

`TRUE` when the file is open for reading; `FALSE` when it is not.

`BOOLEAN`

Fileunit number

RO

`TRUE` when the file's read-only attribute is set; `FALSE` when it is not.

`BOOLEAN`

Fileunit number or file name

TABEXPAND

`TRUE` when the tab characters are expanded when the file is read by FILEGET or FILEREAD; `FALSE` when they are not. See "Tab Treatment".

`BOOLEAN`

Fileunit number or file name

TMARGIN

The number of blank lines that form the top margin.

`INTEGER`

Fileunit number

UNIT

The file unit for the specified file name.

`INTEGER`

File name

W[RITE]

`TRUE` when the file is open for writing; `FALSE` when it is not.

`BOOLEAN`

Fileunit number

Notes

Tab Treatment

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

Paging Attributes

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

Wildcard Characters

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

Examples

Example 6-93 Setting Paging Options for a File Opened for Writing

The following statements show how the paging options are set for a file opened for writing.

```DEFINE fil.unit INTEGER
fil.unit = FILEOPEN('REPORT' WRITE)
```
• The statement

```SHOW FILEQUERY(fil.unit PAGING)
```

produces the following output.

```YES
```
• The statement

```SHOW FILEQUERY(fil.unit PAGESIZE)
```

produces the following output.

```66
```
• The statement

```SHOW FILEQUERY(fil.unit TMARGIN)
```

produces the following output.

```5
```

The following statement closes the file.

```FILECLOSE fil.unit
```

## FILTERLINES

The FILTERLINES function applies a filter expression that you create to each line of a multiline text expression.

Return Value

TEXT or NTEXT

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

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

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

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

Syntax

FILTERLINES(source-expression filter-expression)

Arguments

source-expression

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

filter-expression

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

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

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

Notes

The Result of FILTERLINES

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

Examples

Example 6-94 Removing Extension From File Names

The following example shows how FILTERLINES could be used on a list of file names to produce a list of those same file names without extensions.

With a multiline text variable named `filelist` that evaluates to

```myfile1.txt
file2.txt
myfile3
file4.txt
```

the statement

```SHOW FILTERLINES(FILELIST -
IF FINDCHARS(VALUE '.') GT 0 -
THEN EXTCHARS(VALUE 1 FINDCHARS(VALUE '.') -1) -
ELSE VALUE)
```

produces the following output.

```myfile1
file2
myfile3
file4
```

## FINDBYTES

The FINDBYTES function returns the byte position of the beginning of a specified group of bytes within a text expression.

Tip:

When you are using a single-byte character set, you can use the FINDCHARS function instead of the FINDBYTES function.

Return Value

INTEGER

Syntax

FINDBYTES(text-expressionbytes [starting-pos [LINENUM]])

Arguments

text-expression

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

Tip:

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

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

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

starting-pos

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

LINENUM

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

Examples

Example 6-95 Finding the Starting Position of a Byte Group

This example shows how to find the starting position of various groups of bytes in the literal TEXT value `hellotherejoe`.

The statement

```SHOW FINDBYTES('hellotherejoe', 'joe')
```

produces the following output.

```11
```

The statement

```SHOW FINDBYTES('hellotherejoe', 'al')
```

produces the following output.

```0
```

## FINDCHARS

The FINDCHARS function returns the character position of the beginning of a specified group of characters within a text expression.

Tip:

When you are using a multibyte character set, you can use the FINDBYTES function instead of the FINDCHARS function.

Return Value

INTEGER

Syntax

FINDCHARS(text-expressioncharacters [starting-pos [LINENUM]])

Arguments

text-expression

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

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

characters

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

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

starting-pos

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

LINENUM

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

Examples

Example 6-96 Finding the Starting Position of a Character Group

This example shows how to find the starting position of various groups of characters in the literal TEXT value `hellotherejoe`.

The statement

```SHOW FINDCHARS('hellotherejoe', 'joe')
```

produces the following output.

```11
```

The statement

```SHOW FINDCHARS('hellotherejoe', 'al')
```

produces the following output.

```0
```

## FINDLINES

The FINDLINES function determines the position of one or more lines in a multiline text expression.

Return Value

INTEGER

Syntax

FINDLINES(text-expressionlines)

Arguments

text-expression

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

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

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

lines

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

Examples

Example 6-97 Finding Two Sequential Lines

This example shows how to find the location of the two lines "products" and "services" in a multiline value in a TEXT variable called `newlist`. The `newlist` variable has the following values.

```salespeople
products
services
regions
priorities
```

The characters "`\n`" in the lines argument to the following FINDLINES function call indicates a line break to show that "product" and "services" are separate lines.

```SHOW FINDLINES(newlist, 'products\nservices')
```

The result of this statement is

```2
```

## FINTSCHED

The FINTSCHED function calculates the interest portion of the payments on a series of fixed-rate installment loans that are paid off over a specified number of time periods. For each time period, you specify the amount of the loans incurred during that time period and a single interest rate that applies to those loans over their lifetime.

FINTSCHED calculates the result for a given time period as the sum of the interest due on each loan that is incurred or outstanding in that period.

Return Value

DECIMAL

The result returned by the FINTSCHED function is dimensioned by the union of all the dimensions of loans, rates, n, and the dimension used as the time-dimension argument.

Syntax

FINTSCHED(loansratesn, [time-dimension] [STATUS])

Arguments

loans

A numeric expression that contains the initial amounts of the loans. When loans does not have a time dimension, or when loans is dimensioned by multiple time dimensions, the time-dimension argument is required.

rates

A numeric expression that contains the interest rates charged for loans. When rates is a dimensioned variable, it can be dimensioned by any dimension, including a different time dimension. When rates is dimensioned by a time dimension, you specify the interest rate in each time period that applies to the loans incurred in that period. The interest rate for the time period in which a loan is incurred applies throughout the lifetime of that loan. The rates are expressed as decimal values; for example, a 5 percent rate is expressed as`.05`.

n

A numeric expression that specifies the number of payments required to pay off the loans in the series. The n expression can be a dimensioned variable, but it cannot be dimensioned by the time dimension argument. One payment is made in each time period of the time dimension by which loans is dimensioned or in each time period of the dimension specified in the time-dimension argument. For example, one payment is made each month when loans is dimensioned by MONTH.

time-dimension

The name of the dimension along which the interest payments are calculated. When the time dimension has a type of DAY, WEEK, MONTH, QUARTER, or YEAR, the time-dimension argument is optional, unless loans has multiple time dimensions.

STATUS

Specifies that FINTSCHED should use the current status list (that is, only the dimension values currently in status in their current status order) when computing the interest portion of the payments. By default FINTSCHED uses the default status list.

Notes

FINTSCHED and NA Values

When loans has a value other than `NA` and the corresponding value of rates is `NA`, an error occurs.

FINTSCHED is affected by the NASKIP option. When NASKIP is set to `YES` (the default), and a loan value is `NA` for the affected time period, the result returned by FINTSCHED depends on whether the corresponding interest rate has a value of `NA` or a value other than `NA`. Table 6-11, "Effect of NASKIP When Loan or Rate Values are NA for a Time Period" illustrates how NASKIP affects the results when a loan or rate value is `NA` for a given time period.

Table 6-11 Effect of NASKIP When Loan or Rate Values are NA for a Time Period

Loan Value Rate Value Result When NASKIP = YES Result When NASKIP = NO

Non-`NA`

`NA`

Error

Error

`NA`

Non-`NA`

Interest values

(`NA` loan value is treated as zero)

`NA` for the affected time periods

`NA`

`NA`

`NA` for affected time periods

`NA` for the affected time periods

As an example, suppose a loan expression and a corresponding interest expression both have `NA` values for 1997 but both have values other than `NA` for succeeding years. When the number of payments is 3, FINTSCHED returns `NA` for 1997, 1998, and 1999. For 2000, FINTSCHED returns the interest portion of the payment due for loans incurred in 1998, 1999, and 2000.

FINTSCHED Ignores the Status of the Time Dimension

The FINTSCHED calculation begins with the first time dimension value, regardless of how the status of that dimension may be limited. For example, suppose loans is dimensioned by `year`, and the values of `year` range from `Yr95` to `Yr99`. The calculation always begins with `Yr95`, even when you limit the status of `year` so that it does not include `Yr95`.

However, when loans is not dimensioned by the time dimension, the FINTSCHED calculation begins with the first value in the current status of the time dimension. For example, suppose loans is not dimensioned by `year`, but `year` is specified as time-dimension. When the status of `year` is limited to `Yr97` to `Yr99`, the calculation begins with `Yr97` instead of `Yr95`.

Examples

Example 6-98 Calculating Interest

The following statements create two variables called `loans` and `rates`.

```DEFINE loans DECIMAL <year>
DEFINE rates DECIMAL <year>
```

Suppose you assign the following values to the variables `loans` and `rates`.

```YEAR             LOANS      RATES
-------------- ---------- ----------
Yr95               100.00       0.05
Yr96               200.00       0.06
Yr97               300.00       0.07
Yr98                 0.00       0.00
Yr99                 0.00       0.00
```

For each year, `loans` contains the initial value of the fixed-rate loan incurred during that year. For each year, the value of `rates` is the interest rate that is charged for any loans incurred in that year; for those loans, this same rate is charged each year until the loans are paid off.

The following statement specifies that each loan is to be paid off in three payments, calculates the interest portion of the payments on the loans,

```REPORT W 20 HEADING 'Payment' FINTSCHED(loans, rates, 3, year)
```

and produces the following report.

```YEAR                        Payment
--------------   --------------------
Yr95                           5.00
Yr96                          15.41
Yr97                          30.98
Yr98                          18.70
Yr99                           7.48
```

The interest payment for 1995 is interest on the loan of \$100 incurred in 1995, at 5 percent. The interest payment for 1996 is the sum of the interest on the remaining principal of the 1995 loan, at 5 percent, plus interest on the loan of \$200 incurred in 1996, at 6 percent. The 1997 interest payment is the sum of the interest on the remaining principal of the 1995 loan, at 5 percent; interest on the remaining principal of the 1996 loan, at 6 percent; and interest on the loan of \$300 incurred in 1997, at 7 percent. Since the 1995 loan is paid off in 1997, the payment for 1998 represents interest on the remaining principal of the 1996 and 1997 loans. In 1999, the interest payment is on the remaining principal of the 1997 loan.

## FLOOR

The FLOOR function returns the largest whole number equal to or less than a specified number.

Return Value

NUMBER

Syntax

FLOOR(n)

Arguments

n

A number.

Examples

Example 6-99 Displaying the Largest Integer Equal to or Less Than a Number

The following statements show results returned by the FLOOR function.

• The following `SHOW FLOOR` statement produces the result that follows it.

```SHOW FLOOR(15.7)

15
```
• The following `SHOW FLOOR` statement produces the result that follows it.

```SHOW FLOOR(4)

4
```
• The following `SHOW FLOOR` statement produces the result that follows it.

```SHOW FLOOR(-6.457)

-7
```

## FPMTSCHED

The FPMTSCHED function calculates a payment schedule (principal plus interest) for paying off a series of fixed-rate installment loans over a specified number of time periods. For each time period, you specify the amount of the loans incurred during that time period and a single interest rate that applies to those loans over their lifetime.

FPMTSCHED calculates the payment for a given time period as the sum of the principal and interest due on each loan that is incurred or outstanding in that period.

Return Value

DECIMAL

The result returned by the FPMTSCHED function is dimensioned by the union of all the dimensions of loans and rates and the dimension used as the time-dimension argument.

Syntax

FPMTSCHED(loansratesn, [time-dimension] [STATUS])

Arguments

loans

A numeric expression that contains the initial amounts of the loans. When loans does not have a time dimension, or when loans is dimensioned by multiple time dimensions, the time-dimension argument is required.

rates

A numeric expression that contains the interest rates charged for loans. When rates is a dimensioned variable, it can be dimensioned by any dimension, including a different time dimension. When rates is dimensioned by a time dimension, you specify the interest rate in each time period that applies to the loans incurred in that period. The interest rate for the time period in which a loan is incurred applies throughout the lifetime of that loan. The rates are expressed as decimal values; for example, a 5 percent rate is expressed as`.05`.

n

A numeric expression that specifies the number of payments required to pay off the loans in the series. The n expression can be dimensioned, but it cannot be dimensioned by the time dimension argument. One payment is made in each time period of the time dimension by which loans is dimensioned or in each time period of the dimension specified in the time-dimension argument. For example, one payment each month is made when loans is dimensioned by `month`.

time-dimension

The name of the dimension along which the interest payments are calculated. When the time dimension for loans has a type of DAY, WEEK, MONTH, QUARTER, or YEAR, the time-dimension argument is optional, unless loans has multiple time dimensions.

STATUS

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

Notes

FPMTSCHED and NA Values

When loans has a value other than `NA` and the corresponding value of rates is `NA`, an error occurs.

FPMTSCHED is affected by the NASKIP option. When NASKIP is set to `YES` (the default), and a loan value is `NA` for the affected time period, the result returned by FPMTSCHED depends on whether the corresponding interest rate has a value of `NA` or a value other than `NA`. Table 6-11, "Effect of NASKIP When Loan or Rate Values are NA for a Time Period" illustrates how NASKIP affects the results when a loan or rate value is `NA` for a given time period.

As an example, suppose a loan expression and a corresponding interest expression both have `NA` values for 1997 but both have values other than `NA` for succeeding years. When the number of payments is 3, FPMTSCHED returns `NA` for 1997, 1998, and 1999. For 2000, FPMTSCHED returns the payment due for loans incurred in 1998, 1999, and 2000.

FPMTSCHED Ignores the Status of the Time Dimension

The FPMTSCHED calculation begins with the first time dimension value, regardless of how the status of that dimension may be limited. For example, suppose loans is dimensioned by `year`, and the values of `year` range from `Yr95` to `Yr99`. The calculation always begins with `Yr95`, even when you limit the status of `year` so that it does not include `Yr95`.

However, when loans is not dimensioned by the time dimension, the FPMTSCHED calculation begins with the first value in the current status of the time dimension. For example, suppose loans is not dimensioned by `year`, but `year` is specified as time-dimension. When the status of `year` is limited to `Yr97` to `Yr99`, the calculation begins with `Yr97` instead of `Yr95`.

Examples

Example 6-100 Calculating a Payment Schedule

The following statements create two variables called `loans` and `rates`.

```DEFINE loans DECIMAL <year>
DEFINE rates DECIMAL <year>
```

Suppose you assign the following values to the variables `loans` and `rates`.

```year             loans         rates
-------------- ---------- ----------
Yr95               100.00       0.05
Yr96               200.00       0.06
Yr97               300.00       0.07
Yr98                 0.00       0.00
Yr99                 0.00       0.00
```

For each year, `loans` contains the initial value of the fixed-rate loan incurred during that year. For each year, the value of `rates` is the interest rate that is charged for any loans incurred in that year; for those loans, this same rate is charged each year until the loans are paid off.

The following statement specifies that each loan is to be paid off in three payments, calculates the schedule for paying off the principal and interest on the loans,

```REPORT W 20 HEADING 'Payment' FPMTSCHED(loans, rates, 3, year)
```

and produces the following report.

```YEAR                        Payment
-------------- --------------------
Yr95                          36.72
Yr96                         111.54
Yr97                         225.86
Yr98                         189.14
Yr99                         114.32
```

The payment for 1995 is the principal due on the loan of \$100 incurred in 1995, plus interest on the loan at 5 percent. The payment due in 1996 is the sum of the second payment on the loan incurred in 1995 (principal plus 5 percent interest), plus the first payment on the loan of \$200 incurred in 1996 (principal plus 6 percent interest). The 1997 payment is the sum of the third and final payment on the loan incurred in 1995, the second of the three payments on the 1996 loan, and the first payment on the loan of \$300 incurred in 1997 (principal plus 7 percent interest). Since the 1995 loan is paid off in 1997, the payment for 1998 covers the principal and interest for the 1996 and 1997 loans. The payment for 1999 is the final payment of principal and interest for the 1997 loan.

Example 6-101 Determining Monthly Payments

The following statement determines what the monthly payments would be on a \$125,000 loan with an 8.75 percent annual interest rate,

```SHOW FPMTSCHED(125000, .0875/12, 360, month)
```

and produces the following output.

```983.38
```

## FROM_TZ

The FROM_TZ function converts a timestamp value and a time zone to a `TIMESTAMP_TZ` value.

Return Values

`TIMESTAMP_TZ`

Syntax

FROM_TZ (timestamp_value , time_zone_value)

Arguments

timestamp_value

A text expression with a TIMESTAMP data type.

time_zone_value

A text expression that returns a string in the format TZH:TZM or in TZR with optional TZD format.

See "Datetime Expressions" for information on specifying timestamp and time zone values.

Examples

Example 6-102 Creating a TIMESTAMP_TZ Value from a Timestamp Value and a Time Zone

```DEFINE mytimestamp VARIABLE TIMESTAMP
DEFINE mytimezone VARIABLE TEXT
DEFINE mytimestamptz VARIABLE TIMESTAMP_TZ
mytimestamp = '26-MAR-06'
mytimezone = '-04:00'
mytimestamptz = FROM_TZ (mytimestamp mytimezone)
REPORT mytimestamptz

MYTIMESTAMPTZ
------------------------------
26-MAR-06 12.00.00 AM -04:00
```

## GET

The GET function requests input from the current input stream. The input may be a single item of data, a dimension value, an analytic workspace object, or simply the next item in the input stream. The simplest form of the GET function requests a value of a certain data type.

GET(datatype)

GET also provides several arguments that verify the input.

Because GET is a function, it must be used in an OLAP DML command. It also may be used in an assignment statement to store the input in a variable for later use, or in a LIMIT command to set the status of a dimension. GET can be used in programs to request information necessary for the completion of the program.

Return Value

The return value depends on the input that you request, as described in the syntax.

Syntax

GET({RAW TEXT|[NEW|VALID|POSLIST] input} -

[VERIFY condition-exp [IFNOT result-exp]])

where input is one of the following:

dim-name
NAME
datatype

Arguments

dim-name

A text expression specifying the name of a dimension. When you specify dim-name, GET requests a value of this dimension as input and verifies that the input is a valid value of the dimension.

RAW TEXT

Specifies that GET should return the next item in the input stream exactly as it is entered. See "GET with RAW TEXT".

NEW dim-name

The NEW keyword with the dim-name argument causes GET to request a new value for the dimension. When requesting a dimension value with NEW, GET verifies that the input is not already a value of the dimension.

VALID dim-name

The VALID keyword with the dim-name argument causes GET to request either a new value or an existing value of the dimension. When requesting a dimension value with VALID, GET verifies that the input is either an existing dimension value or a valid new dimension value.

POSLIST dim-name

The POSLIST keyword with the dim-name argument causes GET to request a dimension value identified by its position in the dimension. When requesting a dimension value with POSLIST, GET verifies that the input is an existing position number in the dimension. See "GET with POSLIST".

NAME

Indicates that GET is requesting the name of an object in the current analytic workspace. When you specify NAME, GET verifies that the input is an object that exists in the current analytic workspace. The object name must not be enclosed in single quotes, and it must follow the rules for valid object names explained in the main DEFINE entry. GET automatically converts the object name to uppercase.

NEW NAME

The NEW NAME keywords cause GET to request a name for a new analytic workspace object. When requesting an analytic workspace object name with NEW, GET verifies that the input is not already the name of an object in any attached analytic workspace (including `EXPRESS.DB`).

VALID NAME

The VALID NAME keywords cause GET to request a name for an analytic workspace object. When requesting an analytic workspace object name with VALID, GET verifies that the input follows the rules for valid object names, even when there is no current analytic workspace and regardless of whether the name exists.

POSLIST NAME

The POSLIST NAME keywords cause GET to request an analytic workspace object name identified by its position in the NAME dimension. When requesting an analytic workspace object name with POSLIST, GET verifies that the input is an existing position number in the NAME dimension.

datatype

Specifies the type of data being requested by GET which can be any of the Oracle OLAP data types: INTEGER, SHORTINTEGER, DECIMAL, SHORTDECIMAL, BOOLEAN, ID, TEXT, or DATE. GET accepts a value of `NA` when requesting any data type.

VERIFY condition-exp [IFNOT result-exp]

With VERIFY, you can specify a Boolean condition that must be satisfied by the input to GET. The keyword VALUE may be used in condition-exp to test the input before any assignment is made. For example, when requesting a value of LSIZE, the Boolean condition might be as follows.

```VALUE NE NA AND VALUE GE 1 AND VALUE LE 80
```

The IFNOT clause specifies a text expression to provide for occasions when the input does not satisfy condition-exp. For example, you might jump to an error-handling routine in your program. When you do not use IFNOT and an error occurs, GET produces an error message and then resumes waiting for input.

Notes

Current Input Stream

Oracle OLAP obtains statements for processing from the current input stream. You can override your default input stream with an INFILE statement. INFILE causes Oracle OLAP to read input from a file. Each line of the infile must contain a single statement.

Input from INFILE

When the GET function is in an infile, Oracle OLAP considers the next line in the infile to be the input to GET. You must be sure you supply the expected input for GET in the line or lines following the statement that invokes the GET function.

For example, suppose your infile contains a line invoking a report program that calls GET to obtain the number of decimal places to use. The infile then continues with other statements. When you do not put the desired number of decimal places on the line following the program call, GET examines line after line in the infile looking for the expected numeric response, rather than executing those lines as statements. See "Using GET to Obtain Textual Value".

INTEGER Dimension Values

When GET requests a value of an INTEGER dimension, the input should usually be in the form of a dimension-value position number

Non-INTEGER Dimension Values

Non-integer dimension values must be entered in uppercase and enclosed in single quotes.

Entering Values for DWMQY Dimensions

Values of DAY, WEEK, MONTH, QUARTER, or YEAR dimensions may be entered in the format of the dimension's VNF (or in the format of the default VNF when the dimension does not have a VNF of its own) or as a date. See the VNF command for an explanation of how to enter values in a VNF format. See "Date-only Input Values" for an explanation the valid input styles for entering values as dates.

Whether you use the VNF format or specify the value as a date, you must specify only the date components that are relevant for this type of time dimension. For example, for a MONTH dimension, you must supply only the month and year.

TEXT or ID Values

TEXT and ID values provided as input to GET retain the case in which they were entered. You do not have to enclose TEXT and ID values in quotes unless they begin with single or double quotes, or contain embedded blanks or escape sequences, such as `\dnnn` or `\n`. (Remember to precede any single quote in the value with a backslash (`\'`) so Oracle OLAP interprets it literally.)

DATE-only Values

When GET requests a DATE value, you can provide the input in any of the valid styles for dates, as explained in "Date-only Input Values". Oracle OLAP uses the current value of the DATEORDER option to resolve any ambiguity in the DATE-only value.

Numeric Values

GET rounds a SHORTDECIMAL or DECIMAL value when converting it into an INTEGER value. When GET requests an INTEGER or SHORTINTEGER value and the input is a number beyond the range for that data type, GET produces an error message and resumes waiting for input.

GET with RAW TEXT

When GET requests RAW TEXT input and no input is provided, GET returns a null string (`''`). For any type of information other than RAW TEXT, GET waits until input is provided.

GET with POSLIST

When you use the POSLIST keyword with the GET function, Oracle OLAP requires that you enter a position value to identify the dimension value rather than the dimension name. The syntax for the POSLIST keyword depends on whether you are using the GET function with either an assignment statement created using an assignment statement or the LIMIT command. When you want to set a variable equal to the result of a GET function, use the following syntax.

expression = GET(POSLIST dimension)

When you want to limit a dimension to a value returned by a GET function, you specify the POSLIST keyword twice, as shown in the following syntax.

LIMIT dimension TO POSLIST GET(POSLIST dimension)

Examples

Example 6-103 Using GET to Obtain Textual Value

Suppose you have written an Oracle OLAP program called `myconn`. This program contains a call to GET that requests a textual value.

```DEFINE myconn PROGRAM
PROGRAM
...
MYTEXT = GET(TEXT)
...
END
```

## GREATEST

The GREATEST function returns the largest expression in a list of expressions. All expressions after the first are implicitly converted to the data type of the first expression before the comparison.

To retrieve the smallest expression in a list of expressions, use LEAST.

Return Value

The data type of the first expression.

Syntax

GREATEST (expr [, expr]...)

Arguments

expr

An expression.

Examples

Example 6-104 Finding the Text Expression that is Last Alphabetically

The following statement selects the string that is last in alphabetic sequence.

```SHOW GREATEST ('Harry', 'Harriot', 'Harold')
Harry
```

Example 6-105 Finding the Largest Numerical Expression

The following statement selects the number with the greatest value.

```SHOW GREATEST (5, 3, 18)
18
```

## GROUPINGID function

The GROUPINGID function retrieves a grouping id for the value of a hierarchical dimension using a grouping relation previously created by the GROUPINGID command.

Return Values

NUMBER

Syntax

GROUPINGID (gidrel...)

Arguments

gidrel

A grouping id relation for the hierarchical dimension that you previously created using the GROUPINGID command.

Examples

Example 6-106 Retrieving the Value of a Single GroupingID

Assume that you have use the GROUPINGID command to define grouping ids for the two hierarchies in the `geog` dimension as described in Example 8-144, "Using GROUPINGID Command to Populate a Relation with Grouping Ids". Now you can use the GROUPINGID function to retrieve the grouping id of a value in the `geog` dimension.

```" For the Political Geog hierarchy
LIMIT geog TO 'Hartford'
LIMIT geog_hierlist TO 'Political_Geog'
SHOW GROUPINGID(geog_gidrel)
0.00
SHOW OBJ(PROPERTY '\$GID_DEPTH' 'geog_gidrel')
4
LIMIT geog TO ALL
SHOW GROUPINGID(geog_gidrel)
3.00
SHOW OBJ(PROPERTY '\$GID_DEPTH' 'geog_gidrel')
4

" For the Sales Geog hierarchy
LIMIT geog TO 'Hartford'
LIMIT geog_hierlist TO 'Sales_Geog'
SHOW GROUPINGID(geog_gidrel)
0.00
SHOW OBJ(PROPERTY '\$GID_DEPTH' 'geog_gidrel')
4
LIMIT geog TO ALL
LIMIT geog TO 'West'
SHOW GROUPINGID(geog_gidrel)
3.00
SHOW OBJ(PROPERTY '\$GID_DEPTH' 'geog_gidrel')
4
```

## GROWRATE

The GROWRATE function calculates the growth rate of a time-series expression, based on the first and last values of the series.

GROWRATE bases its calculation on the values of expression that correspond to the first and last values in the status of time-dimension. The intervening values of expression are ignored. GROWRATE uses the following calculation.

```GROWRATE = ((last/first)**(1/(n-1))-1
```

In the exponent, n is the number of values in the status of the time dimension.

Return Value

DECIMAL

The result returned by GROWRATE is dimensioned by all the dimensions of expression except the dimension specified by time-dimension.

Syntax

GROWRATE(expression [time-dimension])

Arguments

expression

A numeric expression for which you want to calculate the growth rate. The expression must be dimensioned by a time dimension.The following rules apply to the first and last values of expression:

• The first value of expression cannot be zero. (This is to avoid a division by zero in the GROWRATE calculation.)

• The first and last values of expression must both be positive or both negative. (Or the last value of expression can be zero, regardless of whether the first value is positive or negative.)

• Neither the first value nor the last value of expression can be `NA`.

time-dimension

The name of the time dimension by which expression is dimensioned. When the time dimension has a type of DAY, WEEK, MONTH, QUARTER, or YEAR, the time-dimension argument is optional, unless loans has multiple time dimensions.

Examples

Example 6-107 Determining Growth Rate

The following statements limit the dimensions of the `actual` variable and produce a report.

```LIMIT month TO 'Dec95' TO 'Mar96'
LIMIT line TO 'net.income'
REPORT DOWN division ACROSS month: actual
```

These statements produce the following report.

```LINE: NET.INCOME
------------------ACTUAL-------------------
-------------------MONTH-------------------
DIVISION         Dec95      Jan96      Feb96      Mar96
-------------- ---------- ---------- ---------- ----------
Camping          4,378.09  19,915.13  22,510.38  34,731.63
Sporting         6,297.02  13,180.29  17,429.17  18,819.14
Clothing        87,471.74 107,257.85 133,566.01 127,132.55
```

The statement `REPORT W 20 GROWRATE(actual)`produces a report that shows the growth rate of the actual net income in the `demo` workspace between December 1995 and March 1996.

```--GROWRATE(ACTUAL)--
--------LINE--------
DIVISION            NET.INCOME
-------------- --------------------
Camping                        0.99
Sporting                       0.44
Clothing                       0.13
```

## HEXTORAW

The HEXTORAW function converts a character string of hexadecimal digits to a raw value.

"RAW Data Type" and the RAWTOHEX function.

Returns

RAW

Syntax

HEXTORAW(text-exp)

Arguments

text-exp

A text expression containing hexadecimal digits.

## HIERCHECK

The HIERCHECK function checks the hierarchy in the specified relation or all of the relations of the specified aggmap to see if there is any circularity. A hierarchical dimension's parent relation specifies the parent for each of the dimension's values. (Circularity occurs when a dimension value has inadvertently been specified as its own ancestor or descendant in the parent relation. )

You can also specify that HIERCHEK check the hierarchy for other conditions.

HIERSHAPE function

Return Value

BOOLEAN

Syntax

As Command

HIERCHECK parent-relation [STATUS|NOSTATUS|valueset-name] [MULTIPATH] [CONSISTENT]-

[BALANCED levelrelation-name]

or

HIERCHECK aggmap-name [MULTIPATH] [CONSISTENT]levelrelation-name]

Arguments

parent-relation

A text expression indicating the name of the parent relation to be checked.

aggmap-name

A text expression indicating the name of the aggmap. HIERCHECK checks all of the relations in the aggmap.

STATUS

Specifies that HIERCHECK uses the current status of the relation dimension.

valueset

Specifies the values of the relation dimension that HIERCHECK considers in status.

NOSTATUS

Specifies that HIERCHECK uses the default status of the relation dimension.

MULTIPATH

Specifies that HEIRCHECK checks whether there are multiple paths from any child to its parent.

CONSISTENT

Specifies that HIERHECK checks whether the hierarchy is consistent. If the hierarchy is consistent, that means all nodes in the different hierarchies should have the same children.

BALANCED levelrel-name

Using the level relation identified by levelrel-name, specifies that HIERHECK checks to see if all of the following are true:

• All of the elements of a hierarchy which have an NA level are either roots with no leaves or leaves.

• All of the elements of a hierarchy at the same (non NA) level have the same depth from the root (roots) of the hierarchy.

• Elements of a hierarchy for different levels (non NA) have a different depth.

Notes

Why Use HIERCHECK

It is a good strategy to use HIERCHECK at the time you build your hierarchies as a way to verify that they are valid. In other words, do not attempt to roll up a variable's data unless you have verified that its dimensions' hierarchies are structured correctly. For example, the AGGREGATE command uses HIERCHECK to prevent infinite looping once the statement has been executed. Check a parent relation for loops after you set up the levels of a hierarchical dimension, before you load data into any variable that is dimensioned by the hierarchical dimension, or before you use the AGGREGATE command for the first time with a variable. Although it is possible to roll up a variable without first having checked the parent relations of all of its hierarchical dimensions with HIERCHECK, make it a practice to use HIERCHECK first.

Status When Using HEIRCHECK with an Aggmap

When there is any valueset inside a relation in aggmap, HIERCHECK uses this valueset to determine the status of the dimension of the relation. In all other cases, HIERCHECK uses the default status of the relation dimension.

For all dimensions other than relation dimensions, HIERCHECK uses the current status of the dimension.

Error Messages Triggered by HIERCHECK

When you use HIERCHECK, it signals an error when it finds a loop in the parent relation and stops execution (that is, HIERCHECK always stops in the first error message). The error message identifies the dimension values that are involved in the loop, the name of the hierarchy (referred to as the "extra dimension values") in which the loop occurs (when the parent relation has one or more named hierarchies), and the name of the parent relation in which the loop was found. When a parent relation has no loops, no message is displayed. See Example 6-108, "Checking for Loops".

Examples

Example 6-108 Checking for Loops

This example shows how to create a parent relation and check it for loops. You would begin by defining a dimension and adding values to it.

```DEFINE geography DIMENSION ID
MAINTAIN geography ADD 'East' 'Central' 'West'
MAINTAIN geography ADD 'Boston' 'Atlanta' 'Chicago' 'Dallas' 'Denver' 'Seattle'
```

Next, relate the dimension to itself. The following statement defines a parent relation called GEOG.GEOG, which relates the GEOGRAPHY dimension to itself.

```define geog.geog RELATION geography <geography>
```

You would then specify the hierarchy of the dimension values. In this example, there are three levels in the hierarchy: country, regions, and cities. When you specify the hierarchy, you assign parent dimension values (such as `East`) to child dimension values (such as `Boston`) for every level except the highest level. To do this, you store values in the relation. First, group the children with a LIMIT command, then assign a parent to those children.

```LIMIT geography TO 'East' 'Central' 'West'
geog.geog = 'U.S.'
LIMIT geography TO 'Boston' 'Atlanta'
geog.geog = 'East'
LIMIT geography TO 'Chicago' 'Dallas'
geog.geog = 'Central'
LIMIT geography TO 'Denver' 'Seattle'
geog.geog = 'West'
```

Now you can check for loops in the parent relation `geog.geog`, as shown by the following statement.

```HIERCHECK geog.geog
```

In this case, HIERCHECK produces no message output, which means there are no loops in `geog.geog`. It sets HIERCHK.LOOPFND to `NO`, and leaves HIERCHK.LOOPVALS and HIERCHK.XTRADIMS set to `NA`.

Now suppose the following mistake had been made in the storing of values in the relation.

```LIMIT geography TO 'East' 'Central' 'West'
geog.geog = 'East'
```

The preceding statements inadvertently make `East` its own parent, which would cause an aggregation to loop infinitely. When you now check the `geog.geog` relation for loops, the following statement produces the following error message.

```HIERCHECK geog.geog
ERROR: HIERCHECK has detected one or more loops in the hierarchy represented by GEOG.GEOG. The values involved are 'East'.
```

## HIERHEIGHT function

The HIERHEIGHT function returns the value of a node at a specified level for the first value in the current status list of a hierarchical dimension.

To populate a previously-defined relation with the values of a specified hierarchical dimension by level, use the HIERHEIGHT command.

Return Value

The data type returned by HIERHEIGHT is the data type of the dimension value of parentrel

Syntax

HIERHEIGHT(fparentrel [,] level)

Arguments

parentrel

A child-parent self-relation for the hierarchical dimension. See "Parentrel Relation" for more information.

level

An `INTEGER` value that represents a level of the hierarchical dimension. The value `1` (one) represents the lowest-level of the hierarchical dimension.

Notes

Limiting the Hierarchical Dimension

The HIERHEIGHT function always returns a single value of the hierarchical dimension. When you do not limit the hierarchical dimension to a single value before calling the HIERHEIGHT function, the HIERHEIGHT function executes against the first value in the current status list of the dimension. Typically, you either limit the hierarchical dimension to a single value before you call the HIERHEIGHT function or you use the HIERHEIGHT function after a FOR statement to execute the HIERHEIGHT function for each value of the hierarchical dimension.

Examples

Example 6-109 Using HIERHEIGHT as a Simple Command

Assume that your analytic workspace has a hierarchical dimension named `geography` and a relation named `g0.stanparent` that is a self-relation of the `geography` values for the `Standard` hierarchy of `geography`.

```DEFINE g0.newparent RELATION geography <geography>
LD Parent-child when hierarchy of geography is 1
```

Issuing a statement like `REPORT g0.stanparent` displays the values in `g0.stanparent`.

```GEOGRAPHY          G0.STANPARENT
---------------- ----------------
World            NA
Americas         World
...              ...
USA              Americas
Boston           USA
LosAngeles       USA
...              ...
Mexico           Americas
Mexicocity       Mexico
Argentina        Americas
BuenosAires      Argentina
Brazil           Americas
Saopaulo         Brazil
Colombia         Americas
Bogota           Colombia
Australia        World
East.Aust        Australia
Sydney           East.Aust
Budapest         Hungary
Athens           Greece
Vienna           Austria
Melbourne        East.Aust
Central.aust     Australia
...              ...
Perth            West.Aust
Bombay           India
Malaysia         Asia
Europe           World
France           Europe
Caen             France
Paris            France
```

Now you limit geography to the value `Americas` by issuing the following OLAP DML statement.

```LIMIT geography TO 'Americas'
```

When you use the HIERHEIGHT function to find the node for `Americas` for the lowest-level of the hierarchy (level 1) by issuing the following OLAP DML statement.

```REPORT HIERHEIGHT(g0.stanparent 1)
```

The following report is produced.

```HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
NA
```

When you use the HIERHEIGHT function to find the node for `Americas` for the highest-level of the hierarchy (level `4`) by issuing the following OLAP DML statement.

```REPORT HIERHEIGHT(g0.stanparent 4)
```

The following report is produced.

```HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
World
```

When you use the HIERHEIGHT function to find the node for `Americas` for the levels `2` and `3` of the hierarchy by issuing the following OLAP DML statements.

```REPORT HIERHEIGHT(g0.stanparent 2)
REPORT HIERHEIGHT(g0.stanparent 3)
```

The following reports are produced.

```HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
NA

HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
Americas
```

Notice that the output for each level corresponds in between the values that are created for a relation created using HIERHEIGHT command. For example, assume you created a relation named `geog.stanhierrel` for the standard hierarchy for `geography` and limit `geography` to '`Americas`. A report of `geog.stanhierrel` would show the same `geography` values for each level.

```LIMIT geography TO 'AMERICAS'
REPORT DOWN geography geog.stanhierrel

---------------------------GEOG.STANHIERREL--------------------
----------------------------GEOG.LVLDIM------------------------
GEOGRAPHY               1                2                3                4
---------------- ---------------- ---------------- ---------------- ------------
Americas         NA               NA               Americas         World
```

Example 6-110 Using HIERHEIGHT After a FOR Statement

Assume that your analytic workspace has the following program named `findnodes` that finds the nodes of all of the `geography` values in status.

```DEFINE FINDNODES PROGRAM
PROGRAM
VARIABLE level INTEGER
FOR geography
DO
counter = 1
WHILE counter LE statlen(geog.lvldim)
DO
REPORT HIERHEIGHT(g0.stanparent level)
level = level + 1
DOEND
DOEND
END
```

Assume also that you limit `geography` to `Americas` and `Asia` and call the HIERHEIGHT function for each level of the `Standard` hierarchy by issuing the following OLAP statements.

```LIMIT geography TO 'Americas', 'Asia'
CALL findnodes
```

The output of the `findnodes` program for the `geography` values `Americas` and `Asia` is follows. The program first reports on the value of each level for `Americas` is provided. Then it reports on the value of each level for `Asia`.

```HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
NA

HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
NA

HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
Americas

HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
World

HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
NA

HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
NA

HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
Asia

HIERHEIGHT(G0.STANPARENT
COUNTER)
------------------------------
World
```

Notice that the output for each level corresponds in between the values that are created for a relation created using the HIERHEIGHT command

```LIMIT geography TO 'Americas' 'Asia'
REPORT DOWN geography geog.stanhierrel

---------------------------GEOG.STANHIERREL--------------------
----------------------------GEOG.LVLDIM------------------------
GEOGRAPHY               1                2                3                4
---------------- ---------------- ---------------- ---------------- ------------
Americas         NA               NA               Americas         World
Asia             NA               NA               Asia             World
```

## HIERSHAPE

The HIERSHAPE function identifies if a hierarchical dimension has a specified shape.

Return Value

BOOLEAN

Syntax

HIERSHAPE(parent-relation[(qdr)] {LEVEL | RAGGED | SKIPLEVEL | REGULAR} USING levelrel -

[INHIERARCHY inhvalueset] LEVELORDER levelvalueset)

Arguments

parent-relation

A text expression that is the name of the child-parent self-relation for the hierarchical dimension. (See "Parentrel Relation".)

qdr

A text expression that is the name of a QDR that qualifies parent-relation.

LEVEL

This option determines of all of the members are part of the same level as defined by the levelrel.

RAGGED

A hierarchy where leaf-nodes are located at different levels within the hierarchy.

SKIPLEVEL

A hierarchy where one or more leaf nodes link to a higher-level parent above its next most obvious level.

REGULAR

A traditional level-based hierarchy where each child has a parent at the next level up in the hierarchy.

levelrel

A text expression that is the name of the level relation for the hierarchical dimension. (See "Levelrel Relation".)

inhvalueset

A text expression that is the name of the inhier valueset for the hierarchical dimension. (See "Inhier Valueset or Variable".)

levelvalueset

A text expression that is the name of the hierlevels valueset for the hierarchical dimension. (See "Hierlevels Valueset".)

Notes

Star-consistent Hierarchies

A dimension is "star consistent" when the hierarchies it represents can be implemented as a star table (that is, when a single column defines each level so there is no partial membership in a level based on hierarchy). A dimension is "star inconsistent" when it cannot.For example, assume there is a dimension with members A, B, C, D. When some customer have a "mid" level consisting of A and B in hierarchy 1 and C and D in hierarchy 2. This dimension is star inconsistent because in a star table, you would only have a single column to represent the "mid" level and its inclusion means that both hierarchies would contain all the members of that level.

## INFO

The INFO function obtains information that has been produced by a FORECAST, PARSE, or REGRESS statement or that has been produced for a model in your analytic workspace.

Because the syntax of the INFO function is different depending on the type of information being obtained, four separate entries are provided:

### INFO (FORECAST)

The INFO (FORECAST) function obtains information produced by a FORECAST statement and stored internally by Oracle OLAP. Through the use of keywords, INFO lets you extract specific pieces of information about the forecast you have calculated.

Note:

Before using INFO, familiarize yourself with FORECAST.REPORT that is a standard report of its results, which may give you all the information you need. INFO is useful primarily for creating customized reports or for performing further analysis on the results.

When you try to extract information without having calculated a forecast, INFO produces an error. You can use the keyword AVAILABLE to determine whether any results are currently available.

Return Value

The return value depends on the keyword you use, as described in the tables in this entry. INFO returns `NA` when you use an index that is out of range or for any choice that does not apply to the forecasting method last used. For example, when your forecast formula has two coefficients and you request the twelfth one, INFO returns `NA`.

Syntax

INFO(FORECAST choice [index])

Arguments

FORECAST

Indicates that you want to obtain information produced by a FORECAST statement.

choice

The specific information you want. The choices available for FORECAST are listed in Table 6-12, "Choices for All Methods", Table 6-13, "Choices for TREND and EXPONENTIAL Forecasts", and Table 6-14, "Choices for WINTERS Forecasts". Choices marked as indexed require the index argument.

index

An INTEGER expression that specifies which result you want for a choice that can have several different results. For example, a trend equation might have several coefficients. You would use index to specify which coefficient you want information about. When you omit index for a choice that requires it, an error occurs.

Table 6-12 Choices for All Methods

Keyword Type Indexed? Meaning

AVAILABLE

BOOL

No

Is there a computed forecast for which to obtain information?

DEPENDENT

TEXT

No

The variable or expression being forecast.

METHOD

TEXT

No

The forecast method.

MAPE

DEC

No

The mean absolute percent error (a measure of goodness of fit).

LENGTH

INT

No

The number of forecast periods calculated.

TIME

TEXT

No

The dimension along which forecasting is performed.

FCNAME

TEXT

No

The name of the variable that contains the fitted and forecasted values (`NA` when no forecasts were saved).

Table 6-13 Choices for TREND and EXPONENTIAL Forecasts

Keyword Type Indexed? Meaning

FORMULA

TEXT

No

The text of the forecasting equation.

NUMCOEFS

INT

No

The number of coefficients.

COEFFICIENT

DEC

Yes

The specified coefficient in the forecasting equation; index specifies which one you want.

Table 6-14 Choices for WINTERS Forecasts

Keyword Type Indexed? Meaning

PERIODICITY

INT

No

The number of periods in a seasonal cycle.

ALPHA

DEC

No

The smoothing constant for the smoothed data series.

BETA

DEC

No

The smoothing constant for the seasonal index series.

GAMMA

DEC

No

The smoothing constant for the trend series.

STSMOOTHED

DEC

No

The starting value of the smoothed data series.

STSEASONAL

DEC

Yes

The starting values for the seasonal index series; index specifies which one you want.

STTREND

DEC

No

The starting value for the trend series.

FCSMOOTHED

TEXT

No

The variable that holds the smoothed data series.

FCSEASONAL

TEXT

No

The variable that holds the seasonal index series.

FCTREND

TEXT

No

The variable that holds the trend series.

Examples

Example 6-111 Getting Forecast Information

In this example, suppose you forecasted sales.

The following statements limit the dimensions of the `sales` variable, then obtain the formula for your forecast.

```LIMIT product TO 'Sportswear'
LIMIT district TO 'Chicago'
LIMIT month TO 'Jan95' TO 'Dec96'
FORECAST LENGTH 12 METHOD EXPONENTIAL FCNAME fcst time -
month sales
SHOW INFO(FORECAST FORMULA)
```

These statements produce the following output.

```87718.0009541865 * (1.005533834579 ** MONTH)
```

The next statement obtains the mean absolute percent error for your forecast.

```SHOW INFO(FORECAST MAPE)
```

This statement produces the following output.

```.17
```

### INFO (MODEL)

The INFO (MODEL) function obtains information that is produced for the models in your analytic workspace and stored internally by Oracle OLAP. Through the use of keywords, INFO lets you extract specific pieces of information about the structure of a compiled model or the status of a model that you have run in your current session.

Note:

Before using INFO, familiarize yourself with the reports created by MODEL.COMPRPT, MODEL.DEPRT, and MODEL.XEQRPT that might give you all the information you need.

Use INFO with the keyword AVAILABLE to determine whether any model results are currently available. When you try to extract any other information without having considered or defined a model in your current session, INFO produces an error.

Return Value

The return value depends on the keyword you use, as described in the tables in this entry. INFO returns `NA` when you use an index that is out of range or when you request information that is not relevant. For example, if the model contains 5 statements and you request information about statement 6, INFO returns `NA`; or if you specify the DIMENSION REFERENCE choice when the assignment target is actually a variable, INFO returns `NA`.

Syntax

INFO(MODEL choice [index1 [index2 [index3]]])

where index is an argument specifies the result you want for a choice that can have several different results. Depending on the keyword choice, you can supply one or more of the following index arguments:

block-num
dimension-num
element-num
model-num
qualifier-num
source-num
stmnt-num

Arguments

MODEL

Indicates that you want to obtain information about a model in your analytic workspace. INFO returns information about the model that you have most recently defined or considered in the current session (see the DEFINE MODEL and CONSIDER commands).

choice

A keyword that specifies the information you want. The choices available for models are listed in the following tables that represent different informational categories:

Each table consists of four columns that provide the following information: keyword, data type of returned value; index argument associated with the keyword; and meaning.

Table 6-15 INFO (MODEL) Choices to Retrieve General Information About the Model

Keywords Data Type Index Arguments Meaning

AVAILABLE

BOOL

(No arguments)

Is there a model for which information is available?

NAME

TEXT

[MODEL model-num]

Without model-num (or with model-num equal to 0), the name of the current model. With model-num greater than 0, the name of the included model that is the specified model-num within the current model.

COUNT STATEMENTS

INT

(No arguments)

The number of statements in the current model. The count includes comments, equations, and DIMENSION and INCLUDE commands (if any), it but does not include the statements in an included model.

STATEMENT

TEXT

stmnt-num

The text of statement stmnt-num.

SIMULTANEOUS

BOOL

(No arguments)

Does the current model contain a simultaneous block?

Table 6-16 INFO (MODEL) Choices to Retrieve Information about the Structure of the Model

Keyword(s) Data Type Index Argument(s) Meaning

COUNT ELEMENTS

INT

[BLOCK block-num]

Without block-num, the number of blocks in the current model. With block-num, the total number of statements and nested blocks within block block-num in the current model.

When you request further information about a particular element (for example, with the TYPE ELEMENT choice), you always specify the block number to which the element belongs and the number of the element within that block.

TYPE ELEMENT

TEXT

element-num BLOCK block-num

Returns BLOCK or STATEMENT, depending on whether element element-num of block block-num is a nested block or a statement.

NUMBER BLOCK

INT

element-num BLOCK block-num

The block number of the nested block that is element element-num of block block-num.

TYPE BLOCK

TEXT

block-num

Returns SIMPLE, STEP-FORWARD, STEP-BACKWARD, or SIMULTANEOUS, depending on the execution type of block block-num.

COUNT DIMS

INT

[BLOCK block-num]

Without block-num, the number of model dimensions of the current model. With block-num, the number of step-forward, step-backward, or simultaneous dimensions of block block-num within the current model.

DIMENSION

TEXT

dimension-num [BLOCK block-num]

Without block-num, the name of model dimension dimension-num of the current model. With block-num, the name of the specified step-forward, step-backward, or simultaneous dimension of block block-num.

NUMBER STATEMENT

INT

element-num BLOCK block-num

The statement number of the statement that is element element-num of block block-num.

The statement number refers to the position of the statement within its own model. To request further information about the statement (for example, with the HIDDEN choice), its model must be the model that you are currently considering.

HIDDEN

BOOL

stmnt-num

Has statement stmnt-num been masked by a subsequent statement?

NUMBER MODEL

INT

element-num BLOCK block-num

The number of the included model from which the statement that is element element-num of block block-num is taken.

Table 6-17 INFO (MODEL) Choices to Retrieve Information about Target, Sources, and Dependencies

Keyword(s) Data Type Index Argument Meaning

COUNT SOURCES

INT

STATEMENT stmnt-num

The number of data sources in statement stmnt-num within the current model.

TYPE REFERENCE

TEXT

STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the object type of the assignment target of statement stmnt-num. With source-num, the object type of data source source-num in statement stmnt-num. The object type is VARIABLE when the reference is to a variable. The type is DIMENSION when the reference is to the value of a dimension.

VARIABLE REFERENCE

TEXT

STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the name of the variable that is the assignment target of statement stmnt-num. With source-num, the name of the variable that is data source source-num in statement stmnt-num.

VALUE REFERENCE

TEXT

STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the dimension value that is the assignment target of statement stmnt-num. With source-num, the dimension value that is data source source-num in statement stmnt-num.

DIMENSION REFERENCE

TEXT

STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the model dimension of the target dimension value in statement stmnt-num. With source-num, the model dimension of source dimension value source-num in statement stmnt-num.

COUNT QUALIFIERS

INT

STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the number of qualifiers of the assignment target in statement stmnt-num. With source-num, the number of qualifiers of data source source-num in statement stmnt-num.

TYPE QUALIFIER

TEXT

qualifier-num STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the qualifier type of qualifier qualifier-num of the target of statement stmnt-num. With source-num, the qualifier type of qualifier qualifier-num of data source source-num in statement stmnt-num. The qualifier type can indicate dimensional dependence: LAG (previous dimension values only), LEAD (later values only), BOTH (both previous and later values), and VARIABLE (either previous or later values, depending on the value of a variable when the model is run). The qualifier type can also be QDR (qualified data reference).

DIMENSION QUALIFIER

TEXT

qualifier-num STATEMENT stmnt-num [SOURCE source-num]

qualifier-num STATEMENT stmnt-num [SOURCE source-num]

Without source-num, the dimension of qualifier qualifier-num of the assignment target in statement stmnt-num. With source-num, the dimension of qualifier qualifier-num of data source source-num in statement stmnt-num.

Table 6-18 INFO (MODEL) Choices to Retrieve Information About Execution Status

Keyword(s) Data Type Index Argument Meaning

XEQSTATUS

TEXT

[BLOCK block-num]

Without block-num, the execution status of the model as a whole; when the model has not been run, the status is NOT EXECUTED. With block-num, the execution status of block block-num; when the model has not been run, an error is returned. When the model has been run, the status for the model as a whole or for a block can be SOLVED, DIVERGED, or FAILED TO CONVERGE. The status of an outer-level block can be EXECUTION INCOMPLETE when a nested block within it diverged or failed to converge.

COUNT ITERATIONS

INT

BLOCK block-num

The number of iterations that were performed for block block-num before it was solved or it diverged or failed to converge.

DAMP

DEC

(No arguments)

The value of the MODDAMP option when the model was run. (Relevant only when the solution method is GAUSS.)

DIVERGSTMT

INT

BLOCK block-num

The element number of the statement that diverged during the calculations for block block-num.

GAMMA

INT

(No arguments)

The value of the MODGAMMA option when the model was run.

MAXITERS

INT

(No arguments)

The value of the MODMAXITERS option when the model was run.

OVERFLOW

INT

(No arguments)

The value of the MODOVERFLOW option when the model was run.

SIMULTYPE

TEXT

(No arguments)

The value of the MODSIMULTYPE option when the model was run: AITKENS or GAUSS.

TOLERANCE

INT

(No arguments)

The value of the MODTOLERANCE option when the model was run.

block-num

An INTEGER expression that specifies the block for which you want information. Block-num corresponds to the block numbers that are identified in the report produced by the MODEL.COMPRPT program.

dimension-num

An INTEGER expression that specifies the model dimension or block dimension for which you want information. For the model as a whole, the first dimension listed for the model is dimension-num 1, and so on. For example, assume that the MODEL.COMPRPT specifies the model dimensions as `<line month>`. In this case, `line` is dimension-num `1` and `month` is dimension-num `2`. For a simultaneous block in the current model, the first dimension of the block is dimension-num `1`, and so on. A step-forward or step-backward block has a single dimension, so the dimension of the block is always dimension-num `1`. To see a list of the dimensions for the model as a whole and for each block of the model, you can run the MODEL.COMPRPT program.

element-num

An INTEGER expression that specifies the element for which you want information. When you request information about an element, you always specify the block number to which the element belongs. An element is either a statement in the specified block, or it is a nested block within the specified block. The element numbers correspond to the order of the statements and blocks in the compiled model. You can run the MODEL.COMPRPT program to see the list of elements in the compiled model.

For example, suppose the current model has the following compiled structure.

```block 1
statement a
block 2
statement b
statement c
END block 2
statement d
END block 1
```

When you request information about `block` `1` in the preceding model, `statement` `a` is element-num `1`; `block` `2` is element-num `2`; and `statement` `d` is element-num `3`. When you request information about `block` `2`, `statement` `b` is element-num `1` and `statement` `c` is element-num `2`.

model-num

For a hierarchy of included models, an INTEGER expression that specifies the model for which you want information. The model you are currently considering is model-num `0` (zero), the model it includes is model-num `1`, and so on. The root model has the highest model number in the hierarchy.

qualifier-num

An INTEGER expression that specifies the qualifier for which you want information. Qualifiers change the dimensionality of a variable or dimension value reference. The reference can be qualified by a function, such as LAG, LEAD, or TOTAL or by a qualified data reference (QDR). To see the qualifiers for a statement, you can run the MODEL.DEPRT program for the model that contains the statement.

For each equation in the model, the MODEL.DEPRT report lists the assignment target and its qualifiers on one line, followed by the data sources. Each data source is listed on a separate line, together with its qualifiers. The MODEL.DEPRTreport also specifies the type of each qualifier: LAG, LEAD, BOTH, VARIABLE, or QDR (see the TYPE QUALIFIER choice in the third group of INFO keyword choices).

For the target and each source, qualifier-num corresponds to the order in which the qualifiers are listed in the MODEL.DEPRT report.

source-num

An INTEGER expression that specifies the data source for which you want information. In a calculation, each reference to a variable or a dimension value is counted as a source of data for the assignment target. A constant value is not counted as a source.

To see the data sources in a statement, you can run the MODEL.DEPRT program for the model that contains the statement. For each equation in the model, the MODEL.DEPRT report lists the assignment target on one line, followed by its data sources. Each data source is listed on a separate line.

stmnt-num

An INTEGER expression that specifies the statement for which you want information. Stmnt-num always refers to a statement from the model you are currently considering. It does not refer to a statement taken from an included model.

To see the statement numbers in the current model, you can run the MODEL.COMPRPT program. To the left of each statement, the report lists the model from which the statement is taken and the statement number within that model.

Examples

Example 6-112 Getting Qualifier Information

Assume that the following statement is statement 3 of a model called `income.plan`.

```budget(line revenue) = LAG(actual(line revenue), 1, month) -
+ plan.factor
```

You can run the `MODEL.DEPRPT` program to see the qualifiers of the target and sources in this statement.

```MODEL.DEPRPT income.plan
```

This statement produces the following output.

```MODEL INCOME.PLAN
...
3    BUDGET(QDR <LINE>):
ACTUAL(LAG <MONTH>)(QDR <LINE>)
PLAN.FACTOR
...
```

This report shows that the assignment target, `budget`, has two data sources, `actual` and `plan.factor`.

Example 6-113 Checking Qualifier Information

The following statements make INCOME.PLAN the current model and check the number and type of the qualifiers of the assignment target of statement 3.

```CONSIDER income.plan
SHOW INFO(MODEL COUNT QUALIFIERS STATEMENT 3)
```

These statements produce the following output.

```1
```

The OLAP DML statement

```SHOW INFO(MODEL TYPE QUALIFIER 1 STATEMENT 3)
```

produces the following output.

```QDR
```

Example 6-114 Checking Different Data Sources

The following statements check the number and type of the qualifiers of the two data sources in statement 3.

The OLAP DML statement

```SHOW INFO(MODEL COUNT QUALIFIERS STATEMENT 3 SOURCE 1)
```

produces the following output.

```2
```

The OLAP DML statement

```SHOW INFO(MODEL TYPE QUALIFIER 1 STATEMENT 3 SOURCE 1)
```

produces the following output.

```LAG
```

The OLAP DML statement

```SHOW INFO(MODEL TYPE QUALIFIER 2 STATEMENT 3 SOURCE 1)
```

produces the following output.

```QDR
```

The OLAP DML statement

```SHOW INFO(MODEL COUNT QUALIFIERS STATEMENT 3 SOURCE 2)
```

produces the following output.

```0
```

### INFO (PARSE)

The INFO (PARSE) function obtains information produced by a PARSE statement and stored internally by Oracle OLAP. Through the use of keywords, INFO lets you extract specific pieces of information about the expression that you have parsed.

Return Value

The return value depends on the keyword you use, as described in Table 6-19. When you try to extract unavailable information or use an index that is out of range, INFO returns `NA`. For example, if you parse a phrase that contains four expressions and then ask for the twelfth FORMULA, INFO returns `NA`.

Syntax

INFO(PARSE choice [index])

Arguments

PARSE

Indicates that you want to obtain information produced by a PARSE statement.

choice

The specific information you want. The choices available for PARSE are listed in Table 6-19, "INFO PARSE Keywords". Choices marked as indexed can take the optional index argument.

index

An INTEGER expression that specifies which result you want for a choice that can have several different results. For example, when you parse text that contains three expressions, each expression has its own formula and data type. You would use index to specify which expression you are interested in.

When you omit index, INFO returns all the information as a multiline value.

Table 6-19 INFO PARSE Keywords

Keyword Type Indexed? Meaning

PARSEABLE

BOOL

No

Was Oracle OLAP able to parse the text?

ERRORTEXT

TEXT

No

The text of an error message when the expressions were not parsed.

NUMFORMULAS

INT

No

The number of expressions (formulas) that were parsed.

NUMDIMS

INT

No

The number of dimensions in the union of all the expressions that were parsed.

FORMULA

TEXT

Yes

The text (formula) of the specified expression; index specifies which one you want.

DATA

TEXT

Yes

The data type of the specified expression.

TYPE

TEXT

Yes

The type of object of the specified expression; when the expression is the name of an object, it returns the type; when the expression is a qualified data reference, it returns QDR; when the expression is anything else, it returns EXP.

DIMENSION

TEXT

Yes

The name of the specified dimension in the union of all dimensions of the expressions.

Examples

Example 6-115 Getting Parsed Information

In a simple report program, you want to allow the user to specify the data to be reported as an argument to the program. You want to allow the user to specify an expression and the name of a data variable. You cannot process expression arguments with an ARGS statement, so you use PARSE and INFO to parse the program arguments and produce the report.

The following statements create a simple report program.

```DEFINE report1 PROGRAM
PROGRAM
PUSH month product district DECIMALS
DECIMALS = 0
LIMIT month TO FIRST 2
LIMIT product TO ALL
LIMIT district TO 'Chicago'
PARSE ARGS
REPORT ACROSS month: WIDTH 8 <&INFO(PARSE FORMULA 1) -
WIDTH 13 &INFO(PARSE FORMULA 2)>
POP month product district DECIMALS
END
```

When users run the program, they can supply either the name of a variable (`sales`) or an expression (`sales-expense`) or both as arguments.

The following statement

```REPORT1 sales sales-expense
```

produces the following output.

```DISTRICT: CHICAGO
--------------------MONTH--------------------
--------Jan95--------- --------Feb95---------
PRODUCT       SALES   SALES-EXPENSE  SALES   SALES-EXPENSE
------------ -------- ------------- -------- -------------
Tents          29,099         1,595   29,010         1,505
Canoes         45,278           292   50,596           477
Racquets       54,270         1,400   58,158         1,863
Sportswear     72,123         7,719   80,072         9,333
Footwear       90,288         8,117   96,539        13,847
```

### INFO (REGRESS)

The INFO (REGRESS) function obtains information produced by an REGRESS statement and stored internally by Oracle OLAP. Through the use of keywords, INFO lets you extract specific pieces of information about the regression you have calculated.

Note:

Before using INFO, familiarize yourself with REGRESS.REPORT that produces a standard report of its results, which might give you all the information you need. INFO is useful primarily for creating customized reports or for performing further analysis on the results

Return Value

The return value depends on the keyword you use, as described in Table 6-20, "INFO REGRESS Keywords".

Syntax

INFO(REGRESS choice [index])

Arguments

REGRESS

Indicates that you want to obtain information produced by an REGRESS statement.

choice

The specific information you want. The choices available for REGRESS are listed in Table 6-20, "INFO REGRESS Keywords". Choices marked as indexed require the index argument.

index

An INTEGER expression that specifies which result you want for a choice that can have several different results. For example, in a regression there may be multiple independent variables. You would use index to specify which independent variable you want information about. When you omit index for a choice that requires it, an error occurs.

Table 6-20 INFO REGRESS Keywords

Keyword Type Indexed? Meaning

AVAILABLE

BOOL

No

Is there a computed regression from which to extract information?

DEPENDENT

TEXT

No

The name of the dependent variable in the regression.

NOINTERCEPT

BOOL

No

Was the regression calculated with the intercept suppressed?

WEIGHTED

BOOL

No

Was the last regression weighted?

WEIGHT

TEXT

No

The expression used to weight the last regression.

NUMCOEFS

INT

No

The number of coefficients.

INDEPENDENT

TEXT

Yes

An independent variable; index specifies which one you want (Intercept to be first unless it was suppressed).

COEFFICIENT

DEC

Yes

An estimated coefficient; index specifies which one you want.

STDERROR

DEC

Yes

The standard error of an estimated coefficient; index specifies which one you want.

TRATIO

DEC

Yes

The t-ratio for an estimated coefficient; index specifies which one you want.

NUMOBS

INT

No

The number of observations that were used.

FRATIO

DEC

No

The F-ratio for the regression.

RBSQ

DEC

No

The corrected R-squared for the regression.

FORMULA

TEXT

No

The regression formula.

STDERROREST

DEC

No

The standard error of estimate for the regression

RESET

BOOL

Use when you want to reset the original state of AVAILABLE back to `NO`

Notes

Determining Regression Results Availability

When you try to extract information without having performed a regression, INFO produces an error. You can use the keyword AVAILABLE to determine whether any results are currently available. Once a successful regression has run, AVAILABLE remains true even when one or more unsuccessful regressions follow, because the results of the previous successful regression are still available. AVAILABLE remains true until you use RESET to change the AVAILABLE state back to its original value of `NO`.

NA Results Due to Index

INFO returns `NA` when you use an index that is out of range. For example, when your regression has five independent variables and you request the coefficient of the twelfth one, INFO returns `NA`.

Examples

Example 6-116 Getting Regression Information

The following statement sends the third coefficient from your most recently calculated regression to the current outfile.

```SHOW INFO(REGRESS COEFFICIENT 3)
```

This statement produces the following result.

```7.55
```

## INITCAP

The INITCAP function returns a specified text expression, with the first letter of each word in uppercase and all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.

Return Value

The same data type as the expression.

Syntax

INITCAP (text-exp)

Arguments

text-exp

A text expression.

Examples

Example 6-117 Capitalizing the First Character in Each World

The following example capitalizes each word in the string.

```SHOW INITCAP('the soap')
The Soap
```

## INLIST

The INLIST function determines whether every line of a text value is a line in a second text value. Normally, INLIST is used to determine whether all the lines of a list (in the form of a multiline text value) can be found in a master list (in the form of a second multiline text value).

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

Return Value

BOOLEAN

Syntax

INLIST(masterlist list)

Arguments

masterlist

A multiline text expression to which the lines of list are compared.

list

A multiline text expression whose lines are compared with the lines of masterlist. When every line of list can be found as a line of masterlist, INLIST returns the value `YES`. When one or more lines of list are not found in masterlist, INLIST returns the value `NO`.

Examples

Example 6-118 Comparing a List to a Master List

This example shows how to use INLIST to determine whether the lines of one list can be found in a master list. The master list in this case is a multiline text value in a variable called `depts`. The `depts` variable has the following values.

```Marketing
Accounting
Engineering
Personnel
```

The first function call compares a list, which is specified as a text literal, with the master list. The return value is `YES`.

```INLIST(depts, 'Accounting\nPersonnel')
```

The second function call compares a variable `newlist` that has the following values,

```Development
Accounting
```

with the master list in `depts`. The return value is `NO`.

```INLIST(depts, newlist)
```

## INSBYTES

The INSBYTES function inserts one or more bytes into a text expression.

When you are using a single-byte character set, you can use INSCHARS.

Return Value

TEXT

Syntax

INSBYTES(text-expression bytes [after])

Arguments

text-expression

A `TEXT` expression into which the bytes are to be inserted. When text-expression is a multiline TEXT value, INSBYTES preserves the line breaks in the returned value.

bytes

One or more bytes that you insert into text-expression.

after

An `INTEGER` that represents the byte position after which the specified bytes are to be inserted. The position of the first byte in text-expression is 1. To insert bytes at the beginning of the text, specify 0 for after. When you omit this argument, INSBYTES inserts the bytes after the last byte in text-expression.

When you specify a value for after that is greater than the length of text-expression, INSBYTES adds blanks to the last line of text-expression. The number of inserted blanks is the difference between the value of after and the length of text-expression. For example, `insbytes('abc' 'def' 4)` inserts one blank space before adding `def` to `abc`, resulting in.

```abc def
```

Examples

Example 6-119 Inserting Bytes in Text

This example shows how to insert the bytes `there` in the TEXT value `hellojoe`.

The function

```INSBYTES('hellojoe', 'there', 5)
```

returns the following value.

```hellotherejoe
```

## INSCHARS

The INSCHARS function inserts one or more characters into a text expression.

When you are using a multibyte character set, you can use the INSBYTES function instead of the INSCHARS function.

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

INSCHARS(text-expression characters [after])

Arguments

text-expression

The expression into which the characters are to be inserted. When text-expression is a multiline TEXT value, INSCHARS preserves the line breaks in the returned value.

characters

One or more characters that you insert into text-expression.

after

An `INTEGER` that represents the character position after which the specified characters are to be inserted. The position of the first character in text-expression is 1. To insert characters at the beginning of the text, specify 0 for after. When you omit this argument, INSCHARS inserts the characters after the last character in text-expression.

When you specify a value for after that is greater than the length of text-expression, INSCHARS adds blanks to the last line of text-expression. The number of inserted blanks is the difference between the value of after and the length of text-expression. For example, `INSCHARS('abc' 'def' 4)` inserts one blank before adding `'def'` to 'abc', resulting in.

```abc def
```

Examples

Example 6-120 Inserting Characters in Text

This example shows how to insert the characters `there` in the TEXT value `hellojoe`.

```INSCHARS('hellojoe', 'there', 5)

hellotherejoe
```

## INSCOLS

The INSCOLS function inserts into the columns of a multiline TEXT value all the columns of another TEXT value. The inserted columns are placed after the column position you specify, and the original columns in each line are moved to the right. The function returns a multiline TEXT value composed of the resulting columns.

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, INSCOLS repeats its last line in each subsequent line of the return value.

Syntax

INSCOLS(text-expression columns [after])

Arguments

text-expression

The expression into which you want to insert columns.

columns

The expression containing one or more columns in each line. All the columns of this expression is inserted into the corresponding lines of text-expression.

after

An `INTEGER` between 0 and 4,000 representing the column position after which columns should be inserted. The column position of the first character in each line is 1. When you do not specify after, insertion begins at the end of each line. The total length of a line cannot exceed 4,000 columns of single-byte characters or 2,000 columns of double-byte characters.

When you specify an after 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.

Examples

Example 6-121 Inserting Text Columns

In the following example, a color code (stored in the multiline TEXT value `itemcolor`) is inserted into item identifiers that are stored in the `itemid` text value. The columns are inserted after Column 3.

`itemcolor` has the following value.

```Blu
Red
Gre
Ora
```

`itemid` has the following value.

```542-Fra
379-Eng
968-USA
369-Can
```

The INSCOLS function call

```INSCOLS(itemid itemcolor 3)
```

returns the following.

```542Blu-Fra
379Red-Eng
968Gre-USA
369Ora-Can
```

## INSLINES

The INSLINES function inserts one or more lines into a multiline text expression.

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

INSLINES(text-expression lines [after])

Arguments

text-expression

A multiline expression into whose values one or more lines are to be inserted.

lines

An expression that represents one or more lines of text that you insert into text-expression.

after

An `INTEGER` that represents the line number after which the specified lines are to be inserted. The position of the first line in text-expression is `1` (one). To insert lines at the very beginning, specify `0` (zero) for after. When you omit this argument, INSLINES inserts the new lines after the last line of text-expression.

Examples

Example 6-122 Inserting Text Lines

This example shows how to insert a new line into a multiline text value in a variable called `mktglist` with the following value.

```Salespeople
Products
Services
```

The INSLINES function

```INSLINES(mktglist, 'Advertising', 2)
```

returns the following.

```Salespeople
Products
Services
```

## INSTAT

The INSTAT function checks whether a dimension or dimension surrogate value is in the current status list or whether a dimension value is in a valueset.

Return Value

BOOLEAN

YES if the value is in the current status list or in a valueset and NO if it is not.

Syntax

INSTAT(dimensionvalue)

Arguments

dimension

The name of the dimension, dimension surrogate, or valueset.

value

The dimension or dimension surrogate value you want to test, either a text literal (enclosed in single quotes) or an expression that specifies the value. To specify the value of a conjoint dimension or a concat dimension, enclose the value in angle brackets. For a conjoint dimension, separate the base dimension values with a comma and space. For a concat dimension, separate the base dimension and its value with a colon and a space.

Notes

Checking an Invalid Value

When you specify a dimension name and value in an INSTAT statement, Oracle OLAP tells you whether that value is in the current status list for that dimension. Conversely, the ISVALUE function tells you whether an item is a value of a dimension, regardless of whether it is in the status. INSTAT produces an error when value is not a dimension value, but ISVALUE simply returns a value of `FALSE`.

Examples

Example 6-123 Using INSTAT With a Valueset

Assume that within your analytic workspace you have a `geog` dimension with the following definition and values.

```DEFINE geog TEXT DIMENSION
GEOG
--------------
Austria
Belgium
```

Assume, also, that you define a `mygeogs` valueset and identify a value for that valueset using the following statements.

```DEFINE mygeogs VALUESET geog
LIMIT mygeogs TO 'Belgium'

```

You cannot issue a REPORT on a valueset. If you attempt to, Oracle OLAP issues an error message.

```REPORT mygeogs
ORA-34104: INSTATTEST!MYGEOGS is not a type of object that contains data values.

```

However, you can use the INSTAT function to display which values of a dimension are in a valueset.

```REPORT INSTAT (mygeogs, geog)

GEOG            INSTAT (MYGEOGS, GEOG)
-------------- ------------------------
Austria                              no
Belgium                             yes
```

Example 6-124 Checking Current Status

In the following example, a program accepts a value of the `month` dimension as an argument. The first lines of the program use INSTAT to check whether the dimension value that was passed as an argument is in the current status for `month`. When it is, the program calls a report program. When it is not, the program branches to its error-handling section.

```ARGUMENT onemonth month

IF INSTAT(month onemonth)
THEN sales_report
ELSE GOTO error
...
```

Example 6-125 Using INSTAT When the Dimension is a Conjoint Dimension

When the dimension that you specify is a conjoint dimension, then the entire value must be enclosed in single quotes. For example, suppose the analytic workspace has a `region` dimension and a `product` dimension. The `region` dimension values include `East`, `Central`, and `West`. The `product` dimension values include `Tents`, `Canoes`, and `Racquets`.

The following statements define a conjoint dimension, and add values to it.

```DEFINE reg.prod DIMENSION <geography product>
MAINTAIN reg.prod ADD <'East', 'Tents'> <'West', 'Canoes'>
```

To specify base positions, use a statement such as the following.

```SHOW INSTAT(reg.prod '<1, 1>')
YES
```

To specify base text values, use a statement such as the following.

```SHOW INSTAT(reg.prod '<\'East\', \'Tents\'>')
YES
```

Example 6-126 Using INSTAT When the Dimension is a Concat Dimension

When the dimension that you specify is a concat dimension, then you must enclose the entire `<component dimension: dimension value>` pair in single quotes. The following statement defines a concat dimension that has as its base dimensions `region` and `product`.

```DEFINE reg.prod.ccdim DIMENSION CONCAT(region product)
```

A report of `reg.prod.ccdim` returns the following.

```REG.PROD.CCDIM
----------------------
<region: East>
<region: Central>
<region: West>
<product: Tents>
<product: Canoes>
<product: Racquets>
```

To specify a base dimension position, use a statement such as the following.

```SHOW INSTAT(reg.prod.ccdim '<product: 3>')
yes
```

To specify base dimension text values, use a statement such as the following.

```SHOW INSTAT(reg.prod.ccdim '<product: Tents>')
YES
```

## INSTR functions

The INSTR functions (INSTR, INSTRB, INSTRC, INSTR2, and INSTR4) searches a string for a substring using characters and returns the position in the string that is the first character of a specified occurrence of the substring. The functions vary in how they determine the position of the substring to return.

• INSTR calculates lengths using characters as defined by the input character set.

• INSTRB calculates lengths using bytes.

• INSTRC calculates lengths using Unicode complete characters.

• INSTR2 calculates lengths using UCS2 code points.

• INSTR4 calculates lengths using UCS4 code points.

Return Value

A nonzero INTEGER when the search is successful or `0` (zero) when it is not.

Syntax

{INSTR | INSTRB | INSTRC | INSTR2 | INSTR4} (string , substring [, position [, occurrence]])

Arguments

string

The text expression to search.

substring

The string to search for.

position

A nonzero INTEGER indicating where in string the function begins the search. INSTR calculates position using characters as defined by the input character set. INSTRB calculates position using bytes. INSTRC calculates position using Unicode complete characters. INSTR2 calculates position using UCS2 code points. INSTR4 calculates position using UCS4 code points.

When position is negative, then INSTR counts and searches backward from the end of string. The default value of position is `1`, which means that the function begins searching at the beginning of string.

occurrence

An INTEGER indicating which occurrence of string the function should search for. The value of occurrence must be positive. The default values of occurrence is `1`, meaning the function searches for the first occurrence of substring.

Examples

Example 6-127 Using Character Position to Search Forward to Find the Position of a Substring

The following example searches the string "Corporate Floor", beginning with the third character, for the string "or". It returns the position in "Corporate Floor" at which the second occurrence of "or" begins.

```SHOW INSTR('Corporate Floor','or', 3, 2)
14
```

Example 6-128 Using Character Position to Search Backward to Find the Position of a Substring

In this next example, the function counts backward from the last character to the third character from the end, which is the first "o" in "Floor". The function then searches backward for the second occurrence of "or", and finds that this second occurrence begins with the second character in the search string.

```SHOW INSTR('Corporate Floor','or', -3, 2)
2
```

Example 6-129 Using a Double-Byte Character Set to Find the Position of a Substring

This example assumes a double-byte database character set.

```SHOW INSTRB('Corporate Floor','or',5,2)
27
```

## INTPART

The INTPART function calculates the integer part of a decimal number by truncating its decimal fraction.

Return Value

INTEGER

Syntax

INTPART(expression)

Arguments

expression

The decimal expression whose integer part is to be returned.

Notes

Large Values

When expression has a value larger than is allowed for an `INTEGER` (a value between `-2,147,483,647` and `2,147,483,647`), INTPART returns an `NA` value.

Examples

Example 6-130 Calculating the Integer Part of a Decimal Number

The following example shows the integer part of the number `3.14`. The statement

```show intpart(3.14)
```

produces the following result.

```3
```

## IRR

The IRR function computes the internal rate of return associated with a series of cash flow values. Each value of the result is calculated to be the discount rate for each period that makes the net present value of the corresponding cash flows equal to zero.

Return Value

DECIMAL (For example, n 8.25 percent internal rate of return produces a result value of `.0825`.)

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

Syntax

IRR(cashflows, [time-dimension])

Arguments

cashflows

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

Note:

All the cash flows used to compute a result value are assumed to occur at the same relative point within the period with which they are associated. Cash flows that corresponds to out-of-status dimension positions are ignored
time-dimension

A name that specifies the time dimension. When cashflows has a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, the time-dimension argument is optional since IRR automatically uses the DAY, WEEK, MONTH, QUARTER, or YEAR dimension of cashflows when you do not specify a value for time-dimension.

Notes

Multiple Discount Rates

Some series of cash flows have multiple discount rates, which make the net present value equal to zero. In such cases, IRR finds and returns only one of these discount rates as the internal rate of return. When there is only a single solution and it is between -99.9 percent and 10,000 percent, the IRR function finds it. When IRR cannot calculate an internal rate of return, the corresponding value in the result is `NA`.

Examples

Example 6-131 Calculating the Internal Rate of Return

The following statements create a dimension called `project`, add values to it, and create a variable called `cflow`, which is dimensioned by `year` and `project`.

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

Once you have assigned the following values to CFLOW,

```------------------------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
```

then the following statement

```REPORT IRR(cflow, year)
```

produces the following report of the internal rate of return.

```IRR(CFLOW,
PROJECT          YEAR)
-------------- ----------
a                    0.00
b                    0.84
c                    0.22
d                    0.13
E                    0.06
```

## ISDATE

The ISDATE program determines whether a text expression represents a valid date. ISDATE only tests a text expression to see if it can be converted to a DATE-only value; it does not actually make the conversion. You must use CONVERT to make the conversion.

Return Value

BOOLEAN

`YES` when the text expression represents a valid date; `NO` when it does not.

Syntax

ISDATE(test-date)

Arguments

test-date

A single-line ID or TEXT expression to be examined to see if it represents a valid date, as defined by the DATE-only data type. For a description of the valid styles for entering dates, see "Date-only Input Values".

Examples

Example 6-132 Testing a Text Expression

In the following statement, the ISDATE program tests a literal text expression to see if it is a valid date, and the output is sent to the current outfile.

```SHOW ISDATE('3 5 1995')
```

This statement produces the following output.

```YES
```

## ISEMPTY

The ISEMPTY function identifies if a variable or one or more of its partitions has values.

Return Value

BOOLEAN

FALSE when the specified variable or partitions have values; TRUE when they are empty.

Syntax

ISEMPTY( variable [(partition ...)])

Arguments

variable

The name of the variable to check for values.

partition

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

## ISINFINITE

The ISINFINITE function returns a value that indicates if a the value of a numeric expression is infinity.

This function provides information similar provided by the SQL IS [NOT] INFINITE floating point condition as described in Oracle Database SQL Language Reference.

Return Value

BOOLEAN

Returns `TRUE` when the expression is either +INF (or -INF when `NOT` is not specified); otherwise it returns `FALSE`.

Note:

The OLAP DML converts `+INF` or `-INF` values to `NA` when it performs calculation. Consequently, this function can only possibly return `TRUE` when executed against data that has been imported into an analytic workspace from a SQL-populated database but not yet used in an OLAP DML calculation.

Syntax

ISINFINITE(expression)

Arguments

expression

A decimal expression.

## ISNAN

The ISNAN function returns a value that indicates if a the value of a numeric expression is the special `NaN` value.

This function provides information similar provided by the SQL IS [NOT] NAN floating point condition as described in Oracle Database SQL Language Reference.

Return Value

BOOLEAN

Returns `TRUE` when the expression is either +NaN (or -NaN when `NOT` is not specified); otherwise it returns `FALSE`.

Note:

The OLAP DML converts `+NaN` or `-NaN` values to `NA` when it performs calculation. Consequently, this function can only possibly return `TRUE` when executed against data that has been imported into an analytic workspace from a SQL-populated database but not yet used in an OLAP DML calculation.

Syntax

ISNAN(expression)

Arguments

expression

A decimal expression.

## ISSESSION

The ISSESSION function determines whether the current member of a specified dimension is a temporary member (that is, a member added when a MAINTAIN ADD SESSION statement executes).

Return value

BOOLEAN

`YES` when the member is a temporary member; `NO` when it is not.

Syntax

ISSESSION( [RECURSIVE]dimension)

Arguments

RECURSIVE

Specifies that for a dimension with base dimensions, that Oracle OLAP tests the values of the base dimensions when making its determination.

dimension

The name of the dimension whose current member value is to be tested by Oracle OLAP.

## ISVALUE

The ISVALUE function tests whether a dimension or a composite has a specified value.

Tip:

Use INSTAT to determine whether a value of a dimension is in the current status of the dimension.

Return Value

BOOLEAN

Syntax

ISVALUE(namevalue)

Arguments

name

The name of the dimension or the composite to be checked.

When the composite is unnamed, use the SPARSE keyword to refer to the composite (for example, `SPARSE <market product>`).

value

The value you want to test, either a text literal or text expression for an ID or TEXT dimension, an `INTEGER` for an INTEGER dimension, or a combination of values enclosed by angle brackets for composites and conjoint dimensions.

Examples

Example 6-133 Testing Valid Values

Suppose you want to find out if `Packs` is a value of the `product` dimension. The following statement produces the answer `YES` or `NO`.

```SHOW ISVALUE(product, 'Packs')
```

Example 6-134 Testing Logical Position Numbers

You can test for the logical position numbers of base dimension values in a conjoint dimension. For example, suppose `market` and `product` are the base dimensions of the conjoint dimension `markprod`. The following statement tests whether or not there is a value assigned to the combination of the fourth `market` dimension value and the third `product` dimension value.

```SHOW ISVALUE(markprod, '<4 3>')
```

## JOINBYTES

The JOINBYTES function joins two or more text values as a single line.

JOINBYTES ignores any arguments that have a value of `NA` and removes line breaks from the text it joins. (To preserve the breaks in a multiline text expression, use the INSCHARS.) Also, when the length of the joined line exceeds 4,000 (The maximum length of a joined line), JOINBYTES automatically breaks the line and puts the remaining bytes on the next line. The line break could occur between the two bytes of a double-byte character. JOINBYTES would then end one line with the first byte of the double-byte character and start the next line with the second byte of the character.

Return Value

TEXT

Syntax

JOINBYTES(first-expressionnext-expression...)

Arguments

first-expression

An expression to which JOINBYTES joins next-expression. When the first-expression has a data type other than TEXT or NTEXT, JOINBYTES converts it to TEXT. Use the CONVERT or TO_CHAR function to convert a NTEXT expression to TEXT.

next-expression

One or more expressions to join with first-expression. When an expression you want to concatenate has a data type other than TEXT or NTEXT, JOINBYTES converts it to TEXT. Use the CONVERT or TO_CHAR function to convert a NTEXT expression to TEXT.

Examples

Example 6-135 Using JOINBYTES to Concatenate Values

This example shows how you can use JOINBYTES to combine text with the current values of the two variables `name.product` and `price`. The variable `price` has a data type of DECIMAL; however, JOINBYTES automatically converts its value to TEXT to join it with the other text values.

```LIMIT product TO 'Canoes'
LIMIT month TO 'Dec96'
```

The JOINBYTES function

```JOINBYTES('Current Price for ' name.product ' is:  \$' price)
```

returns the following value.

```Current Price for Aluminum Canoes is:  \$200.03
```

## JOINCHARS

The JOINCHARS function joins two or more non-NA expressions as a single line text. JOINCHARS removes line breaks from the text it joins. (Use INSCHARS to preserve line breaks.)

When the length of the joined line exceeds 4,000 bytes, JOINCHARS automatically breaks the line and puts the remaining characters on the next line. When the line break would occur between the two bytes of a double-byte character, JOINCHARS does not split the double-byte character; instead, it puts both bytes of the double-byte character on the next line.

Tip:

When you are using a multibyte character set, you can use the JOINBYTES function instead of the JOINCHARS function.

Return Value

TEXT or NTEXT

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

JOINCHARS(first-expressionnext-expression...)

Arguments

first-expression

An expression to which JOINCHARS joins next-expression. When the first-expression has a data type other than TEXT or NTEXT, JOINCHARS converts it to TEXT.

next-expression...

One or more expressions to join with first-expression. When an expression you want to concatenate has a data type other than TEXT or NTEXT, JOINCHARS converts it to TEXT.

Examples

Example 6-136 Using JOINCHARS to Concatenate Values

This example shows how you can use JOINCHARS to combine text with the current values of the two variables `name.product` and `price`. The variable `price` has a data type of DECIMAL; however, JOINCHARS automatically converts its value to TEXT to join it with the other text values.

```LIMIT product TO 'Canoes'
LIMIT month TO 'Dec96'
```

The JOINCHARS function

```JOINCHARS('Current Price for ' name.product ' is:  \$' price)
```

returns the following value.

```Current Price for Aluminum Canoes is:  \$200.03
```

## JOINCOLS

The JOINCOLS function joins the corresponding lines of two or more multiline text values. The function returns a multiline text value composed of the concatenated lines up to a length of 498 bytes (the maximum length of a single concatenated line).

The number of lines in the return value is always the same as that in the argument expression that has the most lines. When a given argument expression has fewer lines, JOINCOLS repeats its last line in each subsequent line of the return value. This repeating feature is useful when an argument expression is a single-line separator, such as a space or hyphen. See Example 6-137, "Joining the Columns of Two Text Expressions".

Return Value

TEXT or NTEXT

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

JOINCOLS(first-expressionnext-expression...)

Arguments

first-expression

An expression whose lines JOINCOLS joins with those of next-expression. When the expression has a data type other than TEXT or NTEXT, JOINCOLS converts it to TEXT. JOINCOLS ignores any arguments that have a value of `NA`

next-expression...

One or more expressions to join with first-expression. When an expression you want to concatenate has a data type other than TEXT or NTEXT, JOINCOLS converts it to TEXT. JOINCOLS ignores any arguments that have a value of `NA`

Examples

Example 6-137 Joining the Columns of Two Text Expressions

In the following example, each line in `citylist` is joined with a quoted text value, and the corresponding line from `cityreps`.

`citylist` has the following values.

```Boston
Houston
Chicago
Denver
```

`cityrep` has the following values.

```Brady
Lopez
Alfonso
Cody
```

The JOINCOLS function

```JOINCOLS(citylist ' -- ' cityreps)
```

returns the following.

```Boston -- Brady
Houston -- Lopez
Chicago -- Alfonso
Denver -- Cody
```

## JOINLINES

The JOINLINES function joins the values of two or more expressions into a single multiline textual value. When multiline text values are joined, all the lines of the first expression appear first, followed by all the lines of the second expression, and so forth. Normally the arguments for JOINLINES are text values, but they can have other data types.

Return Value

TEXT or NTEXT

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

JOINLINES(first-expression next-expression...)

Arguments

first-expression

An expression to which JOINLINES adds next-expression. When the expression has a data type other than TEXT or NTEXT, JOINLINES converts it to TEXT. JOINLINES ignores any arguments that have a value of `NA`.

next-expression...

One or more expressions to join with first-expression. When an expression you want to concatenate has a data type other than TEXT, JOINLINES converts it to TEXT. JOINLINES ignores any arguments that have a value of `NA`.

Examples

Example 6-138 Joining the Lines of Two Text Expressions

This example shows how to make a new list by adding the value `Regions` to the end of a variable called `mktglist`.

`mktglist` has the following initial values.

```Salespeople
Products
Services
```

The statement

```newlist = JOINLINES(mktglist 'Regions')
```

assigns the following to `newlist`.

```Salespeople
Products
Services
Regions
```

## KEY

The KEY function returns the value of the specified base dimension for a value of a conjoint dimension or a composite.

Return Value

The return value depends on the data type of the specified base dimension.

Syntax

KEY(dimension-expbase-dimension-exp)

Arguments

dimension-exp

An expression that specifies a value of a conjoint dimension or a composite. When you specify the conjoint dimension itself, KEY uses the first value in status. When you specify the composite itself, KEY uses the first value in status for every base dimension in the composite.

base-dimension-exp

An expression that specifies the name of a base dimension of the previously specified conjoint dimension or composite for which you want to know the dimension value.

Examples

Example 6-139 Reporting with a Conjoint

Suppose you want to produce a report of data dimensioned by a conjoint dimension. You can label each row with the base values of each conjoint dimension value with the KEY function. Each base value occupies its own column and you have more control over the layout.

The following program excerpt loops over the conjoint dimension `proddist`, whose values are a combination of `product` and `district`. Assume also that there is a variable named `dsales` which is dimensioned by `proddist`.

```DEFINE proddist DIMENSION <product district>
LD Conjoint dimension made up of combinations of product and district values
DEFINE dsales VARIABLE DECIMAL <month proddist>
LD Sparse sales data made dense by dimensioning by conjoint dimension proddist
```

The program excerpt shows `dsales` for three months. The base values of the conjoint dimension value each occupy their own column. For contrast, the second loop uses the conjoint dimension directly, without the KEY function. The conjoint dimension values are displayed in one column, with angle brackets.

```LIMIT month TO FIRST 3
FOR proddist
ROW KEY(proddist district) KEY( proddist product) ACROSS month: dsales
BLANK 2
FOR proddist
ROW W 25 proddist ACROSS month: dsales
```

The program produces the following report.

```Boston         Tents       32,153.52  32,536.30  43,062.75
Denver         Canoes      45,467.80  51,737.01  58,437.11
Atlanta        Sportswear 114,446.26 123,164.92 138,601.64
<Tents, Boston>            32,153.52  32,536.30  43,062.75
<Canoes, Denver>           45,467.80  51,737.01  58,437.11
<Sportswear, Atlanta>     114,446.26 123,164.92 138,601.64
```