@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:
-
Whether or not the stored procedure or query executed successfully.
-
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 theSQLEXEC
clause.ID
is a requiredSQLEXEC
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
orMAP
statement:-
For multiple executions, use the logical name defined by the
ID
clause of theSQLEXEC
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 nameslookup1
andlookup2
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 phraseproc1.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 phraselookup.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);