Fetching the Selected Data

FETCH statement

You use a FETCH statement to retrieve data from a relational database. The FETCH statement advances the cursor position to each subsequent row of the cursor's active set and delivers the selected fields into OLAP DML objects.

The cursor must already be declared and open before you can use the FETCH statement.

The following is the syntax of the SQL command using a FETCH statement as an argument.

SQL FETCH cursor [LOOP [loopcount]] INTO :targets... -

[THEN action-statements...]

In the above syntax, targets represents output host variables, which can be one or more of the following:

[MATCH] dimension

[APPEND position] dimension

variable|qualified data reference|relation|composite

If the output host variable is a dimension, and that dimension is preceded by a APPEND keyword, then the position that follows APPEND is one of the following:

AFTER dimension-value

BEFORE dimension-value

FIRST

LAST

This chapter describes how to use the SQL FETCH command. For details about this command, refer to the SQL FETCH entry in the OLAP DML Reference. For more information about understanding the syntax, refer to "Using Dimensions as Output Host Variables".

Definition: Output host variables

The fetched data is brought into one or more output host variables. An output host variable is an OLAP DML object that will be used to store the data retrieved from the relational database.

Using output host variables

The order of the output host variables must match the order of the columns in the DECLARE CURSOR statement, and a colon must precede each output host variable name. The variable or dimension receiving the data must be defined already. It must also have a compatible data type.

The following are examples of expressions that can be used as output host variables.

Type of Expression	Example
Variable (database or local)	:sales
Dimension	:geog
Qualified data reference	:units(geog 'G4' prod 'P15' time 'T23')

Fetching dimension values

Whenever you fetch data into a dimensioned OLAP DML variable, you must include the dimension values in the fetch. While you can add new dimension values at the same time, you do not need to add them when they already exist in your analytic workspace; instead, you use the dimension values in the fetch to align the data.

When data is written into a dimension, it temporarily limits the status of the dimension to the value being matched or appended. This means that when the FETCH statement also includes output host variables that are dimensioned by the specified dimension, the temporary status is observed when values are assigned to those variables.

Note: Be sure to fetch the dimension values before the values of the variable. Otherwise, the fetch will not loop through the dimension values.

Fetching null values

Null values in a relational table are equivalent to NAs. In OLAP DML variables, null values do not pose a problem; they appear as NAs. However, you cannot have a dimension value of NA. Therefore, any rows that have a null value are discarded in a column being fetched into a dimension.

Host indicator variables in the INTO clause of a FETCH statement are not supported. (Indicator variables are required when using embedded SQL in C and COBOL, which do not normally allow unknown or missing values in a variable.)

To promote good performance, use LOOP

Use the LOOP keyword to promote good performance. In most cases, you can improve performance by using the LOOP keyword instead of using a WHILE loop. The following is an example of a WHILE loop:

while sqlcode eq 0

        sql fetch highprice into :prod, :prod_label

Instead of using a WHILE loop, use the LOOP keyword, as shown below:

sql fetch highprice LOOP into :prod, :prod_label

For more information about the LOOP keyword and how to use it, refer to the SQL FETCH entry in the OLAP DML Reference.

Example: Fetching descriptive dimension labels

This example shows the SQL commands used to retrieve dimension labels for the PROD dimension. The FETCH statement stores descriptive labels in a variable named LABELS.P. The dimension values have already been fetched from the PROD_ID column of the Products table to the PROD dimension. They are fetched again only to align the values in the PROD_NAME column with the appropriate dimension values. Note that the output host variables, PROD and LABELS.P, must already be defined as objects in the analytic workspace.

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 fetch highprice loop into :prod, :labels.p