SQL Select on a Versioned SDE Layer
SDE has a fairly complicated table structure. A versioned layer will have five tables (business, spatial, index, add, and delete tables), along with references in all kinds of other tables in the SDE database. A select on the business table will not retrieve a correct recordset on a versioned layer, as changes to the layer are stored in the add and delete tables and not in the business table itself. While all of that complexity arguably enables a lot of functionality, it makes writing a simple select statement on a layer a major pain.*
First, you need to figure out the registration ID of your layer. That information is stored in the sde_table_registry in your SDE database.
select registration_id, database_name, table_name, owner from sde.sde.sde_table_registry where table_name = ‘library_t’
Your registration id is a number SDE will tack on to the non-business table tables. For example, if your layer registry was 109, your add table would be a109. I generally get the other fields so I can be doubly sure I’m looking at the right layer.
That’s what we need to write a SQL select statement to get a correct recordset of our versioned layer. We’re assuming here that your library_t layer has a registration id from the last step of 107.
select objectid, shape, name, address from gis.sde.library_t l
where not exists (select * from gis.sde.d107 d where l.objectid = d.sde_deletes_row_id)
union select objectid, shape, name, address from gis.sde.a107
What we’re doing is a select statement in three parts. First, we get the fields we want from the business table (no select * here - we’ll need the list of fields later in the statement). Next, we remove all of the records from the business table that have object id’s in the delete table, as those records have been removed. Finally, we tack all of the records from the add table onto the bottom of the main business table results minus the deletes.
That’s it - this SQL will get you a proper recordset from a versioned SDE layer. If it’s something you’re going to need to do a lot, you’ll probably want to put it in a web service so you won’t have to write all of that code every time.
*As a comparision - PostGIS has one table for each layer (the geometry is stored in a text field in the business table itself), and one table that references all of the layers. Simplicity at its finest.