Publishing multiple derived columns using the %GETCOL function (Dynamic SQL)
An example of how to publish multiple derived columns using the %GETCOL function.
This example refers to the primary (EMPLOYEE) and secondary (BRANCH,
STATE, and COUNTRY) table relationship. The table names referenced
in the examples follow the format for the CDC Replication Engine for Microsoft SQL Server and CDC Replication Engine for Sybase databases.
If you installed another CDC Replication replication
engine, see the table_name parameter in this
function for more information about specifying table names which depends
on your database. 
This example combines the CUSTID and CUSTADDR tables into a single
CUSTOMER table with all five columns. By calling the %GETCOL function
twice, you retrieve values of two columns from the same row in the
secondary table. The first call reads the entire row into memory and
the second call retrieves data from the same row in memory, without
reading the same table twice. 
To use this example, perform the following steps:
- Add a derived column, ADDR1, to the CUSTID source table and enter
the following expression for the column:
%GETCOL(ADDRESS1,MASTER.DBO.CUSTADDR
,Not Found
, CUSTNO, CUSTNO) - Add a derived column, ADDR2, to the CUSTID source table and enter
the following expression for the column:
%GETCOL(ADDRESS2,MASTER.DBO.CUSTADDR
,Not Found
)
The expression for ADDR1 queries the CUSTADDR table and returns
the value in the ADDRESS1 column where the two CUSTNO values match.
The expression for ADDR2 uses the results returned by the previous
%GETCOL function invocation without reading the CUSTADDR table. Instead,
it uses matching rows from the ADDR2 definition to know when to return
the value from the ADDRESS2 column. If either %GETCOL function cannot
find a matching row, then it returns a value of Not Found
.
