| Oracle® OLAP DML Reference 11g Release 1 (11.1) Part Number B28126-01 |
|
|
View PDF |
| Oracle® OLAP DML Reference 11g Release 1 (11.1) Part Number B28126-01 |
|
|
View PDF |
Expressions represent data values in the syntax of the OLAP DML. This section provides the following information about OLAP DML expressions:
Detailed information about the various types of OLAP DML expressions:
An OLAP DML expression has a data type and can also have dimensions. You can use expressions as arguments in statements. An expression often performs a mathematical or logical operation. It always evaluates to a result in one of the workspace data types.
An expression can be:
A literal value. For example, 10 or 'East'
An analytic workspace object that contains multiple values. For example, the variable sales
A function that returns one or more values. For example, TOTAL or JOINLINES
Another expression that combines literal values, dimensions, variables, formulas, and functions with operators. For example, inflation*1.02
You can save an expression as a formula as described in "OLAP DML Formulas"
The data type of an expression is the data type of the resulting value. It might not be the same as the data type of the data objects that make up the expression; it depends on the data and on the operators and functions that are involved.
In addition, a conditional expression that is indicated by an IF... THEN...ELSE operator is supported. A conditional expression returns a value whose data type depends on the expressions in the THEN and ELSE clauses, not on the expression in the IF clause, which must be BOOLEAN.
Note:
Do not confuse a conditional expression with the IF...THEN...ELSE command in a program, which has similar syntax but a different purpose. The
IF statement does not have a data type and is not evaluated like an expression.An expression is dimensioned by a union of the dimensions of all of the variables, dimensions, relations, formulas, qualified data references, and functions in the expression:
Variables, relations, and formulas are dimensioned by the dimensions listed in the definition of the object.
Example 1: When the price variable is dimensioned by month and product, then the expression price * 1.2 is also dimensioned by month and product.
Example 2: When the units variable is dimensioned by month, product, and district, then the expression units * price is dimensioned by month, product, and district (even though the dimensions of the price variable are month and product only).
Qualified data references (QDRs) are dimensioned by all of the dimensions of the associated object, except for the dimensions being qualified. (For more information about qualified data references, see "Limiting a Dimension to a Single Value Without Changing Status".)
The return values of most OLAP DML functions are, in most cases, dimensioned by the union of the dimensions of the input arguments. However, some functions (such as aggregation functions) have fewer dimensions than the input arguments. In these cases, the dimensionality of the return value is documented in the topic for the function in Chapter 7, "OLAP DML Functions: A - K".
Note:
Unless otherwise noted, when you specify breakout dimensions or relations in an aggregation function, you change the dimensionality of the expression. The first dimension that you specify as a breakout dimension is the slowest varying and the last dimension that you specify is the fastest varying.Note:
You can change the dimensionality of an expression or subexpression using the CHGDIMS functionYou can find out the dimensions of an expression by issuing a PARSE statement, followed by the INFO function. PARSE evaluates the text of an expression; the INFO indicates how the expression is interpreted.
This example illustrates the use of the DIMENSION keyword with the INFO function to retrieve the dimensions of the expression just analyzed by PARSE. Assume that you issue the following statement.
PARSE 'TOTAL(sales region)'
The statement produces the following output.
SHOW INFO(PARSE DIMENSION) REGION
The number of values an expression yields depends on the dimensions of the expression and the status of those dimensions. An expression yields one data value for each combination of dimension values in the current status. For example, when three dimension values are in status for month, and two for product, then the expression price GT 100 results in six values (3 times 2).
Thus, to get the desired results, you must ensure that the dimensions of an expression are limited to the range of data you want to consider. In addition, you must consider any PERMIT statements that might limit access to the dimensions of the data.
When you want to specify a single value without changing the current status you can use a qualified data reference (QDR). Using a QDR, you can qualify a dimension (which enables you to specify one dimension value in an expression) or one or more dimensions of a variable or relation. For more information on dimension status, see "How to Specify the Set of Data that OLAP DML Operations Work Against"; for more information on QDRs, see "Limiting a Dimension to a Single Value Without Changing Status".
You can use an analytic workspace data object in an expression by specifying its name as described in "Syntax for Specifying an Object in an Expression". When calculating the expression, Oracle OLAP uses the data in the specified object as described in "How OLAP DML Data Objects Behave in Expressions".
Table 2-12 summarizes how Oracle OLAP uses the data in an object used as an argument in an expression.
Table 2-12 Objects in Expressions
| Object | Use in Expressions |
|---|---|
|
Variables |
As a one-dimensional or multi-dimensional array of data, depending on its definition. For example, as the target or source expression in an assignment statement. See also: "Using Variables and Relations in Expressions" and "Using Objects in Assignment Statements". |
|
Relations |
As a one-dimensional or multi-dimensional of data, depending on its definition. For example, as the target or source expression in an assignment statement as outlined in "Using Objects in Assignment Statements".
See also: "Using Variables and Relations in Expressions" , "Using Related Dimensions in Expressions". |
|
Dimensions |
As a one-dimensional array of data. When you use a See also: "Specifying a Value of a CONCAT Dimension" and "Using Related Dimensions in Expressions". |
|
Composites |
You can use a composite wherever you can use a dimension. See also: "Specifying a Value of a Composite" . |
|
Valuesets |
As a list of dimension values. See also: "Using Variables and Relations in Expressions" and the DEFINE VALUESET command. |
|
Dimension surrogates |
As a one-dimensional array. A surrogate provides an alternative set of values for a dimension. When you use a Note: You can use a surrogate rather than a dimension in a model, in a LIMIT command, in a qualified data reference, or in data loading with statements such as FILEREAD, FILEVIEW, SQL FETCH, and SQL IMPORT. A surrogate cannot be a participant object in any argument in a DEFINE statement that defines another object. |
|
Formulas |
As a sub-expression or as an expression in a statement. |
|
Programs |
For a program that does not return a value, use the program name as you would an OLAP DML command. For a program that returns a value, invoke the program the same way you invoke an OLAP DML function— use the program name in then expression and enclose the program arguments, if any, in parentheses. |
You can specify an analytic workspace object in an expression using the following syntax.
[[schema-name.]analytic-workspace-name!]object-name
The name of the schema in which the analytic workspace was defined when it was created. By default, an analytic workspace is created in the schema for the database user ID of the user issuing the AW CREATE statement. In almost any DML statement, you can specify the full name of an analytic workspace (for example, Scott.demo).
The name of the workspace that contains the desired object. By specify the analytic workspace name along with the object name you create a qualified object name (QON) for the object. Using a qualified object name for an object is recommended except in those situations described in "When Not to Use Qualified Object Names".
You can specify the value for analytic-workspace-name in any of the following ways:
The name of an analytic workspace. A workspace name is assigned when an analytic workspace is created with an AW CREATE statement.
The alias name of an analytic workspace. An analytic workspace alias is an alternative name for an attached analytic workspace. You can assign or delete an alias with an AW ALIAS LIST statement. An alias is in effect from the time it is assigned to the time that the workspace is detached (or until the alias is deleted). Therefore, each time you attach an unattached workspace, you must reassign its aliases.
One reason for assigning an alias is to have a short way to reference an analytic workspace that belongs to a schema that is not yours. For example, you can use the alias in qualified object names and statements that reference such an analytic workspace. Another reason for assigning an alias is to write generic code that includes a reference to an analytic workspace but does not hard-code its name. With the alias providing a generic reference, you can assign the alias and run the code on different workspaces at different times.
Within an aggregation specification, model, or program, you can use THIS_AW to qualify an object name. When Oracle OLAP compiles an object, it interprets any occurrence of THIS_AW as the name of the workspace in which the object is being compiled. Thus if you have an analytic workspace named myworkspace that contains a program named myprog and a variable named myvar, Oracle OLAP interprets a statement myvar=1 as though it was written myworkspace!myvar=1. Within a program, you can retrieve the value of THIS_AW using the THIS_AW option.
When you do not specify a value for analytic-workspace-name, Oracle OLAP assumes that the specified object is in the current analytic workspace. The current analytic workspace is the first analytic workspace in the list of the active analytic workspaces that you view with an AW LIST statement. You can retrieve the name of the current analytic workspace by using the AW function with the NAME keyword.
Note:
Your session does not have to have a current analytic workspace. When you start Oracle OLAP without specifying an analytic workspace name, then theEXPRESS analytic workspace is first on the list. However, in this case, the EXPRESS analytic workspace is not current; there is no current analytic workspace until you specify one with the AW command.The name of the object unless the object is an unnamed composite. When the object is an unnamed composite, use the following syntax.
SPARSE <basedims....>
For the basedims argument, specify the names of the dimensions, separated by spaces, for which the unnamed composite was created. For an example of using an unnamed composite in an OLAP DML statement, see Example 10-104, "Reporting Data Dimensioned by Composites".
Objects with the same name in different workspaces are treated as completely separate objects, and no similarity or relationship is assumed to exist between them. Any OLAP DML language restrictions that apply between objects in different workspaces apply even when the objects have the same name. For example, you cannot dimension an object in one workspace by a dimension that resides in another workspace, even when both workspaces have dimensions with the same name.
Although the use of qualified object names for objects is typical, there are a number of considerations to keep in mind:
There are some situations where you cannot use a qualified object name or do not need to use a qualified object name. See "When Not to Use Qualified Object Names" for more information
Before you use ampersand substitution when creating a qualified object name you need to understand how and when the substitution occurs. See "Using Ampersand Substitution for Workspace and Object Names" for more information.
Special considerations apply when passing a qualified object name as an argument to a program. See "Passing Qualified Object Names to Programs" for more information.
When Not to Use Qualified Object Names
Generally it is good practice to use a qualified object name in an expression. However, there are some situations where you cannot use a qualified object name or when a qualified object name is not necessary:
The following objects cannot have qualified object names:
An object that is local to a particular program because it was created by an ARGUMENT or VARIABLE statement.
The NAME dimension of any given workspace. When you reference the NAME dimension, Oracle OLAP always uses the NAME dimension of the current workspace.
You do not need to use a qualified object name in the following circumstances:
In the qualifiers of a qualified data reference (QDR). Only the object being qualified needs to be named with a qualified object name. Any unqualified names are assumed to apply to objects in the same workspace as the object being qualified.
In an unnamed composite, when you specify one base dimension as a qualified object name, then all the others are assumed to come from the same workspace.
In a named composite, when the name is a qualified object name then its base dimensions are assumed to come from the same workspace.
In a model, when you specify the solution variable as a qualified object name, then all the dimensions named in DIMENSION statements are assumed to come from the same workspace.
Using Ampersand Substitution for Workspace and Object Names
The workspace name, or the object name, or both can be supplied using ampersand substitution. However, take care when using a qualified object name with ampersand substitution because Oracle OLAP parses the qualified object name (with its exclamation point) before it resolves the ampersand reference. For example, in the expression &awname!objname, the ampersand (&) applies to the entire qualified object name, not just to the workspace name.
Passing Qualified Object Names to Programs
When you pass a qualified object name as an argument to a program and you use an ARGUMENT statement and the ARG, ARGFR, and ARGS functions, the entire qualified object name is considered to be a single argument. Its component parts are not passed separately.
In most cases, you refer to the value of a dimension merely by specifying the value following the conventions for the data type of the value. For example, assume that you have a TEXT dimension named geog. You can add the value "World" to the dimension by issuing the following statement.
MAINTAIN geog ADD 'World'
Note, however, that when you use a TEXT dimension value in a numeric expression or compare values in a non-numeric dimension, Oracle OLAP uses the INTEGER position number of the value in the array (as based on the default status list) rather than the value itself.
Special considerations apply to specifying the values of composites and concat dimensions.
You can specify a value of a composite in the following ways:
By specifying a set of values of the base dimensions of the composite using the following syntax.
{composite_name | SPARSE} {<base_dimension_name base_dimension_value }, ...>
(Named composites only) By specifying just the values of the composite using the following syntax.
composite_name <base_dimension_value ...>
where base_dimension_value is a set of values of the base dimensions, in the order in which they were defined in the composite, separated by spaces.
Once you have defined a unique CONCAT dimension, you can refer to its values simply by specifying the values of the base dimensions.
However, you must specify a value of a nonunique CONCAT dimension as a concatenation of the name of the base dimension and the base dimension value separated by a colon (:) and a space and enclosed in angle brackets(<>). In an expression, use the following format.
<BASE_DIMENSION_NAME: base_dimension value>
For example, assume that you have defined the base dimensions named city and state and, a CONCAT dimension for them named geog. When you report on the geog dimension, the values of geog include the names of the base dimensions along with the values.
DEFINE city DIMENSION TEXT DEFINE state DIMENSION TEXT DEFINE geog DIMENSION CONCAT(city state) MAINTAIN city ADD 'New York' MAINTAIN state ADD 'New York' REPORT geog GEOG ----------------------------------- <CITY: New York> <STATE: New York>
The syntax of some OLAP DML statements (for example, some variations of the LIMIT command) include two dimension arguments referred to as a dimension, and a related dimension. Other OLAP DML statements (for example, AVERAGE, ANY, COUNT, CUMSUM, NONE, LARGEST, SMALLEST, and TOTAL) allow you to specify the dimensionality of the result in terms of a related dimension. In these contexts, the related dimension is any dimension that shares a relation with another dimension.
Even though the value that you specify for the arguments in these statements is the name of a dimension, in actuality Oracle OLAP uses a relation between the dimensions to perform its calculations. When the two dimensions share only one relation, the behavior is clear. Oracle OLAP performs the calculation based on the values in that relation.
However, when two dimensions share more than one relation, then the behavior is less clear. In some cases, as with a LIMIT using LEVELREL command, you can specify the shared relation you want Oracle OLAP to use. In other cases, the statement syntax does not allow you to specify the name of a relation. In this case, Oracle OLAP chooses among the multiple relations as described in "How Oracle OLAP Chooses Between Multiple Relations".
In expressions, a variable is referenced as an array containing values of the specified data type. A relation is referenced as an array containing values of the specified dimension. In most other respects, variables and relations (both typically multidimensional objects) share the same characteristics.
In most cases, when you use OLAP DML statements with variables that are defined with composites, the statements treat those variables as if they were defined with base dimensions:
You can access a variable that is dimensioned by a composite by requesting any of the base dimension values.
The values of a composite that are in status are determined by the status of the base dimensions of the composite. Composites are not dimensions, and therefore, they do not have any independent status.
When you use a REPORT statement or any other statement that loops over a variable that uses a composite, the default behavior is to evaluate all the combinations of the values of the base dimensions of the composite that are in status. Any combinations that do not exist in the composite display NA for their associated data.
For example, the following statements create a report for the East region that shows the number of coupons issued for sportswear from January through March 2002. Since no coupons were issued in March 2002, the report displays NA in that column.
LIMIT month TO 'Jan02' 'Feb02' 'Mar02'
LIMIT market TO 'East'
LIMIT product TO 'Sportswear'
REPORT coupons
MARKET: EAST
------------COUPONS-------------
-------------MONTH--------------
PRODUCT Jan02 Feb02 Mar02
-------------- ---------- ---------- ----------
Sportswear 1,000 1,000 NA
However, for performance reasons, you can change the default looping behavior for statements such as REPORT, ROW, and the assignment statement (SET) so that they loop over the values in the composite rather than all of the base dimension values.
A qualified data reference (QDR) is a way of limiting one or more dimensions of a data object to a single value. QDRs are useful when you want to specify a single value of a data object without changing the current status. Using a QDR, you can qualify a dimension (which enables you to specify one dimension value in an expression) or one or more dimensions of a variable or relation.
Sometimes the syntax of a QDR is ambiguous and could either be misinterpreted or cause a syntax error. In this case, you can use the QUAL function to explicitly specify a qualified data reference (QDR).
You specify a qualified data reference using the following syntax
expression(dimname1 dimexp1 [, dimname2 dimexp2. . .])
The dimname argument is the name of one of the dimensions, or a dimension surrogate of the dimension, of the expression and the dimexp argument is one of the following:
A value of dimname.
Note:
The setting of the LIMITSTRICT option determines how Oracle OLAP behaves when a a QDR specifies a nonexistent value. By default, when you specify a nonexistent value, Oracle OLAP treats the nonexistent value as an invalid value and issues an error. If, instead, you want Oracle OLAP to treat a nonexistent value as anNA value, set the value of LIMITSTRICT to NOA text expression whose result is a value of dimname.
A numeric expression whose result is the logical position of a value of dimname.
A relation of dimname.
Note:
When syntax of a QDR is ambiguous and could either be misinterpreted or cause a syntax error, use the QUAL function to explicitly specify a qualified data reference (QDR).You can qualify any or all of a dimensions of a variable using either of the following techniques:
The QDR can temporarily limit a dimension of the variable by selecting one specified value of the dimension. This value can be outside the current status.
The QDR can replace a dimension of the variable with a less aggregate related dimension when you supply the name of an appropriate relation as the qualifier. The dimension is temporarily replaced by the dimension(s) of the relation.
For example, the variable sales has three dimensions, month, product, and district. You might want to compare total sales in Boston to the total sales in all cities. In a single statement, you want district to be limited to two different values:
For the numerator of the expression, you want the status of district to be Boston.
For the denominator of the expression, you want the status of district to be ALL.
The following statement lets you calculate this result by using a QDR.
SHOW sales(district 'Boston')/TOTAL(sales)
You can qualify more than one of the dimensions of a variable. For example, when you qualify all the dimensions of the sales variable by specifying one dimension value of each dimension, then you narrow sales down to a single–cell value.
To fetch sales for Jun02, Tents, and Seattle, use the following QDR.
SHOW sales(month 'Jun02', product 'Tents', district 'Seattle')
This statement fetches a single value.
You can use a qualified data reference with the target expression of an assignment (SET) statement. This lets you assign a value to a specific cell in a data object.
The following example assigns the value 10200 to the data cell of the sales composite that is specified in the qualified data reference. When the composite named sales does not already have a value for the combination Boston and Tents, then this value combination is added to the composite, thus adding the data cell.
sales(market 'Boston' product 'Tents' month 'Jan99')= 10200
When you use a relation as the qualifier in the QDR, you replace a dimension of the variable with the dimension or dimensions of the relation. The relation must be related to the dimension that you are qualifying, and it must be dimensioned by the replacement dimension.
Example 2-1 Replacing a Dimension in a Variable
Suppose you have two variables, sales and quota, which are dimensioned by month, product, and district. A third variable, division.mgr, is dimensioned by month and division. You also have a relation between division and product, called division.product. These objects have the following definitions.
DEFINE sales VARIABLE DECIMAL <month product district> LD Sales Revenue DEFINE quota VARIABLE DECIMAL <month product district> DEFINE division.mgr VARIABLE TEXT <month division> DEFINE division.product RELATION division <product> LD Division for each product
The following statement produces the report following it.
REPORT division.mgr
-------------------DIVISION.MGR----------------------
----------------------MONTH--------------------------
DIVISION JAn02 Feb02 Mar02 Apr02 May02 Jun02
-------- -------- -------- -------- -------- -------- --------
Camping Hawley Hawley Jones Jones Jones Jones
Sporting Carey Carey Carey Carey Carey Musgrave
Clothing Musgrave Musgrave Musgrave Musgrave Musgrave Wong
Suppose you want to obtain a report that shows the fraction by which sales have exceeded quota and you want to include the appropriate division manager for each product. You can show the division manager for each product by using the relation division.product, which is related to division and dimensioned by product, as the qualifier. The QDR replaces the division dimension with product, so that it has the same dimensions as the other expression in the report sales / quota. The following statement produces the report following it.
REPORT DOWN month sales W 6 sales/quota W 8 HEADING -
'MANAGER' division.mgr(division division.product)
DISTRICT: BOSTON
-----------------------------PRODUCT------------------------------------
----TEnts---- ---canoes---- --racquets--- --sportswear-- ---footwear---
Sales/ Sales/ Sales/ Sales/ Sales/
Month Quota Manager Quota Manager Quota Manager Quota Manager Quota Manager
------ ----- ------- ----- ------- ----- ------- ----- -------- ----- --------
Jan02 1.00 Hawley 0.82 Hawley 1.02 Carey 0.91 Musgrave 0.92 Musgrave
Feb02 0.84 Hawley 0.96 Hawley 1.00 Carey 0.80 Musgrave 1.07 Musgrave
Mar02 0.87 Jones 0.95 Jones 0.87 Carey 0.88 Musgrave 0.91 Musgrave
Apr02 0.91 Jones 0.93 Jones 0.99 Carey 0.94 Musgrave 0.95 Musgrave
...
You can also use a QDR to qualify a relation (which is really a special kind of variable).
Suppose the region.district relation is dimensioned by district. When you qualify district with the value Seattle, then the value of the expression is the value of the relation for Seattle. Because the QDR specifies one value of district, the expression has a single–cell result.
The definition of region.district is as follows.
DEFINE region.district RELATION region <district> LD The region for each district
The following statement displays the value WEST.
SHOW region.district(district 'Seattle')
You can use a QDR to qualify the dimension itself, which enables you to specify one dimension value in an expression. The following expression specifies one value of district, the one contained in the single-cell variable mydistrict.
district(district mydistrict)
For a concat dimension, you can use a QDR to qualify the dimension by specifying a value from one of the base dimensions of the concat dimension. The following expression specifies one value of reg.dist.ccdim, a concat dimension that has region and district as its base dimensions. The costs variable is dimensioned by the division and reg.dist.ccdim dimensions.
SHOW reg.dist.ccdim(district 'Boston')
The preceding expression produces the following result.
<DISTRICT: Boston>
An ampersand character (&) at the beginning of an expression substitutes the value of the expression for the expression itself in a statement.When you use an ampersand with a QDR, you must enclose the whole expression in parentheses when you want the variable to be qualified before the substitution is made.
Suppose you have a text variable named myvar that is dimensioned by reptype and that contains the names of variables. Remember that it is myvar that is dimensioned by reptype, not the variables named by myvar. Therefore, you must use parentheses so that myvar is qualified and the resulting value is used in a REPORT statement.
REPORT &(myvar(reptype 'actual'))
When you do not use parentheses and the variable that is specified in myvar is sales, then you get an error message that sales is not dimensioned by reptype.
At any given time, some of the cells of an analytic workspace data object may be empty. An empty cell occurs when a specific data value has not been assigned to it or when a data value cannot be calculated for the cell. The value of any empty cell in an object is NA. An NA value has no specific data type. Certain functions (for example, the aggregation functions) return an NA values when the information that is requested with the function is not available or cannot be calculated. Similarly, an expression whose value cannot be calculated has NA as its value.
Note:
To support OLAP DML composite-dimensioned variables that correspond to relational fact tables with null facts, OLAP has a special NA value which is controlled by an NA2 bit. For more information on how Oracle OLAP manages NA values controlled by NA2 bits, see "NA2 Bits and Null Tracking".There are cases in which you might specify an operation for which no data is available. For example, there might be no appropriate value for a given cell in a variable, for the return value of a function, or for the value of an expression that includes an arithmetic operator. In these cases, an NA (Not Available) value is automatically supplied.
To set the values of a variable or relation to NA, you can use an assignment statement (SET), as shown in the following example.
sales = NA
A number of options and functions control how NA values are treated. For example:
The NA options listed in "Options by Category".
The NAFILL function returns the values of the source expression with any NA values appearing as the specified fill expression. You can include this function in an expression to control the format of its value.
System properties listed in Chapter 5, "OLAP DML Properties".
A numeric expression evaluates to any of the numeric data types. The data in a numeric expression can be any combination of the following:
Numeric literals as discussed in "Numeric Data Types".
Numeric variables or formulas
Dimensions
Functions that yield numeric results
Date literals, variables, formulas, or functions
In addition, you can join any of these expressions with the arithmetic operators for a more complex numeric expression. You use arithmetic operators in numeric expressions with numeric data, which returns a numeric result. You can also use some arithmetic operators in date expressions with a mix of date and numeric data, to retrieve either a date or numeric result.
A number of options determine how Oracle OLAP handles numeric expressions.
You can include any type of numeric data in the same numeric expression.
The data type of the result is determined according to the following rules:
When all the data in the expression is INTEGER or SHORTINTEGER, and the only operations are addition, subtraction, and multiplication, then the result is INTEGER.
When any of the data is NUMBER, then the result is NUMBER.
When any of the data is DECIMAL or SHORTDECIMAL, and no data is NUMBER, then the result is DECIMAL.
When you perform any division or exponentiation operations, then the result is DECIMAL.
When you use a dimension with a data type of TEXT in a numeric expression, the dimension value is treated as a position (an INTEGER) and is used numerically. The position number is based on the default status list, not on current status.
All decimal data is converted to floating point format, both for storing and for calculations. In floating point format, a number is represented by means of a mantissa and an exponent. The mantissa and the exponent are stored as binary numbers. The mantissa is a binary fraction which, when multiplied by a number equal to 2 raised to the exponent, produces a number that equals or closely approximates the original decimal number.
Because there is not always an exact binary representation for a fractional decimal number, just as there is not an exact representation for the decimal value of 1/3, fractional parts of decimal numbers cannot always be represented exactly as binary fractions. Arithmetic operations on floating point numbers can result in further approximations, and the inaccuracy gradually increases with the number of operations. In addition to the approximation factor, the available number of significant digits affects the exactness of the result.
For all of these reasons, a result computed by the TOTAL, AVERAGE, or other aggregation functions on a DECIMAL or SHORTDECIMAL variable can differ in the least significant digits from a result you compute by hand. Because the SHORTDECIMAL data type provides a maximum of only seven significant digits, you see more of these differences with SHORTDECIMAL data. Therefore, you might want to use the NUMBER data type when accuracy is more important than computational speed, such as variables that contain currency amounts.
Another result of the fact that some fractional decimal numbers cannot be exactly represented by binary fractions is that for such numbers, the DECIMAL data type offers a different and closer approximation than the SHORTDECIMAL data type, because it has more significant digits. This can lead to problems when SHORTDECIMAL and DECIMAL data types are mixed in a comparison expression. For information on how to handle such comparisons, see "Boolean Expressions" .
You can control the following types of errors:
Division by zero. When you divide an NA value by zero, then the result is NA; no error occurs. Dividing a non-NA value by zero normally produces an error. When a divide-by-zero error occurs when you are making a calculation on dimensioned data, then you can end up with partial results. When you use REPORT or an assignment statement (SET), values are reported or stored as they are calculated, so the division by zero halts the loop before it has gone through all the values.
When you want to suppress the divide-by-zero error, then you can change the value of the DIVIDEBYZERO option to YES. This means that the result of any division by zero is NA and no error occurs. This allows the calculation of the other values of a dimensioned expression to continue.
Root of negative numbers. It is normally an error to try to take the root of a negative number (which includes raising a number to a non-integer power). When you want to suppress the error message and allow the calculation of roots for non-negative values of the expression to continue, then set the ROOTOFNEGATIVE option to YES.
Overflow errors. The DECIMALOVERFLOW option works in a similar manner to DIVIDEBYZERO. It lets you control whether an error is generated when a calculation produces a decimal result larger than it can handle.
A text expression evaluates to data with the TEXT, NTEXT, or ID data type. Text expressions can be any combination of the following:
Text literals. For example, 'Boston' or 'Current Sales Report'
Text dimensions. For example, district or month
Text variables or formulas. For example, product.name
Functions that yield text results. For example, JOINLINES('Product: ' product.name)
Oracle OLAP supports text expressions in all languages that you can identify using the NLS_LANGUAGE option. It also supports multi-language programs and applications using a language dimension.
See also:
"Working with Language Dimension Status"When you use a DATETIME value where a text value (TEXT, NTEXT, or ID) is expected, or when you store a DATETIME value in a text variable, then the DATETIME value is automatically converted to a text value.
The format of a DATETIME value is controlled by the NLS_DATE_FORMAT option. Once a DATETIME value is stored in a text variable, the NLS_DATE_FORMAT setting has no impact.
TEXT and NTEXT data are interchangeable in most cases. However, implicit conversion can occur, such as when an NTEXT value is assigned to a TEXT variable. When TEXT is converted to NTEXT, no data loss occurs because the UTF-8 character encoding of the NTEXT data type encompasses most other data types. However, when NTEXT is converted to TEXT, data loss occurs when NTEXT characters are not represented in the workspace character set.
When TEXT and NTEXT values are used together, for example in a call to the JOINCHARS function, the TEXT value is converted to NTEXT and an NTEXT value is returned.
As discussed in "Datetime and Interval Data Types", the OLAP DML supports the same datetime and interval data types that are supported by SQL. This section discusses:
A datetime expression yields a value of one of the datetime data types. A datetime expression has the following syntax.
datetime_value_expr AT LOCAL |
TIME ZONE { ' [ + | - ] hh:mm' | DBTIMEZONE | 'time_zone_name' | expr }
A datetime_value_expr can be a datetime value or a compound expression that yields a datetime value. Datetimes and intervals can be combined according to the rules defined in Table 2-7, "Datetime Fields and Values". The three combinations that yield datetime values are valid in a datetime expression.
If you specify AT LOCAL, Oracle uses the current session time zone.
The settings for AT TIME ZONE are interpreted as follows:
The string '(+|-)HH:MM' specifies a time zone as an offset from UTC.
DBTIMEZONE: Oracle uses the database time zone established (explicitly or by default) during Database creation.
SESSIONTIMEZONE: Oracle uses the session time zone established by default or in the most recent ALTER SESSION statement.
time_zone_name: Oracle returns the datetime_value_expr in the time zone indicated by time_zone_name. For a listing of valid time zone names, query the V$TIMEZONE_NAMES dynamic performance view.
Note:
Timezone region names are needed by the daylight savings feature. The region names are stored in two time zone files. The default time zone file is a small file containing only the most common time zones to maximize performance. If your time zone is not in the default file, then you will not have daylight savings support until you provide a path to the complete (larger) file by way of theORA_TZFILE environment variable.expr: If expr returns a character string with a valid time zone format, Oracle returns the input in that time zone. Otherwise, Oracle returns an error.
An interval expression yields a value of DSNTERVAL or MYINTERVAL where the expression has the following syntax.
interval_value_expr DAY [(leading_field_precision)] TO
SECOND [(fractional_second_precision)]| YEAR [(leading_field_precision)] TO MONTH
The interval_value_expr can be a DSNTERVAL or MYINTERVAL value or a compound expression that yields a DSNTERVAL or MYINTERVAL value. Datetimes and intervals can be combined according to the rules defined in Table 2-7, "Datetime Fields and Values" . The six combinations that yield interval values are valid in an interval expression.
Both leading_field_precision and fractional_second_precision can be any integer from 0 to 9. If you omit the leading_field_precision for either DAY or YEAR, then Oracle Database uses the default value of 2. If you omit the fractional_second_precision for second, then the Database uses the default value of 6. If the value returned by a query contains more digits that the default precision, then Oracle Database returns an error. Therefore, it is good practice to specify a precision that you know will be at least as large as any value returned by the query.
You can perform a number of arithmetic operations on date (DATETIME), timestamp (TIMESTAMP, TIMESTAMP_TZ, and TIMESTAMP_LTZ) and interval (DSINTERVAL and YMINTERVAL) data. Oracle calculates the results based on the following rules:
You can use NUMBER constants in arithmetic operations on date and timestamp values, but not interval values. Oracle internally converts timestamp values to date values and interprets NUMBER constants in arithmetic datetime and interval expressions as numbers of days. For example, SYSDATE + 1 is tomorrow. SYSDATE - 7 is one week ago. SYSDATE + (10/1440) is ten minutes from now. Subtracting the hire_date column of the sample table employees from SYSDATE returns the number of days since each employee was hired. You cannot multiply or divide date or timestamp values.
Oracle implicitly converts BINARY_FLOAT and BINARY_DOUBLE operands to NUMBER.
Each DATETIME value contains a time component, and the result of many date operations include a fraction. This fraction means a portion of one day. For example, 1.5 days is 36 hours. These fractions are also returned by Oracle built-in functions for common operations on DATETIME data. For example, the MONTHS_BETWEEN function returns the number of months between two dates. The fractional portion of the result represents that portion of a 31-day month.
If one operand is a DATETIME value or a numeric value (neither of which contains time zone or fractional seconds components), then:
Oracle implicitly converts the other operand to DATETIME data. (The exception is multiplication of a numeric value times an interval, which returns an interval.)
If the other operand has a time zone value, then Oracle uses the session time zone in the returned value.
If the other operand has a fractional seconds value, then the fractional seconds value is lost.
When you pass a timestamp, interval, or numeric value to a built-in function that was designed only for the DATETIME data type, Oracle implicitly converts the non-DATETIME value to a DATETIME value.
When interval calculations return a datetime value, the result must be an actual datetime value or the Database returns an error.
Oracle performs all timestamp arithmetic in UTC time. For TIMESTAMP_LTZ, Oracle converts the datetime value from the database time zone to UTC and converts back to the database time zone after performing the arithmetic. For TIMESTAMP_TZ, the datetime value is always in UTC, so no conversion is necessary.
Table 2-13, "Matrix of Datetime Arithmetic" is a matrix of datetime arithmetic operations. Dashes represent operations that are not supported.
Table 2-13 Matrix of Datetime Arithmetic
| Operand & Operator | DATETIME | TIMESTAMP | INTERVAL | Numeric |
|---|---|---|---|---|
|
DATETIME |
— |
— |
— |
— |
|
+ |
|
|
|
|
|
- |
|
|
|
|
|
* |
|
|
|
|
|
/ |
|
|
|
|
|
TIMESTAMP |
— |
— |
— |
— |
|
+ |
|
|
|
|
|
- |
|
|
|
|
|
* |
|
|
|
|
|
/ |
|
|
|
|
|
INTERVAL |
— |
— |
— |
— |
|
+ |
|
|
|
|
|
- |
|
|
|
|
|
* |
|
|
|
|
|
/ |
|
|
|
|
|
Numeric |
— |
— |
— |
— |
|
+ |
|
|
|
|
|
- |
|
|
|
|
|
* |
|
|
|
|
|
/ |
|
|
|
|
Examples You can add an interval value expression to a start time. Consider the sample table oe.orders with a column order_date.
A date-only expression is an expression that evaluates to the OLAP DML DATE data type as discussed in "Date-only Data Type". The expression might be a function that returns a date-only value, a date-only literal, or a more complex expression.
See also:
"Date-only Input Values", "Date-only Dimension Values", and "DATE-only Variable Display Styles".Calculating DATE-only Values
You can add numbers to a DATE value, or subtract numbers from them. Whole numbers are calculated as days, and decimal values are calculated as fractions of a day. For example, SYSDATE+1.5 adds 1 day and 12 hours to the current date and time. You cannot divide or multiply DATE values, and you cannot subtract them from numbers. For example, 1-SYSDATE and 1*SYSDATE return errors.
Using DATE-only Values in Arithmetic Expressions
When you use DATE values in arithmetic expressions, the result can be numeric or it can be a date. The legal operations for dates and the data type of the result are outlined in Table 2-14, "Legal Operations for DATE Values".
Table 2-14 Legal Operations for DATE Values
| Operation | Result |
|---|---|
|
Add or subtract a number from a date |
Future or prior date |
|
Subtract a date from a date |
The number of days between the dates. |
|
Add or subtract a number from a time period. |
The time period at the appropriate interval in the future or the past, similar to the return values of the LEAD or LAG function. The result is NA when there is no dimension value that corresponds to the result. The calculation is made based on the positions of the values in the default status list of the dimension. |
A Boolean expression is a logical statement that is either TRUE or FALSE. Boolean expressions can compare data of any type as long as both parts of the expression have the same basic data type. You can test data to see if it is equal to, greater than, or less than other data.
A Boolean expression can consist of Boolean data, such as the following:
BOOLEAN values (YES and NO, and their synonyms, ON and OFF, and TRUE and FALSE)
BOOLEAN variables or formulas
Functions that yield BOOLEAN results
BOOLEAN values calculated by comparison operators
For example, assume that your code contains the following Boolean expression.
actual GT 20000
When processing this expression, Oracle OLAP compares each value of the variable actual to the constant 20,000. When the value is greater than 20,000, then the statement is TRUE; when the value is less than or equal to 20,000, then the statement is FALSE.
When you are supplying a Boolean value, you can type either YES, ON, or TRUE for a true value, and NO, OFF, or FALSE for a false value. When the result of a Boolean calculation is produced, the defaults are YES and NO in the language specified by the NLS_LANGUAGE option. The read-only YESSPELL and NOSPELL options record the YES and NO values.
Table 2-11, "Comparison and Logical Operators" shows the comparison and logical operators. Each operator has a priority that determines its order of evaluation. Operators of equal priority are evaluated left to right, unless parentheses change the order of evaluation. However, the evaluation is halted when the truth value is already decided. For example, in the following expression, the TOTAL function is never executed because the first phrase determines that the whole expression is true.
yes EQ yes OR TOTAL(sales) GT 20000
A Boolean expression is a three-part clause that consists of two items to be compared, separated by a comparison operator. You can create a more complex Boolean expression by joining any of these three-part expressions with the AND and OR logical operators. Each expression that is connected by AND or OR must be a complete Boolean expression in itself, even when it means specifying the same variable several times.
For example, the following expression is not valid because the second part is incomplete.
sales GT 50000 AND LE 20000
In the next expression, both parts are complete so the expression is valid.
sales GT 50000 AND sales LE 20000
When you combine several Boolean expressions, the whole expression must be valid even when the truth value can be determined by the first part of the expression. The whole expression is compiled before it is evaluated, so when there are undefined variables in the second part of a Boolean expression, you get an error.
Use the NOT operator, with parentheses around the expression, to reverse the sense of a Boolean expression.
The following two expressions are equivalent.
district NE 'BOSTON' NOT(district EQ 'BOSTON')
Example 2-2 Using Boolean Comparisons
The following example shows a report that displays whether sales in Boston for each product were greater than a literal amount.
LIMIT time TO FIRST 2 LIMIT geography TO 'BOSTON' REPORT DOWN product ACROSS time: f.sales GT 7500
This REPORT statement returns the following data.
CHANNEL: TOTALCHANNEL
GEOGRAPHY: BOSTON
---F.SALES GT 7500---
--------TIME---------
PRODUCT Jan02 Feb02
-------------- ---------- ----------
Portaudio NO NO
Audiocomp YES YES
TV NO NO
VCR NO NO
Camcorder YES YES
Audiotape NO NO
Videotape YES YES
When the data you are comparing in a Boolean expression involves an NA value, a YES or NO result is returned when that makes sense. For example, when you test whether an NA value is equal to a non-NA value, then the result is NO. However, when the result would be misleading, then NA is returned. For example, testing whether an NA value is less than or greater than a non–NA value gives a result of NA.
Table 2-15, "Boolean Expressions with NA Values that Result in non-NA Values" shows the results of Boolean expressions involving NA values, which yield non-NA values.
When you get unexpected results when comparing numeric data, then there are several possible causes to consider:
One of the numbers you are comparing might have a small decimal part that does not show in output because of the setting of the DECIMALS option.
You are comparing two floating point numbers and at least one number is the result of an arithmetic operation.
You have mixed SHORTDECIMAL and DECIMAL data types in a comparison.
Oracle recommends that you use the ABS and ROUND functions to do approximate tests for equality and avoid all three causes of unexpected comparison failure. When using ABS or ROUND, you can adjust the absolute difference or the rounding factor to values you feel are appropriate for your application. When speed of calculation is important, then you probably want to use the ABS rather than the ROUND function.
Suppose expense is a decimal variable whose value is set by a calculation. When the result of the calculation is 100.000001 and the number of decimal places is two, then the value appears in output as 100.00. However, the output of the following statement returns NO.
SHOW expense EQ 100.00
You can use the ABS or the ROUND function to ignore these slight differences when making comparisons.
A standard restriction on the use of floating point numbers in a computer language is that you cannot expect exact equality in a comparison of two floating point numbers when either number is the result of an arithmetic operation. For example, on some systems, the following statement returns a NO instead of the expected YES.
SHOW .1 + .2 EQ .3
When you deal with decimal data, you should not code direct comparisons. Instead, you can use the ABS or the ROUND function to allow a tolerance for approximate equality. For example, either of the following two statements produce the desired YES.
SHOW ABS((.1 + .2) - .3) LT .00001 SHOW ROUND(.1 + .2) EQ ROUND(.3, .00001)
You cannot expect exact equality between SHORTDECIMAL and DECIMAL or NUMBER representations of a decimal number with a fractional component, because the DECIMAL and NUMBER data types have more significant digits to approximate fractional components that cannot be represented exactly.
Suppose you define a variable with a SHORTDECIMAL data type and set it to a fractional decimal number, then compare the SHORTDECIMAL number to the fractional decimal number, as shown here.
DEFINE sdvar SHORTDECIMAL sdvar = 1.3 SHOW sdvar EQ 1.3
The comparison is likely to return NO. What happens in this situation is that the literal is automatically typed as DECIMAL and converts the SHORTDECIMAL variable sdvar to DECIMAL, which extends the decimal places with zeros. A bit-by-bit comparison is then performed, which fails. The same comparison using a variable with a DECIMAL or a NUMBER data type is likely to return YES.
There are several ways to avoid this type of comparison failure:
Do not mix the SHORTDECIMAL with DECIMAL or NUMBER types in comparisons. To avoid mixing these two data types, you should generally avoid defining variables with decimal components as SHORTDECIMAL.
Use the ABS or ROUND function to allow for approximate equality. The following statements both produce YES.
SHOW ABS(sdvar - 1.3) LT .00001 SHOW ROUND(sdvar, .00001) EQ ROUND(.3, .00001)
Values are not compared in the same dimension based on their textual values. Instead, Oracle OLAP compares the positions of the values in the default status of the dimension. This enables you to specify statements like the following statement.
REPORT district LT 'Seattle'
Statements are interpreted such as these using the following process:
The text literal 'Seattle' is converted to its position in the district default status list of the dimension.
That position is compared to the position of all other values in the district dimension.
As shown by the following report, the value YES is returned for districts that are positioned before Seattle in the district default status list of the dimension, and NO for Seattle itself.
REPORT 22 WIDTH district LT 'Seattle' District DISTRICT LT 'Seattle' -------------- ---------------------- Boston YES Atlanta YES Chicago YES Dallas YES Denver YES Seattle NO
A more complex example assigns increasing values to the variable quota based on initial values assigned to the first six months. The comparison depends on the position of the values in the month dimension. Because it is a time dimension, the values are in chronological order.
quota = IF month LE 'Jun02' THEN 100 ELSE LAG(quota, 1, month)* 1.15
However, when you compare values from different dimensions, such as in the expression region lt district, then the only common denominator is TEXT, and text values are compared, not dimension positions.
You can compare two dates with any of the Boolean comparison operators. For dates, "less" means before and "greater" means after. The expressions being compared can include any of the date calculations discussed in Table 2-11, "Comparison and Logical Operators". For example, in a billing application, you can determine whether today is 60 or more days after the billing date in order to send out a more strongly worded bill.
bill.date + 60 LE SYSDATE
Dates also have a numeric value. You can use the TO_NUMBER and TO_DATE functions to change a value from a DATE to an INTEGER or an INTEGER to a DATE for comparison.
When you compare text data, you must specify the text exactly as it appears, with punctuation, spaces, and uppercase or lowercase letters. A text literal must be enclosed in single quotes. For example, this expression tests whether the first letter of each employee's name is greater than the letter "M."
EXTCHARS(employee.name, 1, 1) GT 'M'
You can compare TEXT and ID values, but they can only be equal when they are the same length. When you test whether a text value is greater or less than another, the ordering is based on the setting of the NLS_SORT option.
You can compare numbers with text by first converting the number to text. Ordering is based on the values of the characters. This can produce unexpected results because the text is evaluated from left to right. For example, the text literal 1234 is greater than 100,999.00 because 2, the second character in the first text literal, is greater than 0, the second character in the second text literal.
Suppose name.label is an ID variable whose value is 3-Person and name.desc is a TEXT variable whose value is 3-Person Tents.
The result of the following SHOW statement is NO.
SHOW name.desc EQ name.label
The result of the following statements is YES.
name.desc = '3-Person' SHOW name.desc EQ name.label
The Boolean operator LIKE is designed for comparing a text value to a text pattern. A text value is like another text value or pattern when corresponding characters match.
Besides literal matching, LIKE lets you use wildcard characters to match more than one character in a string:
An underscore (_) character in a pattern matches any single character.
A percent (%) character in a pattern matches zero or more characters in the first string.
For example, a pattern of %AT_ matches any text that contains zero or more characters, followed by the characters AT, followed by any other single character. Both DATA and ERRATA return YES when LIKE is used to compare them with the pattern %AT_.
The results of expressions using the LIKE operator are affected by the settings of the LIKECASE and LIKENL options.
No negation operator exists for LIKE. To accomplish negation, you must negate the entire expression. For example, the result of the following statement is NO.
SHOW NOT ('Boston' LIKE 'Bo%')
You can also compare a text literal to a relation. A relation contains values of the related dimension and the text literal is compared to a value of that dimension. For example, region.district holds values of region, so you can do the following comparison.
region.district EQ 'West'
A conditional expression is an expression you can use to select between values based on a condition. You can use conditional expression as part of any other expression as long as the data type is appropriate. Oracle OLAP supports the use of the following conditional expressions:
An IF expression is an expression you can use to select one of two values based on a Boolean condition.
Note:
Do not confuse the IF expression with the IF...THEN...ELSE command, which has similar syntax but a different purpose, and which must be used in an Oracle OLAP program. The IF...THEN...ELSE command does not have a data type and is not evaluated like an expression.An IF expression has the following syntax.
IF Boolean-expression THEN expression1 ELSE expression2
In most cases, expression1 and expression2 must be of the same basic data type (numeric, text, or Boolean) and the data type of the whole expression is determined using the same rules as those for the binary operators. However, when the data type of either expression1 or expression2 is DATE, it is possible for the other expression to have a numeric or text data type. Because Oracle OLAP expects both data types to be DATE, it will convert the numeric or text value to a DATE. Also, when the value of one expression is a dimension value then the value of the other expression is converted to a dimension value as it is for QDRs.
You can nest IF expressions; however, in this case, you might want to use a SWITCH expression instead as discussed in "SWITCH Expressions".
An IF expression is processed by first evaluating the Boolean expression; then:
When the result of the Boolean expression is TRUE, then expression1 is evaluated and returns that value.
When the result of the Boolean expression is FALSE, then expression2 is evaluated and returns that value.
The expression1 and expression2 arguments are any valid OLAP DML expressions that evaluate to the same basic data type. However, when the data type of either value is DATE, it is possible for the other value to have a numeric or text data type. Because both data types are expected to be DATE, Oracle OLAP converts the numeric or text value to a DATE. The data type of the whole expression is the same as the two expressions. When the result of the Boolean expression is NA, then NA is returned.
Example 2-3 Using an IF Expression
This example shows a sales bonus report. The bonus is 5 percent of the amount that sales exceeded budget, but when sales in the district are below budget, then the bonus is zero.
LIMIT month TO 'Jan02' TO 'Jun02'
LIMIT product TO 'Tents'
REPORT DOWN district IF sales-sales.plan LT 0 THEN 0
ELSE .05*(sales-sales.plan)
PRODUCT: TENTS
---IF SALES-SALES.PLAN LT 0 THEN 0 ELSE .05*(SALES-SALES.PLAN)---
----------------------MONTH------------------------------
DISTRICT Jan02 Feb02 Mar02 Apr02 May02 Jun02
--------- -------- -------- -------- ------- --------- ----------
Boston 229.53 0.00 0.00 0.00 584.51 749.13
Atlanta 0.00 0.00 0.00 190.34 837.62 1,154.87
Chicago 0.00 0.00 0.00 84.06 504.95 786.81
...
A SWITCH expression consists of a series of CASE expressions. You can use a SWITCH expression as an alternative to a complicated, nested IF ... THEN ... ELSE expression when all the conditions are equality comparisons with a single value.
Note:
Do not confuse the SWTICH expression with the SWITCH command, which has similar syntax but a different purpose, and which must be used in an Oracle OLAP program. The SWITCH command is not evaluated like an expression.A SWITCH expression has the following syntax.
SWITCH expression DO { case-label ... exp [,] } ... DOEND
where case-label has the following syntax:
CASE exp: | DEFAULT:
When processing a SWITCH expression, Oracle OLAP compares each CASE expression in succession until it finds a match. When a match is found, it returns the value specified after the last label of the current case group. When no match is found and a DEFAULT label is specified, it returns the value specified for the DEFAULT case; otherwise it returns NA.
Example 2-4 Using a SWITCH Expression Instead of an IF Expression
Assume that you have coded the following OLAP DML statement which includes nested IF...THEN...ELSE statements.
testprogram = IF testtype EQ 0 -
THEN 'program0' -
ELSE IF testtype EQ 1 -
THEN 'program1' -
ELSE IF testtype EQ 2 OR testtype EQ 3 -
THEN 'program2'
ELSE NA
You could, instead, code the same behavior using a SWITCH expression as shown below.
testprogram = SWITCH testtype DO -
CASE 0: 'program0', -
CASE 1: 'program1', -
CASE 2: -
CASE 3: 'program2', -
DEFAULT: NA -
DOEND
You could also code the same behavior using a SWITCH statement that spans fewer lines, omits commas, and omits the DEFAULT case since NA is the default return value when a match is not found.
testprogram = SWITCH testtype DO CASE 0: 'program0' CASE 1: 'program1' -
CASE 2: CASE 3: 'program2' DOEND
To construct a substitution expression, use an ampersand character (&) at the beginning of an expression. Using an ampersand (that is, the substitution operator) this way is also called ampersand substitution. The ampersand specifies that Oracle OLAP should evaluate an expression containing a substitution expression as follows:
Evaluate the expression following the ampersand (the substitution expression).
Evaluate the rest of the expression using the result of step 1 (that is, the result of the substitution expression).
Ampersand substitution gives you a level of indirection when you are specifying an expression. For example, when you specify an ampersand followed by a variable that holds the name of another variable, the value of the expression becomes the data in the second variable. Ampersand substitution lets you write more general programs that can operate on data that is chosen when the program is run. Note, however, that, Oracle OLAP does not compile program lines with ampersand substitution; instead these lines are interpreted when the program runs. To avoid ampersand substitution in a program, you can often use an IF or SWITCH command instead.
You cannot use ampersand substitution in model equations.
Example 2-5 Using Ampersand Substitution
Suppose you have a variable called curname that holds the name of one of the dimensions in the analytic workspace (product). When you execute the following statement, then REPORT produces the single value, product, which is the actual value stored in the curname variable.
REPORT curname CURNAME ---------- PRODUCT
However, when you execute the following statement, then REPORT produces the values of the dimension product.
REPORT &curname PRODUCT -------------- Tents Canoes Racquets Sportswear Footwear
Using Ampersand Substitution with QDRs
When you use an ampersand with a QDR, you must enclose the whole expression in parentheses if you want the variable to be qualified before the substitution is made.
Suppose you have a text variable named myvar that is dimensioned by reptype and that contains the names of variables. Remember that it is myvar that is dimensioned by reptype, not the variables named by myvar. Therefore, you must use parentheses so that myvar is qualified and the resulting value is used in a REPORT statement.
REPORT &(myvar(reptype 'actual'))
When you do not use parentheses and the variable that is specified in myvar is sales, then you get an error message that sales is not dimensioned by reptype.
