Friday, September 30, 2016

Implementing complex filters in the Extracts or Replicats


I prefer to apply filters from the extracts for reducing size of the trail files, this is an example to implement complex filter in the Extract.


TABLE schema.TABLE1,FETCHBEFOREFILTER,
SQLEXEC( ID RN_CHECK, QUERY "select count(1) c_total
    FROM
    (select FK_COL, DATE_COL from TABLE1 where KeyCol= :p_id) a
      WHERE
    (A.FK_COL  IN
        (SELECT PK
        FROM TABLE2
        WHERE COL LIKE '%Condition%'
        )
      AND A.DATE_COL >= TRUNC (SYSDATE - 30))
      OR (A.FK_COL                     IN
        (SELECT PK
        FROM TABLE3
        WHERE COL LIKE '%Condition%'
        )
      AND A.DATE_COL >= TRUNC (SYSDATE - 30))
    )", PARAMS (p_id=ID_COL),BEFOREFILTER),
COLS (col1,col2,col3,col4,col5),
FILTER (RN_CHECK.c_total>0);

Wednesday, September 21, 2016

Filter records by Date in GoldenGate Extract or Replicat

I post this information because it was hard for me to find the solution in GoldenGate Documentation.

Scenario: We need to implement a filter by date in the Extract

              where date_column > to_date('2016-01-01','yyyy-mm-dd')

Simple...

Implemented:


TABLE source_table,
COLS (list of columns),
FILTER (@COMPUTE (@DATE('JUL', 'YYYY-MM-DD', date_column) >= @DATE('JUL', 'YYYY-MM-DD', '2016-01-01')) > 0)

Should be easier, if someone found another option please feedback.