Regarding Join Condition

Diya · Post by **Diya** » Tue Feb 28, 2006 6:32 am

Hi All,

I need to join 2 tables based on one key coloumn.
I need to populate a value for a perticular coloumn based on matched records i.e one type of value for matched records
and different value for unmatched records.

ex:If the records in two tables satisfy the join condition then

for matched records
location

usa

Else,

for unmatched record

location

uk

Could you please help out on this

Thanks in advance,

I_Server_Whale · Post by **I_Server_Whale** » Tue Feb 28, 2006 7:37 am

Hi Diya,

You can achieve this by loading one of tables into hashed file as a reference table.

Now, use one native DB stage or ODBC stage for one table, a transformer, a hashed file (as said above, which contains data from the other table) and one target stage (can be a seq. or hashed file, depends on your needs).

Check if the key exists (FOUND) in the hashed file. If not then populate your column with UK else populate with US.

Hope that helps.

Thanks,
Naveen.

Diya · Post by **Diya** » Tue Feb 28, 2006 7:48 am

Hi Naveen

Thanks a lot for quick response,i am developing in parellel jobs.
hash file is not availble in parelle stages.i suppose.

can you explain me how to achieve in parellel jobs.

regards,
vinaya

elavenil · Post by **elavenil** » Tue Feb 28, 2006 9:40 am

Do outer (left/right) join of those two tables and check the other table values to identify (un)matching records then populate the column that you want to derive.

HTWH.

Regards
Elavenil

amsh76 · Post by **amsh76** » Tue Feb 28, 2006 11:50 am

It depends on your tables..

Out of two tables can we say that one will have all the records for that key ? Or Keys are divided among two tables ?

If its the first case, then you can use Inner Join or Even lookup stage, but if its the second scenario than you will have to do left or Right outer join.

IHTH..