We load a flat file into our database, the flat file has a unique column flat_file.Column_1 which we use to check against the database (database_table.Column_2) to see if the information already exists. I want to do a full outer join, so basically if flat_file.Column_1 = database_table.Column_2, i don't want to load the data, i only want to load the data where flat_file.Column 1 != database_table.Column 2.
I'm not sure if there's a way to do a lookup and just return the values that aren't equal. I was thinking about doing a full outer join and creating a condition after the join that only passes through null values for Column 2.
However, I'm having trouble using the LookupJoin(assuming that is the right one i should be using). I defined the lookup key, and lookup table.
Query for lookup table
Lookup Key: Flat_file.Column_1
Where in the lookup do i define the sql?
basically want this;
::Disclaimer:: I come from an Informatica background and thus the sql above is similar to what we'd use in a lookup.
Any help is appreciated.
I'm not sure if there's a way to do a lookup and just return the values that aren't equal. I was thinking about doing a full outer join and creating a condition after the join that only passes through null values for Column 2.
However, I'm having trouble using the LookupJoin(assuming that is the right one i should be using). I defined the lookup key, and lookup table.
Query for lookup table
Select database_table.Column_2
from Database_table;Lookup Key: Flat_file.Column_1
Where in the lookup do i define the sql?
basically want this;
select * from Database_table
where Database_table.Column_2=flat_file.Column_1::Disclaimer:: I come from an Informatica background and thus the sql above is similar to what we'd use in a lookup.
Any help is appreciated.
-
Hi,
I would rather suggest a solution where you would use a DBInputTable to search the database table for:select * from Database_table
where Database_table.Column_2=flat_file.Column_1
and if it returns zero records, write the flat file into the DB.
Searching in the database should be quite a lot faster than loading all the data from it into the lookup table and searching that. Please correct me if misunderstood your scenario.
Best regards.
Please sign in to leave a comment.
Comments 1