Informatica多相关子查询实现

本文介绍了Informatica多相关子查询实现的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

由于我对Informatica组件（尤其是SQL Transformation）缺乏经验，我面临的任务是尚未实施。 那么，在PowerCenter中实现这种子查询的最佳方法是什么？

I am facing a task that due to my lack of experience with Informatica Components, in particular SQL Transformation, I did not implemented yet. So what would be the best approch in PowerCenter to implement this kind of subquery:

SELECT A.ID, NVL2(A.SACHKONTO, B.KLAMMER, A.ID) AS KLAMMER FROM Table1 A, (SELECT A.ID AS KLAMMER, B.ID FROM (SELECT ID, ID AS VON_ID, LEAD(ID,1) OVER (ORDER BY ID) - 1 AS BIS_ID FROM Table1 WHERE SACHKONTO IS NULL) A, Table1 B WHERE B.ID BETWEEN A.VON_ID AND A.BIS_ID ) B WHERE A.ID = B.ID

所以我尝试了不同的方式，但收效甚微。 首先是分解 SQL的一小部分（如有必要，我会参考或编辑问题）。 我还尝试放置所有查询（将其调整为适用于Informatica SQL语言之后，但没有成功。

So I tried different approch with small successed. The first was to "decompose" the SQL in it's small part(I will refer if necessary or edit the question). I also tried to put the all query (after have adapted it to Informatica SQL "language", but without success.

这是最接近的解决方案，我必须复制这样的查询，而又不认为性能很重要（我确实在SQ中使用了SQL Override），但是从表结果来看，未对连接进行适当处理，所以我认为我需要添加另一个管道来执行按正确的顺序加入：

This is the most close solution that i got to replicate such query, without considerting performance important(I did use an SQL Override in the SQ), but as from the table result, the join is been not propely processed, then I believe I need to add another pipeline to let execute the join in the proper order:

我的映射解决方案：

推荐答案

您可以将子查询放入查找转换，然后匹配来自外部查询的记录（我假设您将按照原始查询中的A.ID = B.ID将其放入zource限定词中，然后稍后过滤空值）

You could put the subquery into a lookup transformation and then match the records coming through from the outer query (which i assume will be what you're putting into your zource qualifier per the A.ID = B.ID from the original query and then filter the nulls later)

可以 类似地，使用2个源限定符（一个用于父查询，另一个用于子查询），然后使用普通联接类型进行联接器转换。这种方式将比我的第一个选项更快地过滤源数据，因此性能上应该更好。

Or you could similarly use 2 source qualifiers (one for the parent query and another for the subquery) followed by a joiner transformation with normal join type. This way will filter your source data sooner than my first option so performance wise should be better.

最终选项是使用带有SQL覆盖的源限定符，与当前查询相同...过滤器直接在数据库中记录出来，所以在那里加号（只要查询本身是最佳的），但是在更广泛的映射上下文中未验证sql覆盖，并且可能产生意外的惊喜，因此应格外小心。

Final option is to use a source qualifier with sql override same as your current query... filters record out right at the database so a plus there (so long as the query itself is optimal) but sql overrides are not validated in the wider mapping context and can spring unexpected surprises so should be used with a lot of caution.