![]() Previous |
![]() Next |
The SQL IMPORT command retrieves and processes data specified by an explicit SQL cursor. SQL IMPORT assigns the retrieved data to OLAP objects. You use the SQL IMPORT 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". SQL IMPORT is particularly effective in copying fact data from relational tables into analytic workspace variables.
Note: You cannot transfer data with the following data types: LONG, BLOB, and BFILE. |
Syntax
SQL IMPORT cursor [:var-num-of-rows |num-of-rows[:var-num-of-processed-rows]]-
INTO :targets... [THEN action-statements...]
where:
targets is one or more of the following:
action-statements is one of the following:
Parameters
The name of a declared cursor.
The name of a variable that specifies the number of rows that you want SQL IMPORT to attempt to import.
A numeric constant that specifies the number of rows that you want SQL IMPORT to attempt to import.
When you include the MATCHSKIPERR keyword in the targets parameter, the name of a variable that specifies the actual number of rows that you want SQL IMPORT to import into analytic workspace objects.
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, or a composite.
Important: The order in which you specify the 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. |
(Default) Oracle OLAP does not copy values from the corresponding relational table column into the target dimension or surrogate. It only uses the 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, an error is signaled.
Oracle OLAP does not copy values from the corresponding relational table column into the target dimension or surrogate. It only uses the 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, the value is ignored and processing continues without signaling an error.
The one-based logical position of the value.
Oracle OLAP performs dimension maintenance on the target dimension, adding new values from the corresponding relational table column to the dimension. It uses both old and new dimension values to align data being fetched into dimensioned objects. New values are added to the end of a dimension.
Oracle OLAP assigns the corresponding relational value to the specified surrogate.
The name of the analytic workspace dimension.
The name of an analytic workspace surrogate.
The name of the analytic valueset.
The name of the analytic workspace relation.
The name of a variable.
A QDR is a qualifier that limits one or more of the dimensions of a variable or a relation to a single value. Oracle OLAP evaluates QDRs in a SQL IMPORT statement, as follows:
When the QDR includes an expression, the expression is evaluated only once before the data is retrieved. In other words, the expression is, in essence, a constant.
When the QDR is specified as a relation name, the values of the QDR vary depending on the status of the dimensions of that relation.
Specifies any number of action-statements to be performed each time a row of data is imported and assigned to analytic workspace objects. Action statements may contain simple assignment statements, conditional assignment statements, and assignments across dimensions.
Action statements allow you to examine and manipulate the fetched data on a row-by-row basis. For example, you may want to specify temporary objects as analytic workspace objects and only update your permanent objects once you have performed certain actions on the row of fetched data. However, action statements do not have to reference the imported data. For example, one of your action statements might be an assignment statement that executes a user-defined function (that is, a program) that performs complex processing and then simply increments a counter.
A THEN clause can improve SQL loading performance by eliminating the need for postprocessing upon completion of a SQL IMPORT.
Note: The syntax of an action statement within SQL IMPORT is essentially the same as the syntax of an action statement within FILEREAD. Exceptions are in the syntax of an assignment statement and the use of the VALUE keyword. In SQL IMPORT action statements, assignments must be explicit; they must include a source, target, and equal sign. In FILEREAD action statements, assignments may be implicit and specify only the target. The VALUE keyword is supported in FILEREAD action statements, but not in SQL IMPORT action statements. When you have already specified action statements for use with FILEREAD, you can reuse the code with SQL IMPORT by simply adjusting the assignment statements and eliminating the VALUE keyword (if necessary). Most of the attributes listed in FILEREAD (except for the attributes that control dimension processing) are not meaningful for SQL loading and are ignored when executing within SQL IMPORT.For best performance, within a THEN clause reference only the data within the imported row. |
In your list of action statements, be sure to process dimensions before variables. Oracle OLAP processes each action statement from left to right for each row in the relational table. When an action statement performs dimension processing, the resulting status remains in effect for subsequent action statements. When you do not first specify action statements that limit a variable's dimensions, Oracle OLAP uses the first value in status to target a cell in the variable. Unless you specify an ACROSS phrase, Oracle OLAP assigns a single value from a row to a single cell in an Oracle OLAP variable. By default, Oracle OLAP does not loop over a variable's dimensions when assigning data to the variable.
An assignment statement (SET) that assigns a value that is the result of an expression to an Oracle OLAP object.
An IF...THEN...ELSE command that performs some action depending on whether a Boolean expression is TRUE
or FALSE
.
A SQL SELECT statement lets you perform some action based on the value of an expression. A SELECT statement has the following form.
SELECT evaluates the SELECT expression and then sequentially compares the result with the WHEN expressions. When the first match is found, the associated action occurs. When no match is found, the ELSE action (if specified) occurs.
An ACROSS statement causes the following action statement to execute once for every value in status of the ACROSS dimension. When you want the looping to apply to multiple action statements, enclose the action statements in angle brackets. An ACROSS statement has the following form.
ACROSS dimension [limit]:
action-statement
In an ACROSS statement, limit temporarily change the status of dimension, if it is not in a FOR loop over dimension. The new status is in effect only for the duration of the SQL FETCH statement. The format of limit is as follows.
[ADD|COMPLEMENT|KEEP|REMOVE|TO] limit-clause
To specify the temporary status, insert any of the LIMIT command keywords (the default is TO) along with an appropriate list of dimension values or related dimensions. You can use any valid limit clause (see the LIMIT command for further information). The following example limits month
to the last six values, no matter what the current status of month
is.
ACROSS month last 6: units
You can group several action statements by enclosing them in angle brackets. An action-statement-group has the following form.
<action-statement1 -
[action-statement2 . . .]>
A typical use for action statement groups is after an ACROSS statement. With the angle bracket syntax, you can cause multiple action statements to execute for every value in status of the ACROSS dimension.
Usage Notes
Effect of Order of SQL SELECT 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.
Working with Boolean Data Variables
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.
Importing Text Data into a DATE Variable
When importing 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.
Examples
Simple Import
The following program fragment shows the basic steps of declaring a cursor and importing the data. Values from the Prod_ID
and Prod_Name
columns of the Products
relational table in the Sales -History (sh
) database are fetched into the prod_id
dimension and prod_label
analytic workspace variable. The prod_label
variable is dimensioned by prod_id
.
SQL DECLARE productcur CURSOR FOR SELECT Prod_ID, Prod_Name FROM sh.Products SQL OPEN productdur SQL IMPORT productcur INTO :prod_id, :prod_label SQL CLOSE productcur SQL CLEANUP