DataFlow WHERE Clause - Check Another Table ?

[#1]

I am doing a simple Dataflow transform from .CSV to SQL table, but sometimes the vendor sends the same file twice, and we are loading duplicate records.

In the dataflow WHERE clause, is the SQL table target "in scope" to check if records already exist ?

I want to add something like

WHERE IMPORTED_ID NOT IN ( Select LOADED_ID FROM EXISTING_TABLE )

If not, what's another way to check if records already exist in the target or another table ?

[werner_daehn · Accepted solution]

You can use a lookup (or lookup_ext) function in the where clause and the lookup allows to lookup values in other tables.

So my where clause would be in your case:

IMPORTED_ID <> lookup (....EXISTING_TABLE, LOADED_ID, -1, PRE_LOAD_CACHE, LOADED_ID, IMPORTED_ID)

The complete details I had written up here:

https://wiki.scn.sap.com/wiki/pages/viewpage.action?pageId=272532003

[dunncrew · Accepted solution]

I think LOOKUP works for me. Took me a minute to find I needed single quotes around 'PRE_LOAD_CACHE'

[werner_daehn · Accepted solution]

gdunn Best will be to create that lookup via the function editor, then it is filled out correctly. I believe the PRE_LOAD_CACHE must be quoted but I can't test it at the moment myself.

But frankly, as I wrote in the BODS Wiki, I would rather have a first query where you add one more column - the lookup_ext result. And then another query where you filter on the fact if that lookup found something or not. This wiki page has lots of screenshots also.