Question

Correct WHERE Clause for Geodatabase Reader

  • 8 April 2014
  • 5 replies
  • 55 views

I'm attempting to limit the number of records being read on multiple file geodatabases by setting the WHERE Clause parameter to yesterday's date:

 

 

Edit_date BETWEEN SUBDATE(CURDATE(),1) AND CURDATE()

 

 

 

"Edit Date" is Field Name and the Data type is Date.  I've tested a few different SQL statements, but can't seem to get the right combination? Is this the right approach or is there a better way?

 

 

Gary

5 replies

Userlevel 4
Hi,

 

 

this is more of an ArcGIS questions than an FME one, as FME transmits the WHERE-clause directly to the underlying database, which is then responsible for parsing it.

 

 

Perhaps try the ESRI forums, if nobody here has a suggestion.

 

 

David
David,

 

 

Thanks for pointing me in the right direction.  I now have another question.  Can you pass the a value from a startup python script (function) into the parameter of a reader?  In other words I want a reader parameter that looks for Yesterday's date and only reads records that where changed the previous day.  I have a python function that sets the correct SQL format:

 

 

def Yesterdays_Date():

 

import datetime, time

 

today = datetime.date.today()

 

day = datetime.timedelta(days=1)

 

yesterday = today - day

 

date_format = yesterday.strftime("%Y-%m-%d")

 

datestring = "Edit_date = date ' " + date_format + "'"

 

return(datestring)

 

 

I just need to get it into the Reader's Where Clause.  Does substituting the parameter require running the fme workbench from within a python script?

 

 

Gary
Userlevel 4
Hi,

 

 

you cannot do this from a startup script, but you can use a scripted Python parameter instead, it'll accomplish the same thing. You can then link this parameter to the reader WHERE-clause:

 

 

 

 

I also corrected your code slightly:

 

 

---

 

import datetime, time

 

today = datetime.date.today()

 

day = datetime.timedelta(days=1)

 

yesterday = today - day

 

date_format = yesterday.strftime("%Y-%m-%d")

 

datestring = "Edit_date = date '" + date_format + "'"

 

return(datestring)

 

---

 

 

Good luck!

 

 

David
David,

 

 

Perfect - works like a charm!  Thanks for your help.

 

 

Gary
Badge +3

For anyone (like me) who needs an answer to the original question and following @david_r's response, the correct syntax for the WHERE clause for a File GDB is:

"STATE_NAME" = 'California'

Note the double and single quotes

 

Or in case of a User Parameter called @(StateName):

"STATE_NAME" = '@(StateName)'

For further info, see ArcGIS Help

Reply