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.

[sapuserapr2021]

Hi Gabriel,

One solution is to add a Table_Comparison transform into your dataflow. Here is an example:

Notice the CADDOCMASTER_Compare transform. In there you can specify a table to compare to which in your case would be the target table you are populating. You can also specify one or more columns that will provide uniqueness for a key. If you already have a unique key column you would just use that. You also specify compare columns that will be used to detect differences. If a recode comes in that has the same key values, it will update that row assuming that one or more of the compare columns are different. If the key values don't match any record already in the table, a new row is inserted. You can also have the comparison delete records if you want. Here's a picture of the Compare transform in the example above:

I hope this is of some help.

Dave

[dunncrew]

Thank you. I will try that.

[dunncrew]

I have an alpha field as key, so getting an error due to data type. I will have to try something else.

Generated key column <TRACEID> for table <AMGIMP01GD> must be of type REAL, FLOAT, DOUBLE, INTEGER, DECIMAL, or NUMERIC.