Skip Headers
Oracle® OLAP DML Reference
11g Release 1 (11.1)

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

Go to previous page
Previous
Go to next page
Next
View PDF

STATVAL

The STATVAL function returns the dimension value that corresponds to a specified position in the current status list of a dimension or a dimension surrogate, or in a valueset.

Return Value

The data type returned by STATVAL is either the data type of the dimension or dimension surrogate value or an INTEGER that indicates its position in the default status list of the dimension. The dimension value that STATVAL returns is converted to a number or a text value, as appropriate to the context. To ensure that STATVAL returns an INTEGER value, specify the INTEGER keyword. See Example 8-120, "Ensuring that STATVAL Returns an INTEGER".

Syntax

STATVAL(dimension position [INTEGER])

Arguments

dimension

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

position

An INTEGER expression that specifies the position in the current status list of a dimension or a valueset. When you specify a position that has no values, STATVAL returns NA.

INTEGER

Specifies that STATVAL must return an INTEGER that represents the position of the dimension value in the default status list.

Notes

STATVAL in a FOR Loop

In a FOR loop over a dimension, the status is limited to a single dimension value for each iteration of the loop. Therefore, STATVAL has a value only for position 1. For other positions, STATVAL returns NA.

Examples

Example 8-119 STAVAL with Qualified Data References

Suppose you want to know the sales figures for the month ranked fifth among the 10 months with the highest total sales. After limiting month to the TOP 10, use STATVAL in a qualified data reference to produce sales figures for the month ranked fifth.

LIMIT month TO TOP 10 BASEDON TOTAL(sales, month)
REPORT month

These statements produce the following report.

MONTH
--------------
Jul96
Jun96
Jul95
Aug96
Jun95
Sep96
May96
Aug95
Sep95
MAY95

Using STATVAL in the following REPORT statement produces a different report.

REPORT W 8 DOWN district HEADING -
   JOINCHARS('Sales: 5th of Top Ten - ' STATVAL(month 5)) -
   sales(month STATVAL(month 5))

This is the report produced by the preceding statement.

------------Sales: 5th of Top Ten - Jun95-------------
         -----------------------PRODUCT------------------------
DISTRICT   Tents      Canoes    Racquets  Sportswear  Footwear
-------- ---------- ---------- ---------- ---------- ----------
Boston    88,996.35 147,412.44  90,840.60  75,206.30 144,162.66
Atlanta  110,765.24 106,327.17 109,695.31 155,652.78 146,364.99
Chicago   70,908.96 108,039.05 100,030.29 104,900.66 148,386.81
Dallas   128,692.56  71,899.23 176,452.58 164,823.10  32,421.25
Denver    91,717.46  99,099.20 140,961.37  99,951.60  70,149.77
Seattle  113,806.48 143,037.62  54,926.87  57,739.03  75,457.04

Notice that the qualified data reference in the following statement means "sales for the fifth month in the default status of month."

sales(month 5)

While the qualified data reference in the following statement means "sales for the fifth month in the current status of month."

sales(month STATVAL(month 5))

The following statements show the different values that are returned for a qualified data reference of month and for STATVAL with month as an argument.

SHOW month(month 5)
SHOW STATVAL(month 5)

The preceding statements produce the following output.

May95
Jun95 

Example 8-120 Ensuring that STATVAL Returns an INTEGER

Depending on the context, STATVAL may return an INTEGER value without your specifying the INTEGER keyword.

The following statements

LIMIT month TO 'Jun95' TO 'Dec95'
SHOW STATVAL(month 3)

produce this output.

Aug95

With the INTEGER keyword,

SHOW STATVAL(month 3 INTEGER)

the following output is produced.

8