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? 

[Joshua-Young · Accepted solution]

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. 

[tmichael_wpjwa]

This article has examples for branch version SQL views:
https://community.esri.com/t5/arcgis-utility-network-documents/branch-version-sql-views-utility-netw...

[AmyRoust]

@Joshua-Young - thank you. I had to add that long WHERE clause twice - once to get the "parent" table for the point feature class and once to get the "parent" table for the table I was joining to the point feature class. But it worked and the count FINALLY matches what I expected.

@tmichael_wpjwa - Thank you for that link.