Performing an outer join using the %GETCOL function (DB2® for i)

An example of how to perform an outer join using the %GETCOL function and the CDC Replication Engine for Db2® for i.

This example uses the relationship between primary and secondary tables. In this example, you will retrieve the column NAME from the COUNTRY table using the COUNTRY column from the STATE table. The COUNTRY column, used to read the COUNTRY table, is not from the primary table.

To use this example, perform the following steps:
  1. Add a derived column, CTNAME, based on the NAME column, to the COUNTRY table.
  2. Enter the following expression for the CTNAME column: %GETCOL(NAME, PRODLIB/COUNTRY, , , 1, %GETCOL(COUNTRY, PRODLIB/STATE))

    The second %GETCOL function retrieves the COUNTRY column from the STATE table. This column was retrieved in a previous %GETCOL invocation. In the first %GETCOL function, the default_value and record_format parameters are not specified, as they are specified in the long version of the %GETCOL function that performed the read.