Filtering the Most Recent Record using Semarchy Integrator

Owned by Former user (Deleted)
Last updated: Mar 14, 2018 by Peter Tse
2 min read

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

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

Option 1: Use a filter on the source table:

Step-by-step guide

  1. You need to use a correlated sub-query which means having to specify the schema and fully qualify the table names. 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 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 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 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 will need to physically change it in the sub-query. There are ways of making this dynamic if required.


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 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.