Which Lookup should I Use?

Support/help with CloverETL (4.9) and CloverDX (5.0 or newer) implementation problems

Posts: 18
Joined: Tue Dec 11, 2018 9:19 pm

Which Lookup should I Use?

Postby mohamedosharif » Wed Jan 23, 2019 4:53 pm

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

Code: Select all

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;

Code: Select all

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.

Posts: 134
Joined: Tue Sep 01, 2015 1:22 pm

Re: Which Lookup should I Use?

Postby cholastal » Fri Feb 01, 2019 4:07 pm


I would rather suggest a solution where you would use a DBInputTable to search the database table for:

Code: Select all

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.

Lukas Cholasta
CloverCARE Support

Visit us online at http://www.cloverdx.com