0

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?

2
  • You've got a number of issues here, but most critically, you're ignoring the impact of deletions on the state tree (and that the state tree is a tree). What you seem to want violates the design of the versioning model, so it's not surprising it doesn't work well. You've also violated best practice by using the SDE user to own spatial data (which increases the risk of destroying your geodatabase instance with a single SQL statement). There are tools that expose versioned tables for SQL use, but nothing that works with multiple versions at the same time. I'm afraid you're on your own here. – Vince Jan 31 at 12:46
  • Hi Vince, thanks for a comment. I am pretty aware of the issues you stated here, but whole context of the application is pretty complicated, part of the bigger system, out of my control. However, I thought it would be interesting to try doing something lime mixed view(ofc without edit possibility, it's a view). I think ignoring delete tables in a query is ok because I know only one element will be updated whole time. Let's see, wish me a luck with this :D – Kumbra Jan 31 at 19:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.