2 Data Types, Operators, and Expressions

Using LONGINTEGER Values

Most of the numeric data types return NA when a value is outside its range. However, the LONGINTEGER data type does not have overflow protection and returns an incorrect value when, for example, a calculation produces a number that exceeds its range. Use the NUMBER data type instead of LONGINTEGER when this is likely to be a problem.

Using NUMBER Values

When you define a NUMBER variable, you can specify its precision (p) and scale (s) so that it is sufficiently, but not unnecessarily, large. Precision is the number of significant digits. Scale can be positive or negative. Positive scale identifies the number of digits to the right of the decimal point; negative scale identifies the number of digits to the left of the decimal point that can be rounded up or down.

The NUMBER data type is supported by Oracle Database standard libraries and operates the same way as it does in SQL. It is used for dimensions and surrogates when a text or INTEGER data type is not appropriate. It is typically assigned to variables that are not used for calculations (like forecasts and aggregations), and it is used for variables that must match the rounding behavior of the Database or require a high degree of precision. When deciding whether to assign the NUMBER data type to a variable, keep the following facts in mind to maximize performance:

• Analytic workspace calculations on NUMBER variables is slower than other numeric data types because NUMBER values are calculated in software (for accuracy) rather than in hardware (for speed).

• When data is fetched from an analytic workspace to a relational column that has the NUMBER data type, performance is best when the data has the NUMBER data type in the analytic workspace because a conversion step is not required.

Text Literals

Enclose text literals in single quotes. Oracle OLAP recognizes unquoted alpha-numeric values as object names and double quotes as the beginning of a comment.

You can embed quoted strings within a quoted string, which is necessary when you want to specify the base dimension value of a composite or conjoint dimension or when a value includes an apostrophe. Since a single quotation mark is used in Oracle OLAP to indicate a text string, it is considered a special character when used within such a string. Consequently, to specify the literal value of a single quotation mark within a text string, precede the quotation mark with a backslash.

For example, suppose you want to find out if New York and Apple Sauce are a valid combination of base dimension values in the markprod conjoint dimension. The following statement produces the answer YES or NO.

SHOW ISVALUE(markprod, '<\'New York\' \'Apple Sauce\'>')

When embedded quoted strings have a further level of embedding, you must use backslashes before each special character, such as the apostrophe and the backslash that must precede it in "Joe's Deli," as shown in the following statement.

SHOW ISVALUE(markprod, '<\'Joe\\\'s Deli\' \'Apple Sauce\'>')

Escape Sequences

Table 2-4, "Recognized Escape Sequences" shows escape sequences that are recognized by Oracle OLAP.

Date-only Input Values

A valid input literal value of type DATE must conform to one of three styles: numeric, packed numeric, or month name. You can mix these styles throughout a session.

Numeric style

Specify the day, month, and year as three INTEGER values with one or more separators between them, using these rules:

• The day and month components can have one digit or two digits.

• For any year, the year component can have four digits (for example, 1997). For years in the range 1950 to 2049, the year component can, alternatively, have two digits (50 represents 1950, and so on).

• To separate the components, you can use a space, dash (-), slash (/), colon (:), or comma (,).

Examples: '24/4/97' or '24-04-1997'

Packed numeric style

Specify the day, month, and year as three INTEGER values with no separators between them, using these rules:

• The day and month components must have two digits. When the day or month is less than 10, it must be preceded by a zero.

• For any year, the year component can have four digits (for example, 1997). For years in the range 1950 to 2049, the year component can, alternatively, have two digits (50 represents 1950, and so on).

• You cannot use any separators between the date components.

Examples: '240497' or '04241997'

Month name style

Specify the day and year as INTEGER values and the month as text, using these rules:

• The month component must match one name listed in the MONTHNAMES option. You can abbreviate the month name to one letter or more, when you supply enough letters to uniquely match the beginning of a name in MONTHNAMES. The case of the letters in the month component (uppercase or lowercase) does not need to match the case in MONTHNAMES.

• The day component can have one digit or two digits.

