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

E17122-08
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
PDF · Mobi · ePub

TEMPSTAT

The TEMPSTAT command limits the dimension you are looping over, inside a FOR loop or inside a loop that is generated by a REPORT statement. Status is restored after the statement following TEMPSTAT. When a DO ... DOEND phrase follows TEMPSTAT, status is restored when the matched DOEND or a BREAK or GOTO statement is encountered. You can use TEMPSTAT only within programs.

Syntax

TEMPSTAT dimension...

     statement block

Parameters

dimension(s)

One or more dimensions whose status you would like to temporarily change inside a FOR loop or an automatic loop that is generated by the REPORT statement.

statement block

One or more statements that change the status of the dimension. To execute multiple statements under the temporary status, enclose them between DO ... DOEND brackets.

Usage Notes

Nesting TEMPSTATE Statements

You can nest TEMPSTAT commands, one within another, and you can repeat the same dimension within the nested TEMPSTAT commands.

Placement of TEMPSTAT

When you want to be able to change the status of a dimension while REPORT is looping over it, you must place a TEMPSTAT statement inside that REPORT loop rather than before the REPORT statement. For example, suppose you have written a user-defined function called monthly_sales, which changes the status of month, and monthly_sales is part of a REPORT statement that is looping over month. In this case a TEMPSTAT statement must be inside the monthly_sales function in order for a status change to take place. (TEMPSTATE must be inside of monthly_sales even when the REPORT statement is given within TEMPSTAT DO/DOEND brackets within a FOR loop over MONTH.)

POP and POPLEVEL Commands

Within the DO/DOEND brackets of a TEMPSTAT statement block, you cannot use a POP statement to pop a dimension that is protected by TEMPSTAT on the block -- unless the matching PUSH statement is also within the block.

Similarly, within the DO/DOEND brackets of a TEMPSTAT statement block, you cannot use a POPLEVEL statement to pop a dimension that is protected by TEMPSTAT on the block -- unless one of two conditions is met: the matching PUSHLEVEL statement is also within the block, or the only pushes on the dimension since the PUSHLEVEL statement was given are also within the block.

Use Only LIMIT and CONTEXT Commands

Within the DO/DOEND brackets of a TEMPSTAT statement, the only way to change the status of a dimension within a loop over that dimension is with the LIMIT or CONTEXT APPLY commands. (See the LIMIT command and the CONTEXT command for details.) You cannot change the status of the dimension using POP or POPLEVEL. You also cannot perform any operations that would add values to the dimension, because adding values also changes the status of the dimension to ALL. For example, MAINTAIN ADD, FILEREAD APPEND, and IMPORT (with new values in the EIF file) add values to a dimension.

Examples

TEMPSTAT in a FOR Loop

The following program excerpt uses a TEMPSTAT statement to limit the market dimension within the FOR market loop.

FOR market
DO
 TEMPSTAT market
  DO
   LIMIT market TO CHILDREN USING market.market
   REPORT market
  DOEND
DOEND