Skip to main content
  • EN

I am trying to use the SQLExecuter to identify records from an archived ArcSDE database where the ID does not exist in another table by utilizing the _matched_records output. My SQL Statement is this:

Select SCRD_ID from gis.STREET_CENTERLINE where SCRD_ID = '@Value(SCRD_ID)'

I know that SCRD_ID: 3745 does not exist in the STREET_CENTERLINE table, but because this data is archived it is coming back with a _matched_record count of 2 instead of 0

Does anyone know how to only read/query the CURRENT data from the ArcSDE database?

I'm not sure which RDBMS your geodatabase is running on, but I can speak for my experience with MS SQL Server. There should be a couple of columns on your Street_Centerline table, GDB_FROM_DATE and GDB_TO_DATE. These store the date and time period that each particular set of values for the row existed. The default GDB_TO_DATE (in SQL Server at least) is '9999-12-31' for currently active rows. You should be able to add to your where clause to your select like this.

Select SCRD_ID from gis.STREET_CENTERLINE where SCRD_ID = '@Value(SCRD_ID)' AND GDB_TO_DATE = '9999-12-31'

FWIW, in MS SQL Server, I typically also see a view named gis.STREET_CENTERLINE_EVW which has this GDB_TO_DATE filter applied already to return only the active rows, so that may be another option for you.

I'm not sure which RDBMS your geodatabase is running on, but I can speak for my experience with MS SQL Server. There should be a couple of columns on your Street_Centerline table, GDB_FROM_DATE and GDB_TO_DATE. These store the date and time period that each particular set of values for the row existed. The default GDB_TO_DATE (in SQL Server at least) is '9999-12-31' for currently active rows. You should be able to add to your where clause to your select like this.

Select SCRD_ID from gis.STREET_CENTERLINE where SCRD_ID = '@Value(SCRD_ID)' AND GDB_TO_DATE = '9999-12-31'

FWIW, in MS SQL Server, I typically also see a view named gis.STREET_CENTERLINE_EVW which has this GDB_TO_DATE filter applied already to return only the active rows, so that may be another option for you.

Thanks for this info!

 

We are running on an Oracle RDBMS and by reading in the views did the trick!

 

 

One thing was when I added the reader, the views are not included in the list of tables, so I just entered the view name manually gis.STREET_CENTERLINE_EVW and it read the data fine.

 

Thanks!

Reply


Community Stats

31,821
Posts
120,861
Replies
39,452
Members

Latest FME

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.