• For any year, the year component can have four digits (for example, 1997). For years in the range 1950 to 2049, the year component can, alternatively, have two digits (50 represents 1950, and so on).

• When the day and year components are adjacent, they must have at least one separator between them. As separators, you can use a space, dash (-), slash (/), colon (:), or comma (,). When you want, you can place one or more separators between the day and month or between the year and month.

Examples: '24APR97' or '24 ap 97' or 'April 24, 1997'

Date-only Dimension Values

The format of a DATE -only value of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR is determined by the value name format (VNF) associated with the object. A VNF is a template that controls the input and display format for DATE -only values. The template can include format specifications for any of the components that identify a time period (day, month, calendar year, fiscal year, and period within a fiscal year). You associate a VNF with an object by adding a VNF statement to its definition. When you do not add a VNF to the definition of an object, the object uses the default VNF shown in Table 2-5, "Default VNFs for DWMQY Dimensions".

DATE-only values have independent input and output formats. You can enter DATE-only values in one style and report them in a different style.

DATE-only Variable Display Styles

When you show a DATE-only variable value in output, the format depends on the DATEFORMAT option. The default format is a 2-digit day, a 3-letter month, and a 2-digit year; for example, 03MAR97. The text for the month names depends on the MONTHNAMES option. To change the order of the month, day, and year components, see the DATEORDER option.

Datetime and Interval Fields

Both datetimes and intervals are made up of fields. The values of these fields determine the value of the data type. Table 2-7, "Datetime Fields and Values" lists the datetime fields and their possible values for datetimes and intervals.

Note: TIMEZONE_HOUR and TIMEZONE_MINUTE are specified together and interpreted as an entity in the format +|- hh:mm, with values ranging from -12:59 to +14:00.

Datetime Format Templates

A datetime format template is a template that describes the format of datetime data stored in a character string. A format model does not change the internal representation of the value in the Database. When you convert a character string into a date, a format model determines how Oracle Database interprets the string. In OLAP DML statements, you can use a format model as an argument of the TO_CHAR and TO_DATE functions to specify:

• The format for Oracle to use to return a value from the Database

• The format for a value you have specified for Oracle to store in the Database

You can use datetime format templates in the following functions:

• In the TO_* datetime functions to translate a character value that is in a format other than the default format into a datetime value. (The TO_* datetime functions are TO_CHAR, TO_DATE, TO_TIMESTAMP, TO_TIMESTAMP_TZ, TO_YMINTERVAL, and TO_DSINTERVAL.)

• In the TO_CHAR function to translate a datetime value that is in a format other than the default format into a string (for example, to print the date from an application)

The default datetime formats are specified either explicitly with the initialization parameter NLS_DATE_FORMAT or implicitly with the initialization parameter NLS_TERRITORY. You can change the default datetime formats for your session with the ALTER SESSION statement. You can override this default and specify a datetime format for use with a particular OLAP DML object by using the DATE_FORMAT command to add a datetime format to the definition of the object.

String-to-Date Conversion Rules

The following additional formatting rules apply when converting string values to datetime values (unless you have used the FX or FXFM modifiers in the format model to control exact format checking):

• You can omit punctuation included in the format string from the date 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 date string.

• If 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 2-8, "Oracle Format Matching".

'MM'

DATETIME Data Type

The OLAP DML DATETIME data type corresponds to the SQL DATE data type. As such, the format and language of DATETIME values are controlled by the settings of the NLS_DATE_FORMAT and NLS_DATE_LANGUAGE options. The DATETIME data type is supported by Oracle Database standard libraries and operates the same way in the OLAP DML as it does the DATE data type in SQL.

You can specify a DATETIME value as a string literal, or you can convert a character or numeric value to a date value with the TO_DATE function.

To specify a DATETIME value as a literal, you must use the Gregorian calendar. You can specify an ANSI literal, as shown in this example:

DATETIME '1998-12-25'

The ANSI date literal contains no time portion, and must be specified in exactly this format ('YYYY-MM-DD').

Alternatively you can specify a DATETIME value us the TO_DATE function and include, as in the following example:

TO_DATE('98-DEC-25 17:30','YY-MON-DD HH24:MI')

