Select to view content in your preferred language

Query Branch Versioned Parcels

1166
0
05-12-2021 10:47 AM

Query Branch Versioned Parcels

In our "What's New in 2.8" meetup, I demonstrated using a database view to display parcel fabric features joined with an external table.  I didn't spend much time on the SQL so check out the code block and link below for examples.  Please be aware that INSERT, UPDATE and DELETE functionality is not available.

https://github.com/Esri/developer-support/tree/master/arcsde-sql/sql-server/branch-versioning

SELECT 
  a.NAME, 
  a.objectid,
  b.description_, 
  b.address_, 
  b.owner_,  
  a.shape, 
  a.gdb_geomattr_data 
FROM 
  gis.tax_4 a
LEFT JOIN gis.fake_cama b  -- keep all parcels (null external rows ok)
  ON a.name = b.Name

-- Start branch versioning magic
WHERE a.GDB_ARCHIVE_OID IN (
    SELECT GDB_ARCHIVE_OID 
    FROM 
      (
        SELECT 
          GDB_ARCHIVE_OID,  
          ROW_NUMBER() OVER( PARTITION BY OBJECTID ORDER BY GDB_FROM_DATE DESC ) AS rn_, 
          GDB_IS_DELETE 
        FROM GIS.TAX_4 
        WHERE GDB_BRANCH_ID IN (0)
          AND GDB_FROM_DATE <= GETUTCDATE()
          AND OBJECTID IN (
            SELECT OBJECTID 
            FROM GIS.TAX_4 
			WHERE (RetiredByRecord IS NULL)
          ) 	  
      ) AS br__ 
    WHERE 
      br__.rn_ = 1 
      AND br__.GDB_IS_DELETE = 0 
	) 
-- End branch versioning magic

  -- Filter from sub result set
  AND a.RetiredByRecord IS NULL
  AND a.IsSeed <> 1

 

 

Version history
Last update:
‎05-12-2021 10:47 AM
Updated by: