英文:
Add Isnull join condition to Azure data factory data flow
问题
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 don'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 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 .
答案1
得分: 0
为了在连接转换条件中添加col1 is not null
,您可以使用交叉连接,然后将条件设置为and(source1@id == source2@id,not(isNull(name)))
。但这不会给出左表的所有行。作为解决方法,您可以使用连接转换来进行具有相同ID的条件连接,并使用过滤转换来检查所需字段是否为空。我使用示例数据重新生成了这个问题。在这个示例中,我想根据id
列对source1和source2进行左连接,同时检查name
是否不为空。
源数据:1
id,name
1,Pasapugazh
2,Venkat
3,Senthil
4,Virumandi
5,
6,Pari
源数据:2
id,age
1,21
2,22
3,32
4,35
5,42
- 进行连接转换,将连接类型设置为
左外连接
。
条件为source1@id==source2@id
连接的输出结果:
- 要删除具有空名称的行,进行过滤转换,条件为
not(isNull(name))
输出结果
这将删除具有空名称的行。通过这种方式,您可以使用连接和过滤转换在数据流中扩展连接条件。
英文:
To add col1 is not null
in the join transformations condition, you can use cross join and then give the condition as and(source1@id == source2@id,not(isNull(name)))
. But it doesn't give all the rows of left table. As a workaround, you can use Join transformation for condition with same ids and filter transformation to check if the required field is null. I reproduced this with sample data. In this example, I want to do left join the source1 with source2 based on the id
column and also to check if name
is not null.
Source:1
id,name
1,Pasapugazh
2,Venkat
3,Senthil
4,Virumandi
5,
6,Pari
Source:2
id,age
1,21
2,22
3,32
4,35
5,42
- Join transformation is taken and Join type is given as
left outer
.
Conditions issource1@id==source2@id
Output of Join:
- To remove the rows with null name, filter transformation is taken and condition is given as
not(isNull(name))
Output
This removed the rows with null name. By this way, you can expand the joining conditions by join and filter transformations in data flow.
答案2
得分: 0
我从主数据流中创建了两个分支(用于PT和BT上的左连接)
应用了我的筛选条件到每个分支,需要"UpdatedWOV.Id"为null(isNull(UpdatedWOV.Id))
最后,我使用相同的连接条件将每个筛选结果集成回主数据流;
左连接PT
和CTH.Id = PT.TranId
左连接BT
和CTH.Id = BT.TranId
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论