FETCH

The FETCH command specifies how analytic workspace data is retrieved for use in the relational table created by the OLAP_TABLE function which you use to access analytic workspace data using SQL.

You can only use the FETCH command in the OLAP_command parameter of the OLAP_TABLE function; you cannot use it in any other context. For more information on the OLAP_TABLE function, see the Oracle OLAP Reference.

Within the OLAP_TABLE function, the FETCH keyword specifies explicitly how analytic workspace data is mapped to a table object. The FETCH keyword is provided for Express applications that are migrating to the Oracle Database.

Note:

Use the FETCH keyword in OLAP_TABLE only when you are upgrading an Express application that used the FETCH command for SNAPI. When you are upgrading an Express application, note that the syntax is the same here as in Express 6.3. You can use the same FETCH commands that you used previously.

When using FETCH as an argument in OLAP_TABLE, you must enter the entire statement on one line, without line breaks or continuation marks of any type.

To fetch or import data from an relational table into analytic workspace objects using SQL commands embedded in the OLAP DML, use the OLAP DML SQL command.

Syntax

FETCH expression... [TAG tag-exp] [LABELED] [data-order]

where:

data-order is one of the following:

     USING <order-dim...>
     ACROSS across-dim...
     DOWN down-dim...
     ACROSS across-dim... DOWN down-dim...

Arguments

expression...

One expression for each target column, in the same order they appear in the row definition. Separate expressions with spaces or commas.

TAG tag-exp

This keyword is ignored; it is retained in the syntax only for backward compatibility.

LABELED

This keyword is ignored; it is retained in the syntax only for backward compatibility. All fetches are labeled.

USING <order-dim...>

Orders the data block according to the dimension list specified in <order-dim...>. Specify dimensions or composites or a combination of the two within angle brackets. Dimensions are ordered from fastest to slowest varying, with the first dimension being the fastest varying. When you specify a USING clause, then you cannot specify ACROSS or DOWN.

ACROSS across-dim...

Orders the data block in columns and rows and specifies the column dimensions. For across-dim, specify a list of one or more dimensions, composites, the NONE keyword, or a combination of these. When you specify two or more ACROSS dimensions, then they vary from slowest to fastest, with the first dimension being the slowest.

When you specify ACROSS but not DOWN, then all unspecified dimensions default to DOWN dimensions, which vary from fastest to slowest in the order that the dimensions appear in the object definitions. However, adding the NONE keyword to the ACROSS dimension list fetches only the first value in status for the unspecified DOWN dimensions.

When you specify an ACROSS clause, then you cannot specify a USING clause.

DOWN down-dim...

Orders the data block in columns and rows and specifies the row dimensions. For down-dim, specify a list of one or more dimensions, composites, the NONE keyword, or a combination of these. When you specify two or more DOWN dimensions, then they vary from slowest to fastest, with the first dimension being the slowest.

When you specify DOWN but not ACROSS, then all unspecified dimensions default to ACROSS dimensions, which vary from fastest to slowest in the order that the dimensions appear in the object definitions. However, adding the NONE keyword to the DOWN dimension list fetches only the first value in status for the unspecified ACROSS dimensions.

When you specify a DOWN clause, you cannot specify a USING clause.

Notes

Default Data Order

When you do not specify a USING or DOWN/ACROSS clause, the dimensions of the data vary from fastest to slowest in the order they are listed in the workspace object definitions.

Using Expressions with Different Dimensionality

When you specify multiple expressions with different dimensionality in one FETCH command, the ordering of the dimensions from fastest to slowest varying is not predictable.

Maximum Size of Data Block

You can use MAXFETCH to set an upper limit on the size of a data block generated by FETCH.

Variables Defined with Composites

For variables defined with composites, you can specify the composites in place of the base dimensions in the ACROSS, DOWN, and USING clauses of FETCH. This minimizes the number of NA fields in the resulting data block. When a variable has been defined with a named composite, you can specify the name of the composite after the USING, DOWN or ACROSS keyword. You specify unnamed composites with the syntax used to define them. For example, a variable d.sales with the following definition

DEFINE d.sales VARIABLE DECIMAL <month SPARSE<product district>>

could be fetched with the expression SPARSE<product district> immediately following a USING, DOWN, or ACROSS keyword.

Performance Tip for Variables Dimensioned by Composites

By default, when FETCH explicitly loops over a composite, it 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 FETCH output is produced, you can set the SORTCOMPOSITE option to NO.

Examples

For an example of using FETCH in OLAP_TABLE, see the Oracle OLAP Reference.