2.19 @GETVAL

Use the @GETVAL function to extract values from a stored procedure or query so that they can be used as input to a FILTER or COLMAP clause of a MAP or TABLE statement.

Whether or not a parameter value can be extracted with @GETVAL depends upon the following:

1. Whether or not the stored procedure or query executed successfully.

2. Whether or not the stored procedure or query results have expired.

When a value cannot be extracted, the @GETVAL function results in a "column missing" condition. Typically, this occurs for update operations if the database only logs values for columns that were changed.

Usually this means that the column cannot be mapped. To test for missing column values, use the @COLTEST function to test the result of @GETVAL, and then map an alternative value for the column to compensate for missing values, if desired. Or, to ensure that column values are available, you can use the FETCHCOLS or FETCHCOLSEXCEPT option of the TABLE or MAP parameter to fetch the values from the database if they are not present in the log. Enabling supplemental logging for the necessary columns also would work.

Syntax

@GETVAL (name.parameter)

name

The name of the stored procedure or query. When using SQLEXEC to execute the procedure or query, valid values are as follows:

For queries, use the logical name specified with the ID option of the SQLEXEC clause. ID is a required SQLEXEC argument for queries.

For stored procedures, use one of the following, depending on how many times the procedure is to be executed within a TABLE or MAP statement:

• For multiple executions, use the logical name defined by the ID clause of the SQLEXEC statement. ID is required for multiple executions of a procedure.

• For a single execution, use the actual stored procedure name.

parameter

Valid values are one of the following.

• The name of the parameter in the stored procedure or query from which the data will be extracted and passed to the column map.

• RETURN_VALUE, if extracting values returned by a stored procedure or query.

Alternate Syntax

With SQLEXEC, you can capture parameter results without explicitly using the @GETVAL keyword. Simply refer to the procedure name (or logical name if using a query or multiple instances of a procedure) and parameter in the following format:

{procedure_name | logical_name}.parameter

Examples, Standard Syntax

Example 1   

The following enables each map statement to call the stored procedure lookup by referencing the logical names lookup1 and lookup2 within the @GETVAL function and refer appropriately to each set of results.

MAP schema.srctab, TARGET schema.targtab,
SQLEXEC (SPNAME lookup, ID lookup1, PARAMS (param1 = srccol)),
COLMAP (targcol1 = @GETVAL (lookup1.param2));
MAP schema.srctab, TARGET schema.targtab2,
SQLEXEC (SPNAME lookup, ID lookup2, PARAMS (param1 = srccol)),
COLMAP (targcol2= @GETVAL (lookup2.param2));

Example 2   

The following shows a single execution of the stored procedure lookup. In this case, the actual name of the procedure is used. A logical name is not needed.

MAP schema.tab1, TARGET schema.tab2,
SQLEXEC (SPNAME lookup, PARAMS (param1 = srccol)),
COLMAP (targcol = @GETVAL (lookup.param1));

Example 3   

The following shows the execution of a query from which values are mapped with @GETVAL.

MAP sales.account, TARGET sales.newacct,
SQLEXEC (ID lookup,
QUERY ' select desc_col into desc_param from lookup_table '
' where code_col = :code_param ',
PARAMS (code_param = account_code)),
COLMAP (newacct_id = account_id, newacct_val = @GETVAL (lookup.desc_param));

Examples, Alternate Syntax

Example 1   

In the following example, @GETVAL is called implicitly for the phrase proc1.p2 without the @GETVAL keyword.

MAP test.tab1, TARGET test.tab2,
SQLEXEC (SPNAME proc1, ID myproc, PARAMS (p1 = sourcecol1)),
COLMAP (targcol1 = proc1.p2);

Example 2   

In the following example, the @GETVAL function is called implicitly for the phrase lookup.desc_param without the @GETVAL keyword.

MAP sales.account, TARGET sales.newacct,
SQLEXEC (ID lookup,
QUERY ' select desc_col into desc_param from lookup_table '
' where code_col = :code_param ',
PARAMS (code_param = account_code)),
COLMAP (newacct_id = account_id, newacct_val = lookup.desc_param);