Skip Headers

Oracle® OLAP DML Reference
10g Release 1 (10.1)

Part Number B10339-02
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
Feedback

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

PUSHLEVEL

The PUSHLEVEL command marks the start of a series of PUSH commands. You can then use a corresponding POPLEVEL command to restore all the values saved by PUSHcommands that are executed after PUSHLEVEL. POPLEVEL must specify the same marker as the PUSHLEVEL command that starts the series. You can use PUSHLEVEL only within programs.

Syntax

PUSHLEVEL marker-expression

Arguments

marker-expression

A text value to mark the start of a series of PUSH commands all of whose saved values are to be popped at once. A POPLEVEL command that specifies the exact same marker-expression restores the whole series of saved values.

Notes


Nesting

You can nest PUSHLEVEL/POPLEVEL pairs, as long as you specify a different marker for each pair, as illustrated in the following code.

PUSHLEVEL 'firstlevel'
PUSH PAGESIZE DECIMALS       < saves values in FIRSTLEVEL
...
PUSHLEVEL 'secondlevel'
PUSH month product           < Saves values in SECONDLEVEL
...
POPLEVEL 'secondlevel'       < Restores values in SECONDLEVEL
...
POPLEVEL 'firstlevel'        < Restores values in FIRSTLEVEL

You do not normally need more than one level in a single program. However, Oracle OLAP automatically creates nested levels when one program calls another program and each program contains a set of PUSHLEVEL and POPLEVEL commands.


Duplicate Markers

When you specify the same marker for two or more PUSHLEVEL commands, a POPLEVEL command specifying that same marker will restore values that were saved only since the most recent PUSHLEVEL command.


Multiple PUSHLEVEL Commands

When you specify a different marker for two or more PUSHLEVEL commands, a POPLEVEL command that specifies the marker of any PUSHLEVEL command restores all the values that were saved since that command, including values that were saved after later PUSHLEVEL commands.


Related Statements

POP PUSH, POPLEVEL, and CONVERT.

Examples

Example 19-45 Creating Level Markers

You can use the PUSHLEVEL command to establish a level marker called firstlevel, and then use PUSH to save the current values.

PUSHLEVEL 'firstlevel'
PUSH month DECIMALS ZSPELL

The level marker can be any text that is enclosed in single quotation marks. It can also be the name of a single-cell ID or TEXT variable, whose value becomes the name of the level marker. In the exit sections of the program, you can then use the POPLEVEL command to restore all the values you saved since establishing the firstlevel marker.

POPLEVEL 'firstlevel'

Example 19-46 Nesting PUSHLEVEL and POPLEVEL Commands

You can nest PUSHLEVEL and POPLEVELcommands to save certain groups of values in one place in a program and other groups of values in another place in a program. The next example shows two sets of nested PUSHLEVEL and POPLEVEL commands.

PUSHLEVEL 'firstlevel'
PUSH PAGESIZE DECIMALS "Saves values in FIRSTLEVEL
        ...
PUSHLEVEL 'secondlevel'
PUSH month product     "Saves values in SECONDLEVEL
      ...
POPLEVEL 'secondlevel' "Restores values in SECONDLEVEL
       ...
POPLEVEL 'firstlevel'  "Restores values in FIRSTLEVEL

Normally, you do not use more than one set of PUSHLEVEL and POPLEVEL commands in a single program. However, the nesting feature comes into play automatically when one program calls another program, and each program contains a set of PUSHLEVEL and POPLEVEL commands.

Example 19-47 One-Step Restoration and Nested Levels

The following program uses PUSHLEVEL 'rpt1' to mark for one-step restoration the original value of DECIMALS and the original status of month, product, and district, even though these are pushed separately in the program.

To demonstrate nesting, the program includes a nested PUSHLEVEL-POPLEVEL pair with 'rpt2' as its marker and some STATUS commands at various points. You can compare the program's output with the program to see how the status is affected.

DEFINE sales.RPT PROGRAM
PROGRAM
STATUS month product district
 
PUSHLEVEL 'rpt1'
PUSH DECIMALS month
DECIMALS = 0
LIMIT month TO 'Jan96'
REPORT WIDTH 8 DOWN district WIDTH 9 ACROSS product: expense
PUSH product
LIMIT product TO 'Racquets' 'Sportswear'
REPORT DOWN district ACROSS product: advertising
 
PUSHLEVEL 'rpt2'
PUSH district
LIMIT district TO 'Atlanta' 'Dallas' 'Chicago'
REPORT DOWN district ACROSS product: sales
BLANK
STATUS month product district
BLANK
 
POPLEVEL 'rpt2'
STATUS month product district
BLANK
POPLEVEL 'rpt1'
 
STATUS month product district
END

The sales.rpt program produces the following output.

The current status of MONTH is:
ALL
The current status of PRODUCT is:
ALL
The current status of DISTRICT is:
ALL
MONTH: JAN96
         ---------------------EXPENSE---------------------
         ---------------------PRODUCT---------------------
DISTRICT   Tents    Canoes   Racquets  Sportswear  Footwear
-------- --------- --------- --------- ---------- ----------
Boston      31,299    67,527    52,942     49,668    80,565
Atlanta     41,139    53,186    57,159    108,047    99,758
Chicago     27,768    45,621    53,756     65,055    81,639
Dallas      47,063    34,072   118,807    113,629    19,785
Denver      33,177    42,975    89,144     63,380    36,960
Seattle     41,043    64,009    26,719     38,970    46,900
Month: Jan96
               -----ADVERTISING-----
               -------PRODUCT-------
DISTRICT        RAcquets  Sportswear
-------------- ---------- ----------
Boston              3,784      3,352
Atlanta             4,384      9,509
Chicago             3,351      5,283
Dallas              8,700      8,340
Denver              6,215      4,654
Seattle             2,344      3,726
MONTH: Jan96
               --------SALES--------
               -------PRODUCT-------
DISTRICT        Racquets  Sportswear
-------------- ---------- ----------
Atlanta            61,895    129,616
Dallas            125,880    128,115
Chicago            58,649     77,490
The current status of MONTH is:
JAN96
The current status of PRODUCT is:
RACQUETS, SPORTSWEAR
The current status of DISTRICT is:
ATLANTA, DALLAS, CHICAGO
 
The current status of MONTH is:
JAN96
The current status of PRODUCT is:
RACQUETS, SPORTSWEAR
The current status of DISTRICT is:
ALL
 
The current status of MONTH is:
ALL
The current status of PRODUCT is:
ALL
The current status of DISTRICT is:
ALL