VALUES

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.

Return Value

TEXT

Syntax

VALUES(dimension [keyword] [INTEGER])

Arguments

dimension

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

keyword

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.

INTEGER

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.

Notes

Comparing VALUES to CHARLIST

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

The main differences are:

For dimensions, the NOSTATUS keyword of VALUES lets you use the default status without first limiting the dimension values to ALL.
The VALUES function lets you use a text expression to specify the dimension or valueset name. See Example 24-31, "VALUES with Text Variables".

Do Not Use with Composites

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.

Special Considerations for an Ampersand (&)

Under certain circumstances, an ampersand (&) that is intended to be a character in a dimension value name will be 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 24-32, "Workaround for Dimension Value Names Including an Ampersand".

Examples

Example 24-29 Listing the Values of a Valueset

Suppose an analytic workspace contains a valueset called monthset that has the values Jan95, May95, and Dec95. You can use VALUES to list the values in that valueset.

The following statement

SHOW VALUES(monthset)

produces this output.

Jan95
May95
Dec95

Example 24-30 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.

The following statement

SHOW VALUES(monthset INTEGER)

produces this output.

61
65
72

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 24-31 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. When the variable textvar has the value district, VALUES(textvar) returns a list of district values.

The following statements

textvar = 'district'
SHOW VALUES(textvar)

produce this output.

Boston
Atlanta
Chicago
Dallas
Denver
Seattle

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

SHOW CHARLIST(&textvar)

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

Example 24-32 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.

DEFINE prod DIMENSION TEXT
DEFINE geog DIMENSION TEXT

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 the MAINTAIN command to add those same tuples to the conj2 conjoint dimension. When you attempt to use the following statement, it will generate an error message.

MAINTAIN conj2 ADD VALUES(conj1)
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 command, you can use the following statement to add the tuples to the CONJ2 conjoint dimension.

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