Query Layer is returning branch version history

AmyRoust

I know I'm missing something obvious. I created a query layer with a subquery to show poles that have associated records in my sign table. Because the pole feature class is branch versioned, the SQL query is returning as many as five records for a single pole because there is historical data sitting in the table. AI suggested adding the where clause GDB_IS_DELETE = 0, but still returns multiple records for the same pole.

I remember a while back needing to write these queries against the _EVW table, but maybe that was traditional versioning? 

joyoung_kerrvilletx

This is what I had to use to create a database view in SQL Server for one of my branch version feature classes to return the "parent" record. It just worked in a query layer for me.

SELECT OBJECTID, SHAPE, FIELD1, FIELD2, FIELD3 
FROM SCHEMA.TABLENAME
WHERE FIELD1 = 'INSERTFIELDTEXTHERE' AND SCHEMA.TABLENAME.GDB_ARCHIVE_OID IN
  (Select MB_.GDB_ARCHIVE_OID
   From
     (SELECT GDB_ARCHIVE_OID,
         ROW_NUMBER() OVER (PARTITION BY OBJECTID
         ORDER BY gdb_from_date DESC) rn, gdb_is_delete
      FROM SCHEMA.TABLENAME
	  WHERE (gdb_branch_id = 0 AND
              gdb_from_date <= '12.31.9999 23:59:59.000')) MB_
    WHERE rn = 1 AND gdb_is_delete = '0' )

I wish I could remember who originally posted this SQL because I definitely did not figure this out on my own.