Monday, June 30, 2014

Goldengate Filter data using Lookup Table and SQLEXEC .


This is true that You can use multiple SQLEXEC in Goldengate.

Below code shows How to filter data from SOURCE Table using Lookup table and get filter value dynamically .

This code check If Old value ORG_ID for source_table is exist in LOOKUP_PARAMETER_TAB.
to filter using lookup table ,I used FILTER and get lookup value from SQLEXEC.if value match it execute another SQLEXEC where it
execute stored procedure and Get dynamic parameters using @GETVAL function and pass to Procedure.
TABLE "INV"."SOURCE_TABLE",FILTER (ORG_ID = @GETVAL(lookup.XYZ)),SQLEXEC (ID lookup, QUERY "select distinct (MASTER_ORG_ID) XYZ from ABC.LOOKUP_PARAMETER_TAB mp  where mp.MASTER_ORG_ID= :p_organization_id_X", PARAMS (p_organization_id_X=ORG_ID),BEFOREFILTER , TRACE),SQLEXEC (ID ABC.PACKAGE_TO_EXEC.PROCEDURE_TO_EXEC,SPNAME ABC.PACKAGE_TO_EXEC.PROCEDURE_TO_EXEC,PARAMS (P_DBNAME=@GETENV("DBENVIRONMENT","DBNAME"),P_TRANSACTION_ID=@GETENV("TRANSACTION", "XID"),P_COMMAND=@CASE(@GETENV("GGHEADER", "OPTYPE"), "INSERT", "INSERT", "SQL COMPUPDATE", "UPDATE", "PK UPDATE", "UPDATE","DELETE","DELETE"),P_INVENT_ITEM_ID=INVENT_ITEM_ID,P_ORGANIZATION_ID=ORG_ID),AFTERFILTER, TRACE);

No comments:

Post a Comment