Question

Read or filter values based on attribute from another source

M
Badge

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
Rank
Userlevel 5
Badge +25

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
Badge

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
Badge

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
Rank
Userlevel 5
Badge +25

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

takashi
Rank
Userlevel 2
Badge +17

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


Community Stats

28,670
Posts
109,365
Replies
36,531
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.