Previous
Previous
 
Next
Next


SQL IMPORT

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:

 

Parameters

cursor

The name of a declared cursor.

var-num-of-rows

The name of a variable that specifies the number of rows that you want SQL IMPORT to attempt to import.

num-of-rows

A numeric constant that specifies the number of rows that you want SQL IMPORT to attempt to import.

var-num-of-processed-rows

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.

targets

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.

MATCH

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

MATCHSKIPERR

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.

position

The one-based logical position of the value.

APPEND

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.

ASSIGN

Oracle OLAP assigns the corresponding relational value to the specified surrogate.

dimension

The name of the analytic workspace dimension.

surrogate

The name of an analytic workspace surrogate.

valueset

The name of the analytic valueset.

relation

The name of the analytic workspace relation.

variable

The name of a variable.

qualified_data_reference

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:

THEN action-statements...

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.

assignment-statement

An assignment statement (SET) that assigns a value that is the result of an expression to an Oracle OLAP object.

IF-statement

An IF...THEN...ELSE command that performs some action depending on whether a Boolean expression is TRUE or FALSE.

SELECT-statement

A SQL SELECT statement lets you perform some action based on the value of an expression. A SELECT statement has the following form.

SELECT select-expression
[WHEN expression1 action]
[WHEN expression2 action . . .]
[ELSE action]

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.

ACROSS-statement: action-statement

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
<action-statement-group>

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