Skip to main content
Solved

Working with Archived Non-Versioned ArcSDE data in a SQLExecuter

  • November 6, 2018
  • 2 replies
  • 37 views
avansickle
Contributor
Forum|alt.badge.img

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.

    This post is closed to further activity.
    It may be an old question, an answered question, an implemented idea, or a notification-only post.
    Please check post dates before relying on any information in a question or answer.
    For follow-up or related questions, please post a new question or idea.
    If there is a genuine update to be made, please contact us and request that the post is reopened.

    2 replies

    P
    Forum|alt.badge.img
    • paulbrandt73
    • 13 replies
    • Best Answer
    • 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.

    avansickle
    Contributor
    Forum|alt.badge.img
    • avansickleContributor
    • Author
    • Contributor
    • 1 reply
    • 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.

    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!

    Community Stats

    32,084
    Posts
    121,835
    Replies
    39,721
    Members

    Latest FME

    Terms & ConditionsCookie settingsAccessibility statement
    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.