The default date format template for an Oracle DATETIME value is specified by the initialization parameter NLS_DATE_FORMAT. This example date format includes a two-digit number for the day of the month, an abbreviation of the month name, the last two digits of the year, and a 24-hour time designation.

Oracle automatically converts character values that are in the default datetime format into datetime values when they are used in datetime expressions.

If you specify a datetime value without a time component, then the default time is midnight (00:00:00 or 12:00:00 for 24-hour and 12-hour clock time, respectively). If you specify a datetime value without a date, then the default date is the first day of the current month.

Values of DATETIME always contain both the date and time fields. Therefore, if you use DATETIME values in an expression, you must either specify the time field in your query or ensure that the time fields in the DATETIME values are set to midnight. Otherwise, Oracle may not return the results you expect. You can use the TRUNC (date) function to set the time field to midnight, or you can include a greater-than or less-than condition in the query instead of an equality or inequality condition. However, if the expression contains DATETIME values other than midnight, then you must filter out the time fields in the query to get the correct result.

The date function SYSDATE returns the current system date and time. The function CURRENT_DATE returns the current session date. For information on SYSDATE, the TO_* datetime functions, and the default date format, see "Datetime functions" and the DATE_FORMAT command.

TIMESTAMP Data Type

The TIMESTAMP data type is an extension of the DATETIME data type. It stores the year, month, and day of the DATETIME data type, plus hour, minute, and second values. This data type is useful for storing precise time values.

SHOW SYSDATE
26-JUL-06
 
DEFINE mytimestamp VARIABLE TIMESTAMP
mytimestamp = SYSDATE
COLWIDTH = 30
REPORT mytimestamp
MYTIMESTAMP
------------------------------
         26-JUL-06 10.44.42 AM

The TIMESTAMP data type stores year, month, day, hour, minute, and second, and fractional second values. When you specify TIMESTAMP as a literal, the fractional seconds precision value can be any number of digits up to 9, as follows:

TIMESTAMP '1997-01-31 09:26:50.124'

TIMESTAMP_TZ Data Type

TIMESTAMP_TZ corresponds to the SQL TIMESTAMP WITH TIMEZONE data type. It is a variant of TIMESTAMP that includes a time zone offset in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time). This data type is useful for collecting and evaluating date information across geographic regions.

Oracle time zone data is derived from the public domain information available at ftp://elsie.nci.nih.gov/pub/. Oracle time zone data may not reflect the most recent data available at this site.

The TIMESTAMP_TZ data type is a variant of TIMESTAMP that includes a time zone offset. When you specify TIMESTAMP_TZ as a literal, the fractional seconds precision value can be any number of digits up to 9. For example:

TIMESTAMP '1997-01-31 09:26:56.66 +02:00'

Two TIMESTAMP_TZ values are considered identical if they represent the same instant in UTC, regardless of the TIME ZONE offsets stored in the data. For example,

TIMESTAMP '1999-04-15 8:00:00 -8:00'

equals

TIMESTAMP '1999-04-15 11:00:00 -5:00'

That is, 8:00 a.m. Pacific Standard Time equals 11:00 a.m. Eastern Standard Time.

You can replace the UTC offset with the TZR (time zone region) format element. For example, the following example has the same value as the preceding example:

TIMESTAMP '1999-04-15 8:00:00 US/Pacific'

To eliminate the ambiguity of boundary cases when the daylight saving time switches, use both the TZR and a corresponding TZD format element. The following example ensures that the preceding example returns a daylight saving time value:

TIMESTAMP '1999-10-29 01:30:00 US/Pacific PDT'

You can also express the time zone offset using a datetime expression.

If you do not add the TZD format element, and the datetime value is ambiguous, then Oracle returns an error if you have the ERROR_ON_OVERLAP_TIME session parameter set to TRUE. If that parameter is set to FALSE, then Oracle interprets the ambiguous datetime as standard time in the specified region.

TIMESTAMP_LTZ Data Type

