Mamta11

 Profile Answers by Mamta11 

• Jul 26th, 2008

 

If you write a SQL override in lookup transformation its a lookup override.

raj_infadev

 Profile Answers by raj_infadev 

• Oct 13th, 2008

 

Obviously we have some differences between those 2 Overrides...

1. SQL Override is to limit the number of incoming rows entering the mapping pipeline
    Lookup Override is to limit the number of lookup rows to avoid the whole table scan by saving the lookup time & cache it uses...

2. Lookup Override uses "Order By" clause by default
    SQL Override doesn't uses it & should provide in our query if we require it

3. SQL Override can provide any kind of join by writing the query
    Lookup Override provides only Non-Equi joins

4. Lookup Override gives only one record even if it finds multiple records for a single condition
    SQL Override doen't do that...

Let me know if m wrong in any point....

Regards
Raj

dbseeker

 Profile Answers by dbseeker 

• Dec 20th, 2015

 

Can we have a different opinion on the 4th point?
"Lookup override gives only one record even though there..."
A lookup can return more than one record. We could handle this using the lookup property - "Lookup Policy on Multiple Match".

To handle all the rows that the lookup returns, we need to create the lookup transformation in the mapping using - "Return All values on Multiple Match" property enabled. Once you enable this property and create the transformation, the "Lookup Policy on Multiple Match" property will only have one option (selected by default) - "Use all Values".

VENKATM

• Jan 2nd, 2016

 

You cannot use directly Union in Lookup Override. You can use in SQL Override. No join conditions in SQL Override. Join condition is compulsory in lookup.