Using Dimensions as Output Host Variables

Syntax of FETCH statement

When an output host variable is a dimension, retrieved values are handled based on the keyword that you specify before the host variable name. You can specify either the MATCH keyword (the default) or the APPEND keyword.

SQL FETCH cursor LOOP INTO [MATCH|APPEND] dimension

MATCH keyword

With the MATCH keyword, only values that are the same as existing values of the dimension are fetched, and an error is signalled when a new value is encountered. You use it when fetching data into a variable whose dimensions are already maintained; the dimensions are included in the fetch only to align the data.

In the following example, the MATCH keyword is omitted because it is the default value. The data in the PROD_ID column of the Products table corresponds to the PROD dimension values in the analytic workspace. An error is signalled when a value from the relational table does not match any value in the PROD dimension.

sql fetch highprice loop into :prod, :labels.p

APPEND keyword

With the APPEND keyword, all values that do not match are added to the list of dimension values.

APPEND has the following arguments, which you use to specify the position where the new values will be inserted. LAST is the default value.

AFTER value
BEFORE value 
FIRST
LAST

Example 1: Adding values to the PROD dimension

In the following example, the APPEND keyword allows new values to be added to the PROD dimension.

sql fetch highprice loop into :append prod, :labels.p

Example 2: Adding values to the PROD dimension

In the next example, new dimension values are added to the PROD dimension before the value P11.

sql fetch highprice loop into :append before 'P11' prod :labels.p