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

COLVAL

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

Syntax

COLVAL(n)

Arguments

n

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). 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.

Notes


Absolute Column Numbers

In figuring an absolute column number, you must count all columns shown in the report. For example, this means that 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.


TEXT or ID Data

When the selected column (n) contains only a TEXT or ID value, COLVAL returns NA.


Error Conditions

An error occurs when you specify the current column, a column to the right of the current column, or a nonexistent column.

Examples

Example 9-7 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