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

Part Number E17122-05
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

ROW command

The ROW command produces a single line of data in cells, one after another in a single row. A series of ROW commands that produce corresponding cells are often used to build up columns of data. For this reason, we normally speak of the ROW command as producing a line of columns. Output from the ROW command is sent to the current outfile.

The ROW command is typically used with other commands, functions, and options that you can think of collectively as report-writing statements

The ROW command itself consists of a series of column descriptions that specify the data to be produced and, optionally, the output format of the data.

In addition, ROW has a versatile capability for doing row and column arithmetic. It can perform calculations and include the calculation results in the output. It can use any kind of calculated expression in the column descriptions; and it can take advantage of row and column totaling functions (see Table 10-6, "Row and Column Arithmetic").

ROW is primarily used in report programs to produce the lines of the report. The maximum width of any row in a report is 4000 characters.

Tip:

When you know ahead of time that you do not need the subtotaling capability of the ROW command, using a HEADING statement instead of ROW to produce the lines of your report can provide a time savings, since, in this case, Oracle OLAP does not keep track of subtotals.

See also::

ROW function

Syntax

ROW [attributes] [ACROSS dimension [limit-clause]:] {exp1|SKIP } -

     [[attributes] [ACROSS dimension [limit-clause]:] {expn|SKIP }]

Parameters

ROW with no arguments produces a blank line.

attributes

One or more attributes for a column. Attributes are format specifications that determine how the data value is formatted within the column. There is no limit to the number of attributes that you can use to describe a column format. (See Table 10-5, "Column Attributes for ROW" for an explanation of each of the available attributes.) The default for some format attributes is determined by the current setting of Oracle OLAP options (see Table 10-7, "Report-Related Options" for a list of these options).

ACROSS

An ACROSS phrase lets you include multiple values of a dimensioned expression in a single row by looping over one dimension (or composite) of the expression. Typically, ROW shows only the value that corresponds to the first dimension value within the current limits. With an ACROSS phrase, ROW produces one data column for each dimension value currently in the status.

You can apply a single ACROSS phrase to multiple data expressions, or you can use separate ACROSS phrases for different data expressions. See "Multiple Expressions" and "Separate ACROSS Phrases".

dimension

The name of a dimension or composite over which the statement loops.

When you show data for a variable dimensioned by a composite and you do not include an ACROSS phrase, ROW shows output for all data cells that correspond to the base dimension values of the composite. When a particular combination of base dimension values does not exist in the composite, ROW shows NA for the corresponding data cell. Likewise, when you specify one composite's base dimension in an ACROSS phrase, ROW shows NA for a data cell for which the composite contains no value. However, when you specify a composite in the ACROSS phrase, ROW shows output only for data cells for which combinations of base dimension values exist in the composite which provides a more concise report that better reflects your data.

When the dimension specified in an ACROSS phrase has null status, ROW does not produce any data columns for that ACROSS phrase.

limit-clause

When you specify a dimension in the ACROSS phrase, a clause that enables you to temporarily change the status of that dimension during the execution of the ROW statement.

The syntax of limit-clause is the same syntax as any of the limit-clause arguments in the various forms of the LIMIT command (that is, the syntax of the LIMIT command after the limit-type argument such as "TO"). For the syntax of these arguments, see LIMIT (using values) command, LIMIT using LEVELREL command, LIMIT (using parent relation), LIMIT (using related dimension) command, LIMIT NOCONVERT command, and LIMIT command (using POSLIST).

The following example temporarily limits month to the last six values, no matter what the current status of month is.

ACROSS month LAST 6: units

When the limits you specify result in empty status for the dimension, an error occurs. However, when you include the phrase IFNONE label, the error is suppressed and execution of your program branches to the specified label where you can handle the error.

Note:

When you specify a composite in the ACROSS phrase, you cannot include a limit-clause argument. You must limit the base dimensions of a composite to the desired values using a LIMIT command before you execute a ROW statement
SKIP

Used instead of an expression to indicate that the column is to be left blank.

Table 10-5 Column Attributes for ROW

Attribute Meaning

WIDTH n

(W n)

Makes the column n spaces wide. The default width for the first column is the value of the LCOLWIDTH option. For other columns, it is the value of the COLWIDTH option. The maximum width is 4000 characters. Columns with a width of 0 are suppressed.

