Skip to main content
Question

Correct WHERE Clause for Geodatabase Reader


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

david_r
Celebrity
  • April 10, 2014
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

  • Author
  • April 10, 2014
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

david_r
Celebrity
  • April 11, 2014
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

  • Author
  • April 11, 2014
David,

 

 

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

 

 

Gary

arnold_bijlsma
Enthusiast
Forum|alt.badge.img+14

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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings