![]() Previous |
![]() Next |
The SQL SELECT command uses an implicit cursor to copy data from relational tables into analytic workspace objects. You use the SQL SELECT command to copy data from relational tables into analytic workspace objects using an implicit cursor. You can also use copy the data using an explicit cursor using the OLAP DML commands outlined in "Copying Relational Data into Analytic Workspace Objects".
Syntax
SQL SELECT expressions FROM tables -
[WHERE predicates] [GROUP BY expressions] -
[ORDER BY expressions] [HAVING predicates] -
INTO :targets... [THEN action-statements...]
where targets is one or more of the following:
Parameters
A SQL SELECT statement that identifies the data you want to associate with the cursor. For the syntax of an SQL SELECT statement, refer to Oracle Database SQL Language Reference.
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. 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. 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.
Tip: Since both OLAP DML syntax and SQL WHERE clauses allow you to use AND and OR, construct the targets clause clearly so that Oracle OLAP can identify the end of an OLAP DML input expression. |
(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. The position can also be used to control how dimension values are processed in action statements.
Assigns the values to the specified surrogate.
You may optionally include a THEN clause to specify any number of action-statements to be performed each time a row of data is fetched and assigned to analytic workspace objects. An action-statement can be one of the following:
Refer to the SQL IMPORT command for a complete description of the syntax of action-statement.
Usage Notes
General Restrictions that APPLY to SQL SELECT
An SQL SELECT statement cannot contain ampersand substitution.
Optimizing Copies
When copying values from relational tables into a multidimensional input variable, list the columns that correspond to the dimensions in an ORDER BY clause in the select-statement argument of the SQL SELECT statement, with the slowest-varying dimension first which optimizes performance.
Examples
Simple select
For example, assume that there is a relational table named sales
with the following description.
PROD_ID NOT NULL NUMBER(6) CUST_ID NOT NULL NUMBER TIME_ID NOT NULL DATE CHANNEL_ID NOT NULL CHAR(1) PROMO_ID NOT NULL NUMBER(6) QUANTITY_SOLD NOT NULL NUMBER(3) AMOUNT_SOLD NOT NULL NUMBER(10,2)
Assume also that your analytic workspace contains the following definitions for corresponding analytic workspace objects.
DEFINE aw_prod_id DIMENSION NUMBER (6) DEFINE aw_cust_id DIMENSION NUMBER (6) DEFINE aw_date DIMENSION TEXT DEFINE aw_channel_id DIMENSION TEXT DEFINE aw_promo_id DIMENSION NUMBER (6) DEFINE aw_sales_dims COMPOSITE <aw_prod_id aw_cust_id - aw_channel_id aw_promo_id> DEFINE aw_sales_quantity_sold VARIABLE NUMBER (3) <aw_date aw_sales_dims - <aw_prod_id aw_cust_id aw_date aw_channel_id aw_promo_id>> DEFINE aw_sales_amount_sold VARIABLE NUMBER (10,2) <aw_date aw_sales_dims - <aw_prod_id aw_cust_id aw_date aw_channel_id aw_promo_id>>
To copy the data for product 415 from the sales
table into the analytic workspace objects, you execute the following statement in the OLAP worksheet.
SQL SELECT prod_id cust_id time_id channel_id promo_id quantity_sold -
amount_sold WHERE prod_id = 415 - INTO :aw_prod_id, :aw_cust_id, :aw_date, - :aw_channel_id, :aw_promo_id, :aw_sales_quantity_sold, :aw_sales_amount_sold