TIMESTAMP_LTZ corresponds to the SQL TIMESTAMP WITH LOCAL TIMEZONE data type. It is another variant of TIMESTAMP that includes a time zone offset in its value. It differs from TIMESTAMP_LTZ in that data stored in the Database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When a user retrieves the data, Oracle returns it in the user's local session time zone. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time). This data type is useful for displaying date information in the time zone of the client system in a two-tier application.

Oracle time zone data is derived from the public domain information available at ftp://elsie.nci.nih.gov/pub/. Oracle time zone data may not reflect the most recent data available at this site.

The TIMESTAMP_LTZ data type differs from TIMESTAMP_TZ in that data stored in the Database is normalized to the database time zone. The time zone offset is not stored as part of the column data. There is no literal for TIMESTAMP_LTZ. Rather, you represent values of this data type using any of the other valid datetime literals. The table that follows shows some formats you can use to add a TIMESTAMP_LTZ value into object, along with the corresponding value returned by a OLAP DML statement such as a SHOW command.

Notice that if the value specified does not include a time component (either explicitly or implicitly, then the value returned defaults to midnight.

YMINTERVAL Data Type

YMINTERVAL corresponds to the SQL INTERVAL YEAR TO MONTH data type. It stores a period of time using the YEAR and MONTH datetime fields. This data type is useful for representing the difference between two datetime values when only the year and month values are significant.

Specify YMINTERVAL interval literals using the following syntax.

INTERVAL 'integer [- integer ]' YEAR|MONTH [(precision) ] [TO YEAR | MONTH ]

where

• 'integer [-integer]' specifies integer values for the leading and optional trailing field of the literal. If the leading field is YEAR and the trailing field is MONTH, then the range of integer values for the month field is 0 to 11.

• precision is the maximum number of digits in the leading field. The valid range of the leading field precision is 0 to 9 and its default value is 2.

If you specify a trailing field, it must be less significant than the leading field. For example, INTERVAL '0-1' MONTH TO YEAR is not valid.

The following YMINTERVAL literal indicates an interval of 123 years, 2 months:

INTERVAL '123-2' YEAR(3) TO MONTH

Examples of the other forms of the literal follow, including some abbreviated versions:

You can add or subtract one INTERVAL YEAR TO MONTH literal to or from another to yield another INTERVAL YEAR TO MONTH literal. For example:

INTERVAL '5-3' YEAR TO MONTH + INTERVAL'20' MONTH = 
INTERVAL '6-11' YEAR TO MONTH

DSINTERVAL Data Type

DSINTERVAL corresponds to the SQL INTERVAL DAY TO SECOND data type. It stores a period of time in terms of days, hours, minutes, and seconds. This data type is useful for representing the precise difference between two datetime values.

Specify DSINTERVAL interval literals using the following syntax.

INTERVAL 'integer|integer time_expr|time_expr

DAY|HOUR|MINUTE [(leading_precision)] | SECOND [leading_precision[, fractional_seconds_precision ])]

[ TO DAY|HOUR|MINUTE|SECOND [(fractional_seconds_precision) ]]

where

• integer specifies the number of days. If this value contains more digits than the number specified by the leading precision, then Oracle returns an error.

• time_expr specifies a time in the format HH[:MI[:SS[.n]]] or MI[:SS[.n]] or SS[.n], where n specifies the fractional part of a second. If n contains more digits than the number specified by fractional_seconds_precision, then n is rounded to the number of digits specified by the fractional_seconds_precision value. You can specify time_expr following an integer and a space only if the leading field is DAY.

• leading_precision is the number of digits in the leading field. Accepted values are 0 to 9. The default is 2.

• fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 1 to 9. The default is 6.

If you specify a trailing field, it must be less significant than the leading field. For example, INTERVAL MINUTE TO DAY is not valid. Because of this restriction, if SECOND is the leading field, the interval literal cannot have any trailing field.

The valid range of values for the trailing field are as follows:

• HOUR: 0 to 23

• MINUTE: 0 to 59

• SECOND: 0 to 59.999999999

Examples of the various forms of DSINTERVAL literals follow, including some abbreviated versions:

