|Oracle® OLAP DML Reference
11g Release 2 (11.2)
Part Number E17122-05
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.
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.
See also:For more information on the
OLAP_TABLEfunction, see Appendix A, "OLAP_TABLE SQL Functions"
FETCH expression... [TAG tag-exp] [LABELED] [data-order]
where data-order is one of the following:
One expression for each target column, in the same order they appear in the row definition. Separate expressions with spaces or commas.
This keyword is ignored; it is retained in the syntax only for backward compatibility.
This keyword is ignored; it is retained in the syntax only for backward compatibility. All fetches are labeled.
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.
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.
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.
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.
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.
For an example of using FETCH in
OLAP_TABLE, see Example A-9, "Script Using FETCH with OLAP_TABLE".