I have two versions, from same base table, with only one feature class in both that I want to combine in a database view for a presentation. Version feature classes differ both with attributes and geometry but are children of a same parent. I wrote a SQL query that worked:
CREATE OR ALTER sde.CominedViews AS SELECT *, ROUND(Shape.STLength(), 1) AS [CalcLength] FROM [sde].[ATableForABaseTable] WHERE SDE_STATE_ID IN (SELECT state_id FROM SDE.SDE_versions WHERE [name] = 'VERSIONNAME1') UNION ALL SELECT *, ROUND(Shape.STLength(), 1) AS [CalcLength] FROM [sde].[ATableForABaseTable] WHERE SDE_STATE_ID IN (SELECT state_id FROM SDE.SDE_versions WHERE [name] = 'VERSIONNAME2')
However, on some occasions, when the user does updates, one of the versions can not found state_id in a A delta table. I have checked A and D delta tables and SDE_version. In this example, SDE_versions table have state_id 15360 assigned and update that user did is in A delta with state_id 15356 assigned (One that it should be shown). D table does increases this number but highest value in SDE_STATE_ID is 1537, still not the one I am looking for from SDE_versions. Where this state_id in SDE_versions could came from?
I thought it might be not saved updates, but shouldn't then that number be higher than SDE_versions number for a particular version? And if I open that version in ArcGIS, it is shown as it should, updated.
Compress is not run in the meantime. All changes are done by the SDE owner. Data is registered as traditionally versioned without archiving.
Do you have any tips?