You can add or subtract one DAY TO SECOND interval literal from another DAY TO SECOND literal. For example.

INTERVAL'20' DAY - INTERVAL'240' HOUR = INTERVAL'10-0' DAY TO SECOND

ROWID Data Type

You can examine a row address of a relational table by querying the pseudocolumn ROWID. Values of this pseudocolumn are strings representing the address of each row. These strings have the data type ROWID.

The extended ROWID data type stored in a user column includes the data in the restricted rowid plus a data object number. The data object number is an identification number assigned to every database segment. You can retrieve the data object number from the data dictionary views USER_OBJECTS, DBA_OBJECTS, and ALL_OBJECTS. Objects that share the same segment (clustered tables in the same cluster, for example) have the same object number.

Extended rowids are stored as base 64 values that can contain the characters A-Z, a-z, 0-9, and the plus sign (+) and forward slash (/). Extended rowids are not available directly. You can use a supplied package, DBMS_ROWID, to interpret extended rowid contents. The package functions extract and provide information that would be available directly from a restricted rowid as well as information specific to extended rowids.

UROWID Data Type

The rows of some relational tables have addresses that are not physical or permanent or were not generated by Oracle Database. For example, the row addresses of index-organized tables are stored in index leaves, which can move. Rowids of foreign tables (such as DB2 tables accessed through a gateway) are not standard Oracle rowids.

Oracle uses universal rowids (urowids) to store the addresses of index-organized and foreign tables. Index-organized tables have logical urowids and foreign tables have foreign urowids. Both types of urowid are stored in the ROWID pseudocolumn (as are the physical rowids of heap-organized tables).

Oracle creates logical rowids based on the primary key of the table. The logical rowids do not change if the primary key does not change. The ROWID pseudocolumn of an index-organized table has a data type of UROWID. You can access this pseudocolumn as you would the ROWID pseudocolumn of a heap-organized table (that is, using a SELECT ... ROWID statement). To store the rowids of an index-organized table, then define a column of type UROWID for the table and retrieve the value of the ROWID pseudocolumn into that column.

Automatic Conversion of Textual Data Types

Oracle OLAP automatically converts NTEXT values to TEXT when they are specified as arguments to OLAP DML statements. This can result in data loss when the NTEXT values cannot be represented in the database character set

Automatic Conversion of Numeric Data Types

Oracle OLAP automatically converts SHORTINTEGER variables and INTEGER variables (with a fixed width of 1 byte to INTEGER (with a width of 4 bytes) for calculations. When you calculate a total of SHORTINTEGER variables, then you can obtain and report a result greater than 32,767 or less than -32,768. When you calculate a total of 1-byte INTEGER variables, then you can obtain and report a result greater than 127 or less than -128. However, when you try to assign the result to a SHORTINTEGER variable or a 1-byte INTEGER variable respectively, then the variable is set to NA.

Oracle OLAP automatically converts numeric data types according to the following rules:

• When you use a value with the SHORTINTEGER or SHORTDECIMAL data type in an expression, then the value is converted to its long counterpart before using it. See "Boolean Expressions" for information about problems that can occur when you mix SHORTDECIMAL and DECIMAL data types in a comparison expression.

• When you save the results of a calculation as a value with the SHORTINTEGER data type, then NA is stored when the result is outside the range of a SHORTINTEGER (E32768 to 32767).

• When you assign the value of a DECIMAL expression to an object with the INTEGER data type, then the value is rounded before storing or using it.

  Note:

  When a DECIMAL value is outside the range of an INTEGER, then an NA is stored.

• When you use a decimal value where a value with the INTEGER data type is required, then the value is rounded before storing or using it.

  Note:

  When the DECIMAL value is outside the range of an INTEGER, then an NA is stored.

• When you assign the value of a decimal expression to a variable with the SHORTDECIMAL data type, then only the first 7 significant digits are stored.

• When you combine NUMBER values with other numeric data types, then all values are converted to NUMBER.

When these conversion are not what you want, then you can use the CONVERT, TO_CHAR, TO_NCHAR, TO_NUMBER, or TO_DATE functions to get different results.

How the Data Type of an Expression is Determined

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 constitute 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.

How the Dimensionality of an Expression is Determined

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.

PARSE 'TOTAL(sales region)'

SHOW INFO(PARSE DIMENSION)
REGION

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.

Syntax for Specifying an Object in an Expression

You can specify an analytic workspace object in an expression using the following syntax.

[[schema-name.]analytic-workspace-name!]object-name

schema-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).

