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

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

Go to previous page
Go to next page
View PDF


The COLVAL function returns a numeric value from a column to the left of the current column in the same row of a report. COLVAL can only be used in the ROW command and the REPORT command.

Return Value

DECIMAL when the selected column contains numeric or Boolean data; NA when the column (n) contains only a TEXT or ID value; or an error when the specified column is the current column, a column to the right of the current column, or a nonexistent column





The number of the column in the current row whose value you want; n can be any INTEGER expression.

Use a positive number to identify an absolute column number (counting left to right from the left margin of the report). In figuring an absolute column number, you must count all columns shown in the report. For example, when you are using a REPORT command that produces a column of labels down the left side of the report, you count this column of labels as column 1.For example, COLVAL(2) identifies the second column from the left margin of the report.

Use a negative number to identify a relative column number (counting right to left from the current column). For example, COLVAL(-2) identifies the column that is two columns to the left of the current column.


Example 7-46 Performing Column Calculations in a Report

Suppose in a report you want to show actual sales and planned sales, along with the difference between the two. You can use the COLVAL function to calculate this difference.

LIMIT month TO 'Jun96'
LIMIT district TO 'Boston'
FOR product
   ROW product sales sales.plan COLVAL(2)-COLVAL(3)

These statements produce the following output.

Tents          95,120.83  80,138.18  14,982.65
Canoes        157,762.08 132,931.39  24,830.69
Racquets       97,174.44  84,758.46  12,415.98
Sportswear     79,630.20  73,568.52   6,061.68
Footwear      153,688.02 109,219.15  44,468.87