Lookup

[sheema]

I have a job where i have an oracle source Tbl1 and i need to do an lookup with another oracle table Tbl2. But instead of using a hash file,i
would like to use a custom sql and join them.
so i am doing an left outer join.But i see that i get more no of records than the no.of records in Tbl1.Since i am doing an left outer join,i should be replicating the functionality of a Lookup.Am i right.

[urshit_1983]

As you are doing Left outer join its going to take all records of left table i.e TBL1 and only matching records from TBL2 based upon the key.

Instead as you want to do look up you can use TBL2 for lookup directly instead of hashed file, no need to join.

[sheema]

I thougt instead of using the Tbl2 as lookup the performance would be better if i do a left outer join.

[madhukar]

[quote="sheema"]I thougt instead of using the Tbl2 as lookup the performance would be better if i do a left outer join.[/quote]

Check for duplicates. if both the files has duplicates then left outer join produces more rows.

[sheema]

yes,i see that i am getting more no of rows in left outer join than the no .of records in Tbl1.That means there are duplicates.In this case,which option is to be used.

[urshit_1983]

from TBL1 while you are selecting and writing custom SQL use "DISTINCT" and in where clause use "GROUP BY" then the key you are using for join.

[ray.wurlod]

If you WANT the duplicates, do nothing. A simple left outer join will deliver all the duplicates from Tbl1, and mimic the behaviour of DataStage lookup. If you want to remove the duplicates, then an approach such as an inner self-join with the distinct key values would suffice.