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.

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

See also:

For more information on the OLAP_TABLE function, see Appendix A, "OLAP_TABLE SQL Functions"

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

Parameters

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.

Usage 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 instead of the base dimensions in the ACROSS, DOWN, and USING clauses of FETCH which 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.

Examples

For an example of using FETCH in OLAP_TABLE, see Example A-9, "Script Using FETCH with OLAP_TABLE".