Solved

FeatureReader - Reading in records based on the LAST_UPDATED date field

  • 14 December 2018
  • 3 replies
  • 74 views
juliarozema
Rank
Badge +6

I am using a FeatureReader to connect to our SDE GDB. I want to add a query (WHERE Clause) to the FeatureReader so that I am only pulling in records within the Feature Class that were edited within the last month (or sometimes edited in the last 5 days).

When I look at the SDE data via MS SQL Server Management Studio (SSMS) vs within FME's Data Inspector I see the data in different formats:

Within SSMS - LAST_UPDATED field is a DATE field:

 

last_updated (datetime2(7), null)

 

2018-12-01 16:23:56.0000000

Within Data Inspector - LAST_UPDATED field is STRING field:

 

last_updated (string)

 

20181128133118

My questions:

1) is there a way to specify which attributes I want to be brought in? Right now I am using an attribute manager to remove the unwanted attributes.

2) How do I only at a WHERE Clause so that I only pull in the data I want within the specific time range? I have tried "last_updated" > '20180500000000' but this brings in nothing. I am assuming this is because FME is treating the field as a string? If this is true, then...

3) How do I get FME to see this field as a date field while using the FeatureReader?

4) Once I am able to work with this data as a date, how do I do a WHERE Clause so that I only pull data that has been edited within the last 5 days? I will be running this query everyday, so I want the query to be something like last_updated >= dateadd(DAY,-5, GETDATE())

 

I am using FME(R) 2018.1.0.2 (20180903 - Build 18547 - WIN64)

 

Also tried a WHERE Clause of @DateTimeCast("last_updated",datetime) > '20181201000000' but get the following error:

 

The error number from ArcObjects is: '-2147216072'. The error message from ArcObjects is: {Underlying DBMS error [[Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '>'
icon

Best answer by fmelizard 15 December 2018, 23:17

View original

3 replies

fmelizard
Rank
Userlevel 4
Badge +13

Hi Julia,

Sorry for the troubles.

RE: The Where clause -- that will be passed straight to the underlying database, so the syntax you'd need to use would be whatever that database likes. I'm guessing (but you'd need to try) that you'd need to specify the date using the database's lingo. So something like: "last_updated" > '2018-05-00 00:00:00'

You'd need to do the date math ahead of time I think and get the string you'd like and then use that in the Where clause definition -- I think that would be easier:

No idea if that will work for you but I'd give it a try. (You'd put last_updated > @Value(_result_datetime) in the FeatureReader Where clause after connecting the DateTimeConverter)

As for reading just what you want read, sadly the FeatureReader doesn't have this magic yet -- do vote over at https://knowledge.safe.com/idea/45144/featurereader-select-attributes-to-read.html

If it was a big problem you could change your plan to just use a normal "reader" (and then use a scripted parameter to calculate your date string) -- then you'd be able to pick attributes to read. OR create a View in your database outside of FME, and have the FeatureReader read the view instead of the real table...but if you're using SDE I don't know if that is an option.

ebygomm
Rank
Userlevel 1
Badge +21

If you want to use a date without converting from FME format you can use something like the following for your where clause if you've already created the date you want to compare against

FORMAT(last_edited_date, 'yyyyMMdd') >= @Value(last_5_days)

or for a datetime

FORMAT(last_edited_date, 'yyyyMMddHHmmss') >= @Value(last_5_days)
juliarozema
Rank
Badge +6

Hi Julia,

Sorry for the troubles.

RE: The Where clause -- that will be passed straight to the underlying database, so the syntax you'd need to use would be whatever that database likes. I'm guessing (but you'd need to try) that you'd need to specify the date using the database's lingo. So something like: "last_updated" > '2018-05-00 00:00:00'

You'd need to do the date math ahead of time I think and get the string you'd like and then use that in the Where clause definition -- I think that would be easier:

No idea if that will work for you but I'd give it a try. (You'd put last_updated > @Value(_result_datetime) in the FeatureReader Where clause after connecting the DateTimeConverter)

As for reading just what you want read, sadly the FeatureReader doesn't have this magic yet -- do vote over at https://knowledge.safe.com/idea/45144/featurereader-select-attributes-to-read.html

If it was a big problem you could change your plan to just use a normal "reader" (and then use a scripted parameter to calculate your date string) -- then you'd be able to pick attributes to read. OR create a View in your database outside of FME, and have the FeatureReader read the view instead of the real table...but if you're using SDE I don't know if that is an option.

Thank you for these thoughts Dale. I like the idea of the DateTimeCalculater followed by the DateTimeConverter going into the FeatureReader. I have tried it and it works.

Solutions for each of my questions:

1. I will wait for this feature in a future version :)

2. Database syntax is the key. The query works if I use the following WHERE Clause: "last_updated" >= '2018-05-01 00:00:00'

3. FME IS seeing this field as a date field, but works with it as a FME Format or a string within the workbench. When reading or writing the data field is a date.

4. I used Dale's suggestion of a DateTimeCalulator and then a DateTimeConverter. The newly created variable (_last_5_days) from the DateTimeConverteris is then used in the WHERE Clause of the FeatureReader: "last_updated" >= '@Value(_last_5_days)'

Reply


Community Stats

28,711
Posts
109,487
Replies
36,559
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.