top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

What are the differences between Connected and Unconnected Lookup?

+1 vote
380 views

What are the differences between Connected and Unconnected Lookup?

posted Feb 18, 2015 by Amit Sharma

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

Connected Look Up:

Receives input values directly from the pipeline.

Use a dynamic or static cache.

Cache includes the lookup source columns in the lookup condition and the lookup source columns that are output ports.

If there is no match for the lookup condition, the Integration Service returns the default value for all output ports. If you configure dynamic caching, the Integration Service inserts rows into the cache or leaves it unchanged. 

If there is a match for the lookup condition, the Integration Service returns the result of the lookup condition for all lookup/output ports. If you configure dynamic caching, the Integration Service either updates the row the in the cache or leaves the row unchanged.

Pass multiple output values to another transformation. Link lookup/output ports to another transformation.

Supports user-defined default values.

Unconnected Look Up:

Receives input values from the result of a :LKP expression in another transformation.

Use a static cache.

Cache includes all lookup/output ports in the lookup condition and the lookup/return port. 

Designate one return port (R). Returns one column from each row. 

If there is no match for the lookup condition, the Integration Service returns NULL.

If there is a match for the lookup condition, the Integration Service returns the result of the lookup condition into the return port.

Pass one output value to another transformation. The lookup/output/return port passes the value to the transformation calling :LKP expression.

Does not support user-defined default values.
answer Feb 23, 2015 by Manikandan J
Similar Questions
+2 votes
Informatica: In unconnected lookup what are other transformations that can be used in place of expression transformation

In unconnected lookup, what are the other transformations that can be used in place of that expression transformation?

+1 vote
Informatica Unconnected Lookup parse error

I am calling an unconnected lookup in Informatica and I am getting this error:

ERROR : Lookup port rfb_id in lkp_V_CLAIM_ELIGBLTY_EFF_LTCG_seqid not specified in lookup override
.
.
.

Transformation Parse Fatal Error; transformation stopped...
TE_7002 [<<PM Parse Error>> [:LKP.RLKP_V_CLAIM_ELIGBLTY_EFF_LTCG_SEQID(rfb_id)]: : invalid function reference
... >>>>:LKP.RLKP_V_CLAIM_ELIGBLTY_EFF_LTCG_SEQID(rfb_id)<<<<]

My lookup is as follows

Ports:

in_rfb_id [Input]
rfb_id [Lookup]
CLAIM_ELIG_EFF_LTCG_SEQ_ID [Output, Lookup, Return]
SQL Override:

select 
   max(CLAIM_ELIG_EFF_LTCG_SEQ_ID) as CLAIM_ELIG_EFF_LTCG_SEQ_ID
from
   $$FAR_CR_VW.V_CLAIM_ELIGBLTY_EFF_LTCG
   -- where rfb_id = in_rfb_id
 group by rfb_id  --
Condition:

rfb_id = in_rfb_id

Other information:

data types are the same between lookup and expression, both integer
SQL returns a one column list of 962 integers

+1 vote
What are Difference between Relational Lookup and Flat File Lookup Transformation?

When you create a Lookup transformation using a relational table as a lookup source, you can connect to the lookup source using ODBC and import the table definition as the structure for the Lookup transformation.
where as When you create a Lookup transformation using a flat file as a lookup source, select a flat file definition in the repository or import the source when you create the transformation. When you import a flat file lookup source, the Designer invokes the Flat File Wizard.

I found an interesting article on this topic at below links , It quite descriptive

http://www.tecktricks.com/difference-between-relational-lookup-and-flat-file-lookup-transformation-in-informatica/

...