Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF


The VALUES function returns the default status list or the current status list of a dimension or dimension surrogate, or it returns the values in a valueset. VALUES returns a multiline text value that contains one dimension value on a line.


Because composites do not have status, you cannot use the VALUES function with a composite. When you attempt to do so, Oracle OLAP displays an error message.

Return Value



VALUES(dimension [keyword] [INTEGER])



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


One of the following keywords that specify whether you want the current status list or the default status list for a dimension or a surrogate:

  • NOSTATUS which indicates that VALUES should return the default status list of a dimension or dimension surrogate rather than its current status list.

  • STATUS which indicates that VALUES should return the current status list of a dimension or dimension surrogate (Default).

These keywords do not affect valuesets. For a valueset, VALUES returns all the values in that valueset whether you specify NOSTATUS, STATUS, or nothing.


When you use the INTEGER keyword, the function returns the position numbers of the dimension or dimension surrogate values rather than the values. When you use INTEGER with a valueset, the function returns the position numbers of the values in the existing dimension, not in the valueset.

Usage Notes

Using a LIMIT Statement With a STATUS Keyword Rather than VALUES

When possible, when you want Oracle OLAP to use the dimension values that are presently in status, use a LIMIT (using values) command with the STATUS keyword (or a LIMIT function with a similar construction) rather than using a VALUES statement. A LIMIT with the STATUS keyword is more efficient than a VALUES (dimname) statement.


The VALUES function is very similar to the CHARLIST function. VALUES(MONTH) returns the same list as CHARLIST(MONTH).

The main differences are:

Special Considerations for an Ampersand (&)

Under certain circumstances, an ampersand (&) that is intended to be a character in a dimension value name is interpreted as ampersand substitution. When this happens, Oracle OLAP generates an error message.

This happens because Oracle OLAP recognizes special characters in dimension value names with when they are used in tuples in text expressions. For example, you can include spaces, such as naming a dimension value New York instead of NewYork. When you have dimension values that include ampersands in their names, refer to Example 8-163, "Workaround for Dimension Value Names Including an Ampersand".


Example 8-160 Listing the Values of a Valueset

The easiest way to display the values of a valueset is simply by using the name of the valueset in a SHOW or a REPORT statement. You can also use VALUES to list the values in that valueset.

For example, suppose an analytic workspace contains a valueset called monthset that has the values Jan95, May95, and Dec95. The following statement displays the values.

SHOW VALUES(monthset)

Example 8-161 Listing Position Numbers of a Dimension

You can use VALUES to list the position numbers instead of the actual values in a dimension or valueset. In this example, because you are using the INTEGER keyword with a valueset instead of a dimension, the function returns the position numbers of the values in the month dimension as shown by the output returned by the following statement.


Therefore, the value Jan95 is shown as the 61st value in the month dimension, May95 as the 65th value, and Dec95 as the 72nd value, although they are the first, second, and third values in monthset.

Example 8-162 VALUES with Text Variables

This example shows how to assign a dimension name to a text variable and use the text variable in the VALUES function instead of the variable name itself. As the following statements illustrate, when the variable textvar has the value district, VALUES(textvar) returns a list of district values.

textvar = 'district'
SHOW VALUES(textvar)

To list the values of district using the CHARLIST function rather than VALUES, you must use an ampersand.


Because ampersands in a program can degrade performance, use VALUES rather than CHARLIST in such cases.

Example 8-163 Workaround for Dimension Value Names Including an Ampersand

When a dimension value name contains an ampersand (&) as one of its characters, and when that dimension is a base dimension of a conjoint dimension, then a text expression that contains the names of dimension values in a tuple can generate an error in certain circumstances. This example shows how to avoid this error.

Suppose you use the following statements to define two dimensions.


Next, you use the following statements to define two conjoint dimensions.

DEFINE conj1 DIMENSION <prod geog>
DEFINE conj2 DIMENSION <prod geog>

The following statements add dimension values to the prod and geog dimensions.

MAINTAIN prod ADD 'prod1' 'prod&val2'
MAINTAIN geog ADD 'geog1' 'geog&val2'

The following statements add tuples (combinations of dimension values) to the CONJ1 conjoint dimension.

MAINTAIN conj1 ADD <'prod1' 'geog1'>
MAINTAIN conj1 ADD <'prod&val2' 'geog1'>

Now, suppose you want to use the VALUE function with a MAINTAIN statement to add those same tuples to the conj2 conjoint dimension. When you attempt to use the following statement, it generates an error message.

ERROR: (MXMSERR) val2 does not exist in any attached workspace.

This error occurs because the ampersand in the dimension value name prod&val2 is interpreted as an attempt at ampersand substitution.

Instead of using the preceding MAINTAIN statement, you can use the following statement to add the tuples to the CONJ2 conjoint dimension.

MAINTAIN conj2 MERGE < KEY(conj1 prod) KEY(conj1 geog) >