If ResultID is negative then I would expect your SQL to test 'where a.ResultID < :sql_last_value' (< instead of >). I believe 0 is the default initial value when you use a numeric tracking column.
@Badger They started the numbering of the primary field from maximum negative so as to get maximum range.
The rough representation is:
-11, -10, -9, -8, -7
If I keep a.ResultID < :sql_last_value, then at end of first run, :sql_last_value will have value -7.
Then the second run will also ingest the first 4 elements and :sql_last_value will have value -8.
Two things I have noticed:
a.ResultID > :sql_last_value leads to this in logs where a.ResultID > 0. It remains unchanged between the queries.
I tried to get around it by creating an alias.
statement => "select a.ResultID, a.ResultID * (-1) as resid , a.UnitIdentifier, b.Result from MyDB.Res a inner join MyDb.ResData b on a.ResultID=b.ResultID where a.ResultID > :sql_last_value and a.Code like '9840%' and a.CreatedOn > '2020-01-01' order by resid ASC"
tracking_column => "resid"
use_column_value => true
tracking_column_type => "numeric"
This gives error: Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::ComMicrosoftSqlserverJdbc::SQLServerException: Invalid column name 'resid'.
The query runs fine in my Microsoft SQL Server Management Studio.
EDIT 1
Just read through this.
Bottomline: column_alias can be used in an ORDER BY clause, but it cannot be used in a WHERE, GROUP BY, or HAVING clause .
EDIT 2
I am trying out this logic:
statement => "select a.ResultID , a.UnitIdentifier, b.Result from MyDB.Res a inner join MyDb.ResData b on a.ResultID=b.ResultID where a.ResultID < :sql_last_value and a.Code like '9840%' and a.CreatedOn > '2020-01-01' order by a.ResultID DESC"
This means that the data is pulled in reverse chronological order:
Dec, Nov, Oct, Sept......
This is fine by me. As long as the whole data comes in it is fine.
But I am concerned if I have turned the logic of incremental pulling of future data from database on it head.
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.