Solved

Working with Archived Non-Versioned ArcSDE data in a SQLExecuter

6 years ago
November 6, 2018
2 replies
19 views

avansickle
1 reply

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?

Best answer by paulbrandt73

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.

View original

Did this help you find an answer to your question?

P

paulbrandt73
13 replies
Best Answer
6 years ago
November 7, 2018

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.

A

avansickle
Author
1 reply
6 years ago
November 7, 2018

paulbrandt73 wrote:

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!