Use the INFOR.LASTMODIFIED() function in a WHERE clause for an incremental data load

Use the infor.lastmodified() function in the WHERE clause of a query to filter results to a subset of records.

The infor.lastmodified() function can be used for incremental data loads, to select only data that was loaded to the Data Lake since the previous update date.

The infor.lastmodified() function supports these operators:

  • =
  • >=
  • <=
  • BETWEEN

The timestamp value in a WHERE clause can be a timestamp in UTC in ISO8601 format with three milliseconds: YYYY-MM-DDThh:mm:ss.sssZ. It can also be a date or a timestamp that does not include milliseconds.

Examples 

select property1,property2 from object where infor.lastmodified()>= '2021-09-01T09:30:47.434Z'

Example for a query using multiple data objects: 

select do1.property1, do2.property5, infor.lastmodified('do1') from object1  do1 inner join object2 do2 on do1.property1=do2.property1 where infor.lastmodified('do2') >='2021-09-01T09:30:47.434Z'
Note: From Infor OS version 2023.02, the infor.lastmodified() function has been corresponding to dl_document_indexed_date for new data objects. Before that, the function corresponded to dl_document_date for old data objects. infor.lastModified() returns a timestamp that is related to dl_document_date or dl_document_indexed_date, depending on when the data object is entered into the system.