Lookup Transformation

Overriding the Lookup Query

Lookup Transformation 的 Lookup Query Override不同于Source Qualifier Transformation的SQL Query：

Lookup Transformation必须明确标明SQL中的列名as  COLUMN_NAME,不能用select * ，

而Source Qualifier Transformation可以用select * ，只要列个数和列顺序正确就可以；

Lookup Transformation不需要列和Ports数目一样，也不需要顺序一样，只要sql中的as列名完全一致就可以（区分大小写）。

 

Lookup Query Override： select  1 as COn1,max(a.oRG_wID)  as OrG_WId1  from Wc_POSTN_COACH_a_TEST1 a--

 

Overriding the ORDER BY Clause

For example, a Lookup transformation uses the following lookup condition:
ITEM_ID = IN_ITEM_ID
PRICE <= IN_PRICE

SELECT ITEMS_DIM.ITEM_NAME, ITEMS_DIM.PRICE, ITEMS_DIM.ITEM_ID FROM ITEMS_DIM ORDER BY
ITEMS_DIM.ITEM_ID, ITEMS_DIM.PRICE --

To override the default ORDER BY clause for a relational lookup, complete the following steps:
1. Generate the lookup query in the Lookup transformation.
2. Enter an ORDER BY clause that contains the condition ports in the same order they appear in the Lookup
condition.
3. Place two dashes ‘--’ as a comment notation after the ORDER BY clause to suppress the ORDER BY clause
that the Integration Service generates.
If you override the lookup query with an ORDER BY clause without adding comment notation, the lookup fails.