Which Lookup should I Use?

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

mohamedosharif
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.

cholastal
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

Hi,

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
CloverDX

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


cron