Skip to main content
Question

Read or filter values based on attribute from another source

M
Forum|alt.badge.img

Hi,

I'm reading a bunch of data from a log database, transforming it and storing it somewhere else. I leave the unique ID attribute of the log message alone.

So I want to make it so my workbench only reads the logging rows I haven't processed yet. The ID numbers are ascending so my idea was to check the destination database using SQL to get the highest ID stored in there, and only read/filter from the source based on that.

Now: How do I do this? I can get the current highest ID processing using the SQLCreator, but how do i read/filter based on that?

Thanks!

5 replies

redgeographics
Celebrity
Forum|alt.badge.img+49

You can use a Tester or TestFilter to select the log messages whose ID is higher than the one you got out of the database.

M
Forum|alt.badge.img
  • mvdorst
  • Author
  • May 2, 2018

But when input the SQLCreator and the log database into a tester, there is on row the the max value to test against and a lot of logging rows the ID values. Comparing them doesn't work.

M
Forum|alt.badge.img
  • mvdorst
  • Author
  • May 2, 2018
redgeographics wrote:

You can use a Tester or TestFilter to select the log messages whose ID is higher than the one you got out of the database.

But when input the SQLCreator and the log database into a tester, there is on row the the max value to test against and a lot of logging rows the ID values. Comparing them doesn't work.

 

 

redgeographics
Celebrity
Forum|alt.badge.img+49

If you do a featurejoin you can add the id from the database to all records

takashi
Influencer
  • takashiInfluencer
  • May 5, 2018

If the two database tables belong to the same database, I think you can extract only required records from the source table with a single SQL statement something like this.

select * from source where (select max(id) from destination) < id

Alternatively, you can also set this expression to the WHERE Clause parameter in the reader which reads the source table.

(select max(id) from destination) < id

Reply


Helpful Members This Week

Community Stats

31,317
Posts
118,810
Replies
38,946
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

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