![]() Previous |
![]() Next |
The SQL FETCH command retrieves and processes data specified by a named SQL cursor. SQL FETCH assigns the retrieved data to OLAP objects. You use the SQL FETCH command in combination with other SQL commands to copy data from relational tables into analytic workspace objects as outlined in "Copying Relational Data into Analytic Workspace Objects".
Syntax
SQL FETCH cursor [LOOP [loopcount]] -
INTO :targets... [THEN action-statements...]
where:
targets is one or more of the following:
position is one of the following:
Parameters
The name of a declared and opened cursor.
Specifies that Oracle OLAP should implicitly loop over the rows obtained from a relational table. For each row, Oracle OLAP copies the data in individual fields to objects specified as target analytic workspace objects. When you include a LOOP clause, SQL FETCH continues processing rows until it reaches the end of the active set specified by the cursor, or an error occurs, or loopcount is satisfied. In most cases, use the LOOP clause to improve the performance of SQL FETCH.
When you do not specify a LOOP clause and the cursor contains multiple rows in its active set, you must code the SQL FETCH statement within a WHILE loop. This loop must be based on the value of the SQLCODE option, which returns a nonzero value to indicate the end of the data or an error.
Optional INTEGER
argument to the LOOP keyword. Loopcount controls how SQL FETCH loops over the rows from a relational table. Loopcount can be a literal value, an OLAP DML variable, or NA
. When loopcount is less than or equal to zero, no looping occurs and no data is fetched.
When you specify a LOOP clause without a value for loopcount, SQL FETCH continues reading rows and copying their contents to target analytic workspace objects until there are no more rows or an error occurs. Internally, each row is processed until SQLCODE is nonzero.
When you specify a literal value for loopcount, SQL FETCH processes the number of rows specified by loopcount or until SQLCODE is nonzero.
When you specify a variable for loopcount, it must be in the form of an OLAP DML variable (preceded by a colon). This variable acts as both an input and an output variable. The initial value of loopcount specifies the number of rows that SQL FETCH attempts to process. Upon completion of the SQL FETCH, loopcount contains the number of rows actually processed.
When you specify NA
for loopcount, SQL FETCH processes rows until SQLCODE is nonzero. However, upon completion of the SQL FETCH, loopcount contains the number of rows actually processed.
Identifies the analytic workspace objects in which you want to store data that is retrieved from a relational table. This list of target analytic workspace objects must correspond in number and data type with the list of table columns specified in the select-statement argument of the SQL DECLARE CURSOR command that declared cursor. A target can be a variable, a qualified data reference, a relation, a dimension, a composite, or a conjoint.
Important: The order in which you specify the target analytic workspace objects affects dimension status. For each dimension value, Oracle OLAP temporarily limits the status of the dimension to the fetched value. Values are assigned to subsequent analytic workspace objects according to this temporary status. See "Conjoints as Target Analytic Workspace Objects" and "Composites as Target Analytic Workspace Objects". |
A target must be preceded by a colon. When the target is a dimension, it can include the MATCH and APPEND keywords to specify dimension handling; in this case, the colon precedes the keywords.
(Default) Oracle OLAP does not perform dimension maintenance on the target dimension or surrogate. It uses the incoming values to align data that is being fetched into dimensioned objects. When a value from the relational database does not match any value in the dimension or surrogate, an error is signaled.
Oracle OLAP performs dimension maintenance on the target dimension, adding new values to the dimension. It uses both old and new dimension values to align data being fetched into dimensioned objects. By default, new values are added to the end of a dimension or surrogate.
position is one of the following:
AFTER dimension-value
Any new values are added after dimension-value in the status list.
BEFORE dimension-value
Any new values are added immediately before dimension-value in the status list.
FIRST
Any new values are added to the beginning of the status list.
LAST
Any new values are added to the end of the status list.
The position can also be used to control how dimension values are processed in action statements.
Assigns the values to the specified surrogate.
Specifies any number of action-statements to be performed each time a row of data is fetched and assigned to target analytic workspace objects. An action-statement can be one of the following:
assignment-statement
IF statement
SELECT-statement
ACROSS statement: action-statement
<action-statement-group>
Refer to the SQL IMPORT command for a complete description of the syntax of action-statements.
Usage Notes
Effect of Order SQL FETCH Targets on Dimension Status
For each dimension value, Oracle OLAP temporarily limits the status of the dimension to the fetched value. Values are assigned to subsequent analytic workspace objects according to this temporary status.
Conjoints as Target Analytic Workspace Objects
You can use a conjoint dimension as a target analytic workspace object, but you must ensure that you select the same number of columns from the relational table as there are simple base dimensions. When Oracle OLAP executes a SQL FETCH statement for a target that is a conjoint dimension, it uses the dimension order that was specified when the conjoint was defined.
Composites as Target Analytic Workspace Objects
You can specify analytic workspace objects for composites just as you would for dimensioned variables. For example, to fetch data into a variable var1
dimensioned by dim1
and dim2
, you would specify the following list of target analytic workspace objects.
:dim1 :dim2 :var1
To fetch data into a variable var2
dimensioned by a composite whose dimensions are dim1
and dim2
, you would specify the following list of target analytic workspace objects.
:dim1 :dim2 :var2
Null Values
A null value in SQL is equivalent to an NA
value in Oracle OLAP, so null values fetched into target analytic workspace objects are given NA
values. Since Oracle OLAP handles null values in this way, the SQL command does not support INDICATOR
variables in the INTO clause of a SQL FETCH statement. When fetching null values into a dimension, however, Oracle OLAP discards the values for the entire row.
Working with Boolean Variables as Input and Target Objects
You can use Boolean variables as input and target analytic workspace objects for OLAP SQL commands. In OLAP DML input expressions, Oracle OLAP treats Boolean values as INTEGER
values with a value of 1
(TRUE
) or 0
(FALSE
).
As target analytic workspace objects, Boolean variables can receive values from any numeric (or bit) column in a relational table.
Fetching Text Data into DATE Variables
When fetching text data into a DATE variable, the current setting of the DATEORDER option is used to interpret the value. For example, a text value of 12-08-96
could be interpreted as December 8, 1996, or August 12, 1996, depending on the setting of DATEORDER.
Untransferable Data Types
You cannot transfer data with the following data types: LONG RAW, ROWID, UROWID, BLOB, and BFILE.
Examples
Fetching Data From Relational Tables -- A Simple SQL FETCH
he following program fragment shows the basic steps of declaring and opening a cursor, and fetching the data. Relational data from the Prod_ID
and Prod_Name
columns of the Products
table are fetched into the prod
dimension and prod_label
variable. The variable prod_label
is dimensioned by prod
. Notice that the SQL FETCH statement in this example does not include a LOOP clause; it therefore retrieves a single row of data each time it is called.
VARIABLE set_price SHORT set_price = 20 ... SQL DECLARE highprice CURSOR FOR SELECT Prod_ID, Prod_Name - FROM Products WHERE suggested_price > :set_price SQL OPEN highprice WHILE SQLCODE EQ 0 SQL FETCH highprice INTO :prod, :prod_label
Fetching Data From Relational Tables with a THEN Clause
The following program fragment shows the SQL FETCH statement from the previous example with the addition of the LOOP keyword and a THEN clause. Because of the LOOP keyword, this SQL FETCH statement does not have to run within a WHILE loop. The action statement following the THEN keyword copies any product names stored in prod_label
that start with the letter A
into a multiline text variable called a_product
.
SQL FETCH highprice LOOP INTO :prod, :prod_label - THEN IF UPCASE(EXTCHARS(prod_label, 1, 1)) EQ 'a' - THEN a_product = JOINLINES(a_product prod_label)
Populating with Relational Data While Maintaining a Conjoint Dimension
In this example, a conjoint dimension (named mpt
) is used as a target analytic workspace object. To populate a conjoint dimension, you must select values from the relational database for each of its base dimensions. Here, the three base dimensions are market
, product
, and time
. Therefore, the SELECT
statement specifies the three corresponding columns (Mktcode
, Prdcode
, and Percode
). The program assumes that the market
, product
, and time
dimensions are already populated with up-to-date values; Oracle OLAP does not update the base dimensions unless you explicitly specify them as target analytic workspace objects.
DEFINE mpt DIMENSION <market product time> DEFINE sql.mpt PROGRAM PROGRAM ... SQL DECLARE c1 CURSOR FOR - SELECT Mktcode, Prdcode, Percode FROM Sqldba.Data IF SQLCODE NE 0 THEN SIGNAL sqlerrm SQL OPEN c1 SQL FETCH c1 LOOP INTO :append mpt SQL CLOSE c1 ... END
Populating Data While Maintaining Base and Conjoint Dimensions
To retrieve current values for the base and conjoint dimensions, or to retrieve the values for the first time, you can fetch the values for the base dimensions immediately before you fetch the values for the conjoint dimension. In the following example, the SQL DECLARE CURSOR and SQL FETCH commands have been edited to fetch both base and conjoint dimension values. Notice that the number of columns selected from the relational table must match the number of base dimensions fetched. There are six column specifications in the SELECT statement. The first three match the three base dimensions, and the last three match the conjoint dimension itself.
SQL DECLARE c1 CURSOR FOR - SELECT Mktcode, Prdcode, Percode, Mktcode, - Prdcode, Percode FROM Sqldba.Data ... SQL FETCH c1 LOOP INTO :APPEND market, :APPEND product, - :APPEND time, :APPEND mpt
Populating Variables with Relational Table Data while Maintaining Dimensions
In the next example, variable dollars.mpt
is dimensioned by the conjoint mpt
, and its values are populated in the same SQL FETCH statement with the dimension values. The SQL DECLARE CURSOR and SQL FETCH commands have been edited again with the new column and target analytic workspace object added.
DEFINE dollars.mpt DECIMAL <mpt> SQL DECLARE c1 CURSOR FOR - SELECT Mktcode, Prdcode, Percode, Mktcode, Prdcode, - Percode, Dollars FROM Sqldba.Data ... SQL FETCH c1 LOOP INTO :APPEND market, :APPEND product, - :APPEND time, :APPEND mpt, :DOLLARS.mpt
Fetching Data into a Concat Dimension
Assume that a relational table has four columns of product data and that you decide to create a Product hierarchy with four levels in your analytic workspace to hold this data. The levels in the hierarchy (prod_id
, prod_subcategory
, prod_category
, and products_all
) map to columns in the products
tables. The lowest level of the hierarchy is prod_id
and the highest level is products_all
. There is also a column with supplier information in the table.
To hold the data in the analytic workspace you define a dimension was defined for each level of the Product hierarchy, a concat dimension for the hierarchy itself, and a child-parent relation between the values in the hierarchy. You also define a dimension for the supplier data and a relation that holds the relationship between suppliers and products with the following definitions.
DEFINE aw_prod_id DIMENSION NUMBER (6) DEFINE aw_prod_subcategory DIMENSION TEXT DEFINE aw_prod_category DIMENSION TEXT DEFINE aw_products_all DIMENSION TEXT DEFINE aw_products DIMENSION CONCAT (aw_products_all - aw_prod_category - aw_prod_subcategory - aw_prod_id) DEFINE aw_products.parents RELATION aw_products <aw_products> DEFINE aw_supplier_id DIMENSION NUMBER (6) DEFINE aw_prod_id.aw_supplier_id RELATION aw_supplier_id <aw_prod_id>
Assume that you write a program named get_products_hier
that consists of the following code.
' get_products_hier Program ALLSTAT " Fetch values into the products hierarchy SQL DECLARE grabprods CURSOR FOR SELECT prod_total, - prod_category, - prod_subcategory, - prod_id - FROM sh.products SQL OPEN grabprods SQL IMPORT grabprods INTO :APPEND aw_products_all - :APPEND aw_prod_category - :APPEND aw_prod_subcategory - :APPEND aw_prod_id SQL CLOSE grabprods SQL CLEANUP " Update the analytic workspace and make the updates permanent UPDATE COMMIT " Fetch values into supplier_id SQL DECLARE grabsupid CURSOR FOR SELECT supplier_id - FROM sh.products SQL OPEN grabsupid SQL IMPORT grabsupid INTO :APPEND aw_supplier_id SQL CLOSE grabsupid SQL CLEANUP " Update the analytic workspace and make the updates permanent UPDATE COMMIT " Populate self-relation for concat dimension " and relation between aw_prod_id and aw_supplier_id SQL DECLARE makerels CURSOR FOR SELECT prod_total, - prod_category, - prod_subcategory, - prod_id, - supplier_id - FROM sh.products SQL OPEN makerels SQL FETCH makerels LOOP INTO :MATCH aw_products_all - :MATCH aw_prod_category - :MATCH aw_prod_subcategory - :MATCH aw_prod_id - :MATCH aw_supplier_id - THEN aw_products.parents(aw_products aw_prod_id) - = aw_products(aw_prod_subcategory aw_prod_subcategory) - aw_products.parents(aw_products aw_prod_subcategory) - = aw_products(aw_prod_category aw_prod_category) - aw_products.parents(aw_products aw_prod_category) - = aw_products(aw_products_all aw_products_all) - aw_prod_id.aw_supplier_id = aw_supplier_id SQL CLOSE makerels SQL CLEANUP " Update the analytic workspace and make the updates permanent UPDATE COMMIT
The get_products_hier
program copies the data from the dimension tables into the base dimensions of the aw_products
concat dimension using SQL FETCH
commands with the APPEND
keyword. As the base dimensions of aw_products
are populated, Oracle OLAP automatically populates aw_products
, itself. As the THEN
clause of the SQL FETCH
statement executes, Oracle OLAP fetches data into the child-parent self-relation for aw_products
. This program also populates the aw_supplier_id
dimension and its relation.