GETVAL

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:

  1. Whether or not the query executed successfully.

  2. 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))
);