英文:
Informatica: How to filter a table based on aggregation result of another table?
问题
在Informatica中，如何基于另一个表的聚合结果来筛选表格？
给定以下两个表：
Table_1:
SomeText VARCHAR,
SomeDate DATE
Table_2:
SomeDate_2 DATE,
OutputDate_2 DATE
以及Table_2的以下聚合操作（放到输出端口"MinDate"）：
SELECT MIN(OutputDate_2) AS MinDate
FROM Table_2
WHERE SomeDate_2 BETWEEN 01/01/2023 AND 02/01/2023
如何能够使Table_1在SomeDate >= MinDate的条件下被筛选？
如果我能在Table_1的源限定符中添加一个端口/列，带有MinDate的值，我可以使用它来筛选Table_1。但不确定是否可能。
英文:
In Informatica, how to filter a table based on the aggregation result of another table?
Given the following 2 tables:
Table_1:
SomeText VARCHAR,
SomeDate DATE
Table_2:
SomeDate_2 DATE,
OutputDate_2 DATE
and the following aggregation (into output port "MinDate") of Table_2:
SELECT MIN(OutputDate_2) AS MinDate
FROM Table_2
WHERE SomeDate_2 BETWEEN 01/01/2023 AND 02/01/2023
how can Table_1 be filtered on SomeDate >= MinDate?
If I can add a port/column in Table_1's source qualifier with MinDate, I can use that to filter Table_1. But not sure if that's possible.
答案1
得分: 1
你可以使用另一个源限定器，执行全外连接，并使用筛选转换。
在这种情况下，您需要注意连接的方式，因为如果不小心执行，它可能会产生许多重复的行。
另一个选项是单个源限定器，带有两个连接的源转换和一个SQL覆盖。这与您之前所做的几乎相同，但在查看映射时会清楚地指示使用了两个源。
Src1-SQ1---Jnr--Flt--Tgt
/
Src2/
英文:
You can have another Source Qualifier, do a Full Outer Join and use a Filter Transformation.
Src1-SQ1---Jnr--Flt--Tgt
/
Src2-SQ2-/
In such case you need to pay attention to the way you join, as it might produce a lot of duplicated rows if not done carefully.
Another option is single Source Qualifier with two Source Transformations connected and a SQL Override. This is pretty much the same thing you did, but it will clearly indicate the use of both sources at the first glance at the mapping.
Src1-SQ1---Jnr--Flt--Tgt
/
Src2/
答案2
得分: 0
我刚刚在源限定器中使用了SQL查询。
英文:
I just used Sql Query in the source qualifier.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论