Skip to main content
  • EN

I am having a hard time figuring out how to make this process work. Basically I'm checking for dateconditions to determine whether records from one table should be updated to match the same records in another table .

I am using SQL executor to determine what the latest date is in TableA and TableB and comparing the dates to each other. Using the Matcher, I determine that if they match, the Workspace can end there. If they DON'T match, then I want the workspace to continue, using the date value from TableB as a variable in order to select everything from TableB that has that date or later and match it up to TableA to set the colum values in TableA = to the colum values in TableB.

I don't know how to select from the NotMatched the date from TableB only, and then to re-use that date to run another selection against TableB to select everything that should be used to update TableA.

I hope this makes sense.

Thanks for your consideration!

You can create an attribute containing the source name, and use a tester on the NotMatched port to test the attribute for TableB.

You could either tweak your sql to create an attribute referencing the table name

e.g.

select max(date) as max_update_date, 'TableB' as table_name from TableB

select max(date) as max_update_date, 'TableA' as table_name from TableA

or

add an attributecreator after each executor to create this attribute

Test your non-matched output to see if table_name = TableB which will give you the date you require which you can then use within an sql executor using @Value(max_update_date)

Hi @alexk, how about using the ChangeDetector? Send the TableA feature to the Original port, send the TableB feature to the Revised port, and compare their MAX_UPDATE_DATE. If their values are different, the TableB feature will be output from the Added port.

Alternatively, if the two tables belong to the same database, the SQLCreator with this SQL statement outputs all the TableB records which have the latest update date, only when the date doesn't match the latest update date in the TableA.

select * from TableB
where update_date = (select b.x
    from (select max(update_date) as x from TableA) as a
    inner join (select max(update_date) as x from TableB) as b
    on a.x <> b.x)

Are the tables residing in the same database?

Then why not do the table compare using sql (with . select.. case .. when .. else)

Or

It is often possible to give the SQL_Querier a more discriptive name and then use an attributeexposer to expose fme_feature_type. THis then will hold the name of the SQL_querier (mind you if the SQL querier is inside a custom, the custom name will be appended)

This means you can test the exposed attribute.

Or just create a attribute at one of the SQL_executors outputports and test that.

Or create attribute inside the sql inside one of the SQL-executors.

Reply


Community Stats

30,114
Posts
114,307
Replies
37,746
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.