1 2 Previous Next 21 Replies Latest reply on Oct 19, 2020 11:02 AM by Nico Heinze

    Detail Outer Join/Filter transformation not working properly

    Saurabh Shrivastava Guru

      Hi All,

       

      I am joining data from 2 different sources(Stage and BO tables) in Joiner Transformation.

      BO records are considered as Master records. Join type is Detail outer join.

      Joiner properties are as below

      My requirement is that If the KEYS are matching(for any of record of NOTES_NAME)(Scenario 1,2,4) then all records from BO table should be loaded into the target (backload) and if the KEYS are not matching(scenario 3,5,6) then those records should be filtered out.

       

      Below are the scenarios

      After joiner I have used a filter transformation that has below condition to filter out the output records of JNR

      IIF(NOT ISNULL(PRIMARY_KEY),

      IIF(PRIMARY_KEY=PKEY_SRC_OBJECT,TRUE,FALSE),FALSE)

       

      But this filter condition is not working properly. Can anyone please suggest a suitable condition to accommodate above scenarios or any other approach is most welcome....

       

      NOTE: Database type is oracle and I cannot use SQL override as STAGE & BO Tables are in different schemas, so it can't be joined in SQ.

        1 2 Previous Next