Use the @
GETVAL
function to extract values from a 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 query executed successfully.
Whether or not the query results have expired.
Handling Missing Column Values
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 query. When using SQLEXEC
to execute the query, the valid value is the logical name specified with the ID
option of the SQLEXEC
clause. ID
is a required SQLEXEC
argument for queries.
parameter
Valid values are one of the following.
The name of the parameter in the query from which the data will be extracted and passed to the column map.
RETURN_VALUE
, if extracting values returned by a query.
Example
The following example enables the COMPUTE
statements to call two stored procedures selectbal
and selecttran
by referencing the logical name within the @GETVAL
function and referring appropriately to the results of each.
MAP \NY.$DATA1.GGSDAT.ACCTTR, TARGET \NY.$DATA1.GGSDAT.ACCTBL SQLEXEC (ID selecttran, ON UPDATES, ON INSERTS, QUERY " select tran_type, tran_amt from $DATA1.GGSDAT.ACCTTR " " where ACCT_NUM = ?P1 ", PARAMS (P1 = ACCT_NUM), ERROR REPORT) SQLEXEC (ID selectbal, ON UPDATES, ON INSERTS, QUERY " select acct_balance from $DATA1.GGSDAT.ACCTBL " " where ACCT_NUM = ?P1 ", PARAMS (P1 = ACCT_NUM), ERROR REPORT) COLMAP (USEDEFAULTS, acct_balance = @IF (@GETVAL (selecttran.tran_type) = 1 @COMPUTE (@GETVAL (selectbal.acct_balance) - selecttran.tran_amt), @COMPUTE (@GETVAL (selectbal.acct_balance) + selecttran.tran_amt)) );