Retrieving a column using nested %GETCOL functions (Dynamic SQL)

An example of how to retrieve a column using nested %GETCOL functions.

This example refers to the primary and secondary table relationship. The table names referenced in the examples follow the format for he 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.

To use the example, you must add a derived column to the MASTER.DBO.EMPLOYEE primary table and enter the specified %GETCOL function in that column.

%GETCOL(NAMES, MASTER.DBO.STATE, , STATES, STATE)

This function call retrieves the NAMES column from the MASTER.DBO.STATE secondary table, using the key column STATE in the primary table and the key column STATES in the secondary table. If a record in the primary table has a state that does not exist in MASTER.DBO.STATE, then this function returns the default value for the data type of the NAMES column. For example, blank characters if the data type is character.

%GETCOL(NAMEC, MASTER.DBO.COUNTRY, , COUNTRYC, %GETCOL(COUNTRYS, MASTER.DBO.STATE, ))

Used after the previous %GETCOL function, the second %GETCOL function in this example is called first to retrieve the COUNTRYS column from the MASTER.DBO.STATE table. Then, the first %GETCOL function is called to retrieve the NAMEC column from the MASTER.DBO.COUNTRY table using the key column COUNTRYS in MASTER.DBO.STATE and the key column COUNTRYC in MASTER.DBO.COUNTRY.

In this example you do not retrieve the NAMEC column in the MASTER.DBO.COUNTRY table directly, using the COUNTRYC key column, because the primary table does not contain a key column to retrieve NAMEC. Instead, you retrieve the NAMEC column indirectly using the COUNTRYS key column in MASTER.DBO.STATE and the COUNTRYC key column in MASTER.DBO.COUNTRY.