INSTAT

The INSTAT function checks whether a dimension or dimension surrogate value is in the current status list or whether a dimension value is in a valueset.

Return Value

BOOLEAN

YES if the value is in the current status list or in a valueset and NO if it is not.

Syntax

INSTAT(dimension, value)

Arguments

dimension: The name of the dimension, dimension surrogate, or valueset.
value: The dimension or dimension surrogate value you want to test, either a text literal (enclosed in single quotes) or an expression that specifies the value. To specify the value of a conjoint dimension or a concat dimension, enclose the value in angle brackets. For a conjoint dimension, separate the base dimension values with a comma and space. For a concat dimension, separate the base dimension and its value with a colon and a space.

Notes

Checking an Invalid Value

When you specify a dimension name and value in an INSTAT statement, Oracle OLAP tells you whether that value is in the current status list for that dimension. Conversely, the ISVALUE function tells you whether an item is a value of a dimension, regardless of whether it is in the status. INSTAT produces an error when value is not a dimension value, but ISVALUE simply returns a value of FALSE.

Examples

Example 7-123 Using INSTAT With a Valueset

Assume that within your analytic workspace you have a geog dimension with the following definition and values.

DEFINE geog TEXT DIMENSION
GEOG
--------------
Austria
Belgium
Canada

Assume, also, that you define a mygeogs valueset and identify a value for that valueset using the following statements.

DEFINE mygeogs VALUESET geog
LIMIT mygeogs TO 'Belgium'

You cannot issue a REPORT on a valueset. If you attempt to, Oracle OLAP issues an error message.

REPORT mygeogs
ORA-34104: INSTATTEST!MYGEOGS is not a type of object that contains data values.

However, you can use the INSTAT function to display which values of a dimension are in a valueset.

REPORT INSTAT (mygeogs, geog)

GEOG            INSTAT (MYGEOGS, GEOG)
-------------- ------------------------
Austria                              no
Belgium                             yes
Canada                               no

Example 7-124 Checking Current Status

In the following example, a program accepts a value of the month dimension as an argument. The first lines of the program use INSTAT to check whether the dimension value that was passed as an argument is in the current status for month. When it is, the program calls a report program. When it is not, the program branches to its error-handling section.

ARGUMENT onemonth month
 
IF INSTAT(month onemonth)
   THEN sales_report
   ELSE GOTO error
...

Example 7-125 Using INSTAT When the Dimension is a Conjoint Dimension

When the dimension that you specify is a conjoint dimension, then the entire value must be enclosed in single quotes. For example, suppose the analytic workspace already has a region dimension and a product dimension. The region dimension values include East, Central, and West. The product dimension values include Tents, Canoes, and Racquets.

The following statements define a conjoint dimension, and add values to it.

DEFINE reg.prod DIMENSION <geography product>
MAINTAIN reg.prod ADD <'East', 'Tents'> <'West', 'Canoes'>

To specify base positions, use a statement such as the following.

SHOW INSTAT(reg.prod '<1, 1>')
YES

To specify base text values, use a statement such as the following.

SHOW INSTAT(reg.prod '<\'East\', \'Tents\'>')
YES

Example 7-126 Using INSTAT When the Dimension is a Concat Dimension

When the dimension that you specify is a concat dimension, then you must enclose the entire <component dimension: dimension value> pair in single quotes. The following statement defines a concat dimension that has as its base dimensions region and product.

DEFINE reg.prod.ccdim DIMENSION CONCAT(region product)

A report of reg.prod.ccdim returns the following.

REG.PROD.CCDIM
----------------------
<region: East>
<region: Central>
<region: West>
<product: Tents>
<product: Canoes>
<product: Racquets>

To specify a base dimension position, use a statement such as the following.

SHOW INSTAT(reg.prod.ccdim '<product: 3>')
yes

To specify base dimension text values, use a statement such as the following.

SHOW INSTAT(reg.prod.ccdim '<product: Tents>')
YES