I have this sql script : I need to add the "ON UpdatedWOV.Id IS NULL AND CTH.Id = PT.TranId" to a dataflow. My challenge is specifically how to add "ON UpdatedWOV IS NULL"
please note that there are other join conditions that doesn't require the filter;
left join PT
ON UpdatedWOV.Id is null
AND CTH.Id = PT.TranId
left join BT
on UpdatedWOV.Id is null
and CTH.Id = BT.TranId
left join etl.PmsRecord
on CTH.PMSRecordId = PmsRecord.Id
left join etl.PMSExpenseRecord
on CTH.PMSExpenseRecordId = PMSExpenseRecord.Id
left join etl.PMSCommissionRecord
on CTH.PMSCommissionRecordId = PMSCommissionRecord.Id
I tried to add a filter with the expression "isNull(UpdatedWOV.Id)" in the data flow right before applying the left join "CTH.Id = PT.TranId" but that filters out all the valid records .
I created two branches (for the left joins on PT and BT) from the main data flow
and applied my filter on each branch that required "UpdatedWOV.Id" to be null (isNull(UpdatedWOV.Id))
I finally integrated the results of each filter back to the main data flow using the same join conditions;
left join PT AND CTH.Id = PT.TranId
left join BT
and CTH.Id = BT.TranId