SPACE n

(SP n)

Precedes the column with n spaces. The default for the first column is 0; for other columns, 1.

INDENT n

Indents the value n spaces within its column. The default is 0.

LEFT

(L)

Left-justifies the value within its column. (The default for TEXT data.)

RIGHT

(R)

Right-justifies the value within its column. (The default for numeric and Boolean data.)

CENTER

(C)

Centers the value within its column.

LSET 'text'

Adds text to the left of the value. When used with an expression that contains NA values, Oracle OLAP does not include the text you specify at the left of any NA values.

NOLSET

Does not add anything to the left of the value.

RSET 'text'

Adds text to the right of the value. When used with an expression that contains NA values, Oracle OLAP does not include the text you specify at the right of any NA values

NORSET

Does not add anything to the right of the value.

FILL 'char'

Puts char into unused positions in the column. The default fill character is a space.

DECIMAL n

(D n)

Shows n decimal places. Decimal places are separated by the character currently specified by the DECIMALCHAR option. The default number of decimal places is controlled by the DECIMALS option.

NODECIMAL

Shows the number of decimal places indicated by the DECIMALS option.

COMMA

Marks thousands and millions with commas or the character currently recorded in the THOUSANDSCHAR option. The default is controlled by the COMMAS option.

NOCOMMA

Does not mark thousands and millions.

PAREN

Uses parentheses to indicate negative numbers. The default is controlled by the PARENS option.

NOPAREN

Uses the minus sign to indicate negative numbers. The default is controlled by the PARENS option.

LEADINGZERO

Puts a leading zero before decimal numbers between -1 and 1.

NOLEADINGZERO

Suppresses leading zeros before decimal numbers between -1 and 1. (Note that when you set the DECIMAL attribute to 0 and you use the NOLEADINGZERO keyword, Oracle OLAP does not show any decimal values between -1 and 1 that are rounded to 0.)

CNLEADINGZERO

Puts a leading zero before decimal numbers between -1 and 1 when it does not cut off any significant digits.

MNOTATION

Always uses M-notation (divides values by one million and appends M).

CMNOTATION

Conditionally uses M-notation, when needed to make a value fit in a column.

NOMNOTATION

Does not use M-notation (uses asterisks for oversize values).

MDECIMAL n

Shows n decimal places in numbers formatted with M-notation; n can be any number from 0 to 16, or 255.

ENOTATION

Always uses scientific notation, also called exponential notation or E-notation (appends E, and includes a sign before the exponent, for example, .230E+2 or .230E-2).

CENOTATION

Conditionally uses E-notation, when needed to make a value fit in a column.

NOENOTATION

Does not use E-notation (defaults to conditional M-notation).

EDECIMAL n

Shows n decimal places in numbers formatted with E-notation; n can be any number from 0 to 16, or 255.

NASPELL 'text'

Uses text instead of NA values. The default is controlled by the NASPELL option.

NONASPELL

Spells NA values as indicated by the NASPELL option.

ZSPELL 'text'

Uses text instead of zero numeric values. The default is controlled by the ZSPELL option.

NOZSPELL

Spells zero values as indicated by the ZSPELL option.

YESSPELL 'text'

Text used for TRUE Boolean values. The default is recorded in the YESSPELL option.

NOSPELL 'text'

Text used for FALSE Boolean values. The default is recorded in the NOSPELL option.

TRUNCATE

(TRUNC)

Truncates a character value to the column width when it does not fit in the column.

NOTRUNCATE

(NOTRUNC)

Creates additional lines when the character value does not fit in the column.

FOLDUP

For a multiline character value, places all but the last line above the rest of the row, and the last line on the row with the other values; also strips any leading or trailing spaces.

FOLDDOWN

For a multiline character value, places the first line on the row with the other values, and places additional lines below the rest of the row; also strips any leading or trailing spaces.

VALONLY

Underlines or overlines the value only. (Used with UNDER and OVER.)

NOVALONLY

Underlines or overlines the entire width of the column. (Used with UNDER and OVER.)

UNDER textexp