analytic-workspace-name

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 the EXPRESS 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.

object-name

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-103, "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.

Specifying Values of Dimensions and Composites in Expressions

In most cases, you refer to the value of a dimension 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.

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>

Using Variables and Relations in Expressions

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.

Limiting a Dimension to a Single Value Without Changing Status

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).

SHOW sales(district 'Boston')/TOTAL(sales)

SHOW sales(month 'Jun02', product 'Tents', district 'Seattle')

sales(market 'Boston' product 'Tents' month 'Jan99')= 10200

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 

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    

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

DEFINE region.district RELATION region <district>
LD The region for each district

SHOW region.district(district 'Seattle')

district(district mydistrict)

SHOW reg.dist.ccdim(district 'Boston')

<DISTRICT: Boston>

REPORT &(myvar(reptype 'actual'))

Specifying a Value of NA

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

Controlling how NA values are treated

Several 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".

Mixing Numeric Data Types

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.

Using Text Dimensions in Arithmetic Expressions

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 as a numeric. The position number is based on the default status list, not on current status.

Limitations of Floating Point Calculations

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" .

Controlling Errors During Calculations

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 nonENA value by zero normally produces an error. When a divideEbyEzero 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 divideEbyEzero error, then you can change the value of the DIVIDEBYZERO option to YES. Consequently, 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 nonEinteger power). When you want to suppress the error message and allow the calculation of roots for nonEnegative 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.

Language of Text Expressions

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.

Working with DATETIME Values in Text Expressions

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.

Working with NTEXT Data

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.

Datetime Expressions

A datetime expression yields a value of a datetime data type. 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 the time zone files under oracore/zoneinfo. The server always uses the large time zone file corresponding to the version number recorded in sys.props$.

• 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.

Interval Expressions

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 is at least as large as any value returned by the query.

Datetime/Interval Arithmetic

You can perform several 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.

Examples You can add an interval value expression to a start time. Consider the sample table oe.orders with a column order_date.

Creating Boolean Expressions

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')

LIMIT time TO FIRST 2
LIMIT geography TO 'BOSTON'
REPORT DOWN product ACROSS time: f.sales GT 7500

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

Comparing NA Values in Boolean Expressions

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 equals a nonENA 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 nonENA values.

Controlling Errors When Comparing Numeric Data

When you get unexpected results when comparing numeric data, then there are several possible causes to consider:

• A number 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.

SHOW expense EQ 100.00

SHOW .1 + .2 EQ .3

SHOW ABS((.1 + .2) - .3) LT .00001
SHOW ROUND(.1 + .2) EQ ROUND(.3, .00001)

DEFINE sdvar SHORTDECIMAL
sdvar = 1.3
SHOW sdvar EQ 1.3

Comparing Dimension Values

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:

1. The text literal 'Seattle' is converted to its position in the district default status list of the dimension.

2. That position is compared to the position of all other values in the district dimension.

3. 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.

Comparing Dates

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

Comparing Text Data

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 3EPerson and name.desc is a TEXT variable whose value is 3EPerson 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

SHOW NOT ('Boston' LIKE 'Bo%')

region.district EQ 'West'

IF...THEN...ELSE expression

An IF expression is an expression you can use to select one of two values based on a Boolean condition.

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 converts 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.

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

SWITCH Expressions

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.

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.

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
 

testprogram = SWITCH testtype DO -
                CASE 0: 'program0', -
                CASE 1: 'program1', -
                CASE 2: -
                CASE 3: 'program2', -
                DEFAULT: NA -
                DOEND

testprogram = SWITCH testtype DO CASE 0: 'program0' CASE 1: 'program1' -
              CASE 2: CASE 3: 'program2' DOEND