Link "A table" state to version

1178
8
Jump to solution
05-10-2019 02:13 PM
forestknutsen1
MVP Regular Contributor

I have a problematic edit that I can see in the "a tables". How can I find the version linked to this edit with sql in an Oracle system?

I cannot find the state in the sde versions table so it is not the most recent state for the version... But the edit is from yesterday afternoon, so it is not super old.

Tags (2)
0 Kudos
1 Solution

Accepted Solutions
BirajaNayak
Esri Contributor

Hi Forest,

This is an expected behavior or you can say by design. When you start edit, for each edit, one state_id created and referenced in state_lineages. Once you save or do reconcile and post, edits are moved to a new state_id and hence previous state_ids are orphaned or unreferenced. Compress helps to remove unreferenced states and their associated delta table rows.

Hope this clarifies your query.

Thanks,

Biraja

View solution in original post

0 Kudos
8 Replies
BirajaNayak
Esri Contributor

Hi Forest,

These are the steps to get the version name or you can write a sql query:

1. Find out state_id for the feature from A table

2. Then look for this state_id in states table , state_lineages table.

3. Get the state_lineages for the feature state_id

4. Similarly get the state_lineages for state_id for versions and compare with state_lineages from step 4 to get the version name for that feature.

Hope that helps.

Thanks,

Biraja

0 Kudos
forestknutsen1
MVP Regular Contributor

Thanks for the feedback Biraja. So, if I am understanding you correctly your are suggesting something like this:

with lina as (select a.objectid, s.lineage_name, s.state_id from a293 a
join sde.states s on s.state_id = a.sde_state_id),
linver as (select s.state_id, s.lineage_name, v.name from sde.versions v
join sde.states s on v.state_id = s.state_id)
select * from lina
full outer join linver on lina.lineage_name = linver.lineage_name‍‍‍‍‍‍

But what about the rows in the "A" table that do not join to a current version lineage? How do I understand what version they were part of?

0 Kudos
BirajaNayak
Esri Contributor

Hi Forrest,

I am sorry and not sure what you are trying to ask in the screen capture. I can see few state_id has state_lineages and then you find the versions. There are few state_id does not present in state_lineages table which are orphan or something and hence no version associated. Is that understanding correct.

Thanks,

Biraja

0 Kudos
forestknutsen1
MVP Regular Contributor

Thanks Biraja

The screen grab is the results of the sql query. And yes your understanding is correct. Is there a way to trace back the states/lineage to find what version the edit was made in for the rows that don't join with the above sql? Or maybe this is just how sde versioning works and there is no way back?

This came up for us because we had some custom code looking at all of the values in the "A" table of a feature class and then blowing up on a bad value (yes the code is of poor quality and should be fixed). So, I wanted to find out the version the bad edit was made in. But I was unable to join it to a version with the above sql as the state/lineage of the version had moved on from the "A" row state/lineage... in the end I was able to track down the version by talking to users. 

0 Kudos
BirajaNayak
Esri Contributor

Please  compress geodatabase and then run the query and let us know if you still find the same issue.

0 Kudos
forestknutsen1
MVP Regular Contributor

Okay, I tested this in the dev environment and as expected all rows now join to a version. We can see this in our user complaints as well. After each compress, the custom code would start to work again. And I could go in and see the that the offending "A" rows that did not join to a version had been removed by the compress operation. So, all working as expected with the compress. 

Is there a way to get the version of the rows that don't join with the above sql before they are compressed away? Or is this something that sde just does not support?

0 Kudos
BirajaNayak
Esri Contributor

Hi Forest,

This is an expected behavior or you can say by design. When you start edit, for each edit, one state_id created and referenced in state_lineages. Once you save or do reconcile and post, edits are moved to a new state_id and hence previous state_ids are orphaned or unreferenced. Compress helps to remove unreferenced states and their associated delta table rows.

Hope this clarifies your query.

Thanks,

Biraja

0 Kudos
forestknutsen1
MVP Regular Contributor

Great! Thanks for clearing this up for me!

0 Kudos