Underlines the value or column with the value of a character expression (textexp). When textexp is a literal value, it must be enclosed in single quotes. Useful literal values include: '-' to underline value or column, '=' to double underline value or column, and '' to indicate that a value or column is not underlined.

To underline only when a condition is met, for textexp use

IF boolean-expression THEN '-' ELSE ''

OVER textexp

Overlines the value or column with the value of a character expression (textexp). When textexp is a literal value, it must be enclosed in single quotes. Useful literal values include: '-' to overline value or column, '=' to double overline value or column, and '' to indicate that a value or column is does not have an overline

To overline only when a condition is met, for textexp use

IF boolean-expression THEN '-' ELSE ''


Use the functions that are listed in Table 10-6, "Row and Column Arithmetic" to perform calculations on the values generated so far in a report.

Table 10-6 Row and Column Arithmetic

Function Data Type Value Returned

COLVAL(n)

DECIMAL

Value in the nth column of the current row. When 0, an absolute column number (from the left margin, moving to the right). When n < 0, a relative column number (from the current column, moving left).

 

RUNTOTAL(n)

where:

n = 1,2, ...32

DECIMAL

Total of all numbers generated in the current column since the last SUBTOTAL or ZEROTOTAL for n. Does not reset total for n to 0.

SUBTOTAL(n)

where:

n = 1,2, ...32

DECIMAL

Total of all numbers generated in the current column since the last SUBTOTAL or ZEROTOTAL for n. Resets total for n to 0.

 


The options that are listed in Table 10-7, "Report-Related Options" affect the default format for a ROW command.

Table 10-7 Report-Related Options

Option Meaning

COLWIDTH

Column width for all but the first column when the WIDTH attribute is not used. The default is 10.

COMMAS

Specifies whether a thousands group separator is used when neither the COMMA attribute nor the NOCOMMA attribute is used. The default is YES (uses a separator).

DECIMALS

Number of decimal places when the DECIMAL attribute is not used. The default is 2.

LCOLWIDTH

Column width for the first column when the WIDTH attribute is not used. The default is 14.

LSIZE

Defines the line size within which the STDHDR program centers the standard header. The default is 80 characters.

NASPELL

Text used for NA values when the NASPELL attribute is not used. The default text is NA.

NLS_LANGUAGE

Specifies the text used for TRUE and FALSE Boolean values. These values are reflected in the YESSPELL and NOSPELL options.

NLS_TERRITORY

Specifies the character used for the decimal marker and the thousands group separator. These values are reflected in the DECIMALCHAR and THOUSANDSCHAR options.

PARENS

Parentheses usage for negative numbers when neither the PAREN attribute nor the NOPAREN attribute is used. The default is NO (does not use parentheses; uses a minus sign).

ZEROROW

Controls generation or suppression of rows in which all numeric values are zero. The default is NO (generates zero rows).

ZSPELL

Text used for zero values when the ZSPELL attribute is not used. The default text is OFF, which shows a zero (0). 


Use the statements that are listed in Table 10-8, "OLAP DML Statements That Are Compatible with the ROW Command" with the ROW command.

Table 10-8 OLAP DML Statements That Are Compatible with the ROW Command

Command Action

BLANK n

Produces n blank lines. The default is one line.

HEADING column-

description(s)

Produces titles and column headings for a report. Numeric values in headings are not added to column totals.

PAGE

Forces a page break in output when PAGING is set to YES.

ZEROTOTAL

Resets all 32 totals to 0 for all columns.

ZEROTOTAL ALL col(s)

Resets all 32 totals to 0 for the specified columns, or for all columns when there are no column arguments.

ZEROTOTAL n col(s)

Resets the indicated total (n) to 0 for the specified columns, or for all columns when there are no column arguments. 


Usage Notes

Report-Writing Commands

The ROW command and its associated options and commands are referred to collectively as report-writing statements. Table 10-6, "Row and Column Arithmetic" lists functions you can use for performing row and column arithmetic in reports. Table 10-7, "Report-Related Options" lists report-related options that determine the default format for ROW output. Table 10-8, "OLAP DML Statements That Are Compatible with the ROW Command" lists additional statements that are used in combination with ROW to create reports.You can use the PAGING option and associated paging-related options to produce your report program in a page-oriented format.

Labels for Composites and Conjoint Dimensions

When you produce a report of data that has a composite or a conjoint dimension in its dimension list, you can produce a label column for each base dimension by using the KEY function. You can also provide a separate WIDTH attribute for each label column. For example, when proddist is a composite with the base dimensions product and district, you can use statements similar to the following ones.

FOR proddist
   ROW W 12 KEY(proddist district) W 8 KEY(proddist product) ...

Multiple Expressions

When you want the same format attribute or ACROSS phrase to apply to multiple data expressions, you can enclose the expressions in angle brackets (< >) and place the common attributes or ACROSS phrase immediately before the bracketed expressions.

attributes <expression1expression2, ...> 

or

ACROSS dimension: <expression1expression2, ...>

When you have attributes that apply to only one expression within the brackets, place the specific attributes immediately before the expression.

attributes1 <expression1attributes2 expression2>

When an attribute inside angle brackets (specific to a column) conflicts with an attribute outside the brackets (common to several columns), the specific attribute overrides the common attribute.

You can nest brackets to any depth, if you have an equal number of right and left brackets.

Separate ACROSS Phrases

For data generated with an ACROSS phrase, you can produce all the columns for one expression and then all the columns for additional expressions by using separate ACROSS phrases.

ACROSS dimexpression1, ACROSS dimexpression2

You also can nest ACROSS phrases to show data columns for two or more dimensions of an expression across a row.

ACROSS dim1: ACROSS dim2expression

Row and Column Arithmetic

See Table 10-6, "Row and Column Arithmetic" for a list of the functions available for row and column arithmetic. You can use these functions to perform calculations on the values already generated in a report. Oracle OLAP maintains 32 running totals for each column, so you can include up to 32 levels of subtotals in a report. Note that when a numeric value is too large to fit into a data cell, ROW rounds it off to the nearest million with the symbol M at the right side of the cell. When a value is still too large, ROW replaces the value with asterisks.

Performance Tip for Using ROW with Variables Dimensioned by Composites

By default, when ROW explicitly loops over a composite, or when ROW is executed in a FOR loop that explicitly loops over a composite, Oracle OLAP sorts the composite values according to the current order of the values in the composite's base dimensions. The task of sorting requires some processing time, so when variables are large, performance can be affected. When your variable is very large, and you are more concerned about performance than about the order in which ROW output is produced, you can set the SORTCOMPOSITE option to NO.

Examples

Example 10-109 Labeling Data Values

In this example, ROW produces a line of output that contains a value of sales, along with the corresponding dimension values for district, month, and product that identify it.

ROW W 8 district month product sales

The preceding statement produces the following row of output.

Boston        Jan95      Tents  32,153.52

Example 10-110 Reporting Two Variables

The line of output produced by this ROW statement contains the current dimension value of district, followed by the values of sales and sales.plan for Sportswear for each of the first two months of 1996. 

LIMIT month TO 'Jan96' 'Feb96'
LIMIT product TO 'Sportswear'
ROW W 8 district ACROSS month: <sales sales.plan>

These statements produce the following row of output.

Boston    57,079.10  61,434.20  63,121.50  64,006.91

Example 10-111 Formatting and Labeling the Output

In this ROW statement, you want to see the actual and planned sales of tents for June 1996. You want to limit the status of month only for this one ROW statement, so you include the value Jun96 in the ACROSS phrase. You format the values as whole dollar amounts, and you also add a dollar sign to the values, along with individual labels that identify the actual and planned figures.

LIMIT product TO 'Tents'
ROW WIDTH 15 name.product ACROSS month 'Jun96': -
   DECIMAL 0 LSET '$' W 18 -
   <RSET ' (actual)' sales -
   RSET ' (plan)' sales.plan>

These statements produce the following row of output.

3-Person Tents    $95,121 (actual)     $80,138 (plan)

Example 10-112 Reporting on a Variable Dimensioned by a Composite

In this example, D.SALES is a variable whose dimension list includes the dimension month and the unnamed composite SPARSE <product district>. By specifying the composite in an ACROSS phrase of a ROW statement, you can produce a report that includes only the data cells for which the composite contains values.

LIMIT product TO ALL
LIMIT district TO 'Atlanta'
LIMIT month TO 'Jan96'
ROW ACROSS SPARSE <product district>: d.sales