添加Isnull连接条件到Azure数据工厂数据流

huangapple go评论63阅读模式
英文:

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

添加Isnull连接条件到Azure数据工厂数据流

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

添加Isnull连接条件到Azure数据工厂数据流

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

添加Isnull连接条件到Azure数据工厂数据流

连接的输出结果:

添加Isnull连接条件到Azure数据工厂数据流

  • 要删除具有空名称的行,进行过滤转换,条件为not(isNull(name))

添加Isnull连接条件到Azure数据工厂数据流

输出结果

添加Isnull连接条件到Azure数据工厂数据流

这将删除具有空名称的行。通过这种方式,您可以使用连接和过滤转换在数据流中扩展连接条件。

英文:

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 is source1@id==source2@id

添加Isnull连接条件到Azure数据工厂数据流

Output of Join:

添加Isnull连接条件到Azure数据工厂数据流

  • To remove the rows with null name, filter transformation is taken and condition is given as not(isNull(name))

添加Isnull连接条件到Azure数据工厂数据流

Output

添加Isnull连接条件到Azure数据工厂数据流

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
添加Isnull连接条件到Azure数据工厂数据流

添加Isnull连接条件到Azure数据工厂数据流

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

添加Isnull连接条件到Azure数据工厂数据流

huangapple
  • 本文由 发表于 2023年7月17日 09:59:26
  • 转载请务必保留本文链接:https://go.coder-hub.com/76701109.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定