topic Re: Link "A table" state to version in Geodatabase Questions

Link "A table" state to version

forestknutsen1 — Fri, 10 May 2019 21:13:25 GMT

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.

Re: Link "A table" state to version

BirajaNayak — Tue, 14 May 2019 00:56:28 GMT

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

Re: Link "A table" state to version

forestknutsen1 — Sun, 12 Dec 2021 08:35:41 GMT

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?

Re: Link "A table" state to version

BirajaNayak — Wed, 15 May 2019 22:19:39 GMT

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

Re: Link "A table" state to version

forestknutsen1 — Thu, 16 May 2019 00:16:45 GMT

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.

Re: Link "A table" state to version

BirajaNayak — Fri, 17 May 2019 00:52:38 GMT

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

Re: Link "A table" state to version

forestknutsen1 — Mon, 20 May 2019 18:14:06 GMT

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?

Re: Link "A table" state to version

BirajaNayak — Tue, 21 May 2019 17:17:45 GMT

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

Re: Link "A table" state to version

forestknutsen1 — Tue, 21 May 2019 18:48:40 GMT

Great! Thanks for clearing this up for me!