Page Comparison

What is the syntax to filter on a record with the most recent date in a Convergence for DI Semarchy Integrator mapping?

Depending on how a job is set-up setup, there are a couple of options:

Option 1: Use a filter on the source table:

Step-by-step guide

1. You have need to use a correlated sub-query which means you have having to specify the schema and fully qualify the table names. They The syntax would be something like this:

2. TEST_DATE.UPDATE_DATE = (
   select MAX(UPDATE_DATE) from SEMSTG.TEST_DATE where ID = TEST_DATE.ID
   )

3. Set the Filter Type to Aggregate in the Properties of the filter (see screen shot screenshot above).

• Note you cannot re-use the metadata alias (TEST_DATE) for the column being evaluated in the sub-query (MAX(UPDATE_DATE)), This this is because it is a sub-query and it does not know about the metadata at this level.
• Also, you have to specify the source schema name, as the sub-query cannot pick that up from the DI metadata (SEMSTG.TEST_DATE).
• This is a robust method, but you have to specify the schema name in the sub-query and if that changes, you have will need to physically change it in the sub-query. There are ways of making this dynamic if needs berequired.

Option 2: Set a filter on the target table (this does not filter on the source)

Step-by-step guide

On the target field mapping, simply use the syntax: MAX(TEST_DATE.UPDATE_DATE) where update_date is replaced by your column name.

1. Select Aggregate in the Properties of the target field  (see screen shot screenshot above).

• Note there are some restrictions to using this method. For example, it cannot be used in conjunction with a Select Distinct. You will get an ORA error message which will highlight the incompatibility if there is one.

...

Related articles