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.
BOOLEAN
YES if the value is in the current status list or in a valueset and NO if it is not.
The name of the dimension, dimension surrogate, or valueset.
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.
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.
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 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