nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Link "A table" state to version

forestknutsen1

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.

Find more posts tagged with

Accepted answers

BirajaNayak

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

All comments

BirajaNayak

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

forestknutsen1

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

<SPAN class="keyword token">with</SPAN> lina <SPAN class="keyword token">as</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">select</SPAN> <SPAN class="number token">a</SPAN><SPAN class="punctuation token">.</SPAN>objectid<SPAN class="punctuation token">,</SPAN> s<SPAN class="punctuation token">.</SPAN>lineage_name<SPAN class="punctuation token">,</SPAN> s<SPAN class="punctuation token">.</SPAN>state_id <SPAN class="keyword token">from</SPAN> <SPAN class="number token">a293</SPAN> <SPAN class="number token">a</SPAN>
<SPAN class="keyword token">join</SPAN> sde<SPAN class="punctuation token">.</SPAN>states s <SPAN class="keyword token">on</SPAN> s<SPAN class="punctuation token">.</SPAN>state_id <SPAN class="operator token">=</SPAN> <SPAN class="number token">a</SPAN><SPAN class="punctuation token">.</SPAN>sde_state_id<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN>
linver <SPAN class="keyword token">as</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="keyword token">select</SPAN> s<SPAN class="punctuation token">.</SPAN>state_id<SPAN class="punctuation token">,</SPAN> s<SPAN class="punctuation token">.</SPAN>lineage_name<SPAN class="punctuation token">,</SPAN> v<SPAN class="punctuation token">.</SPAN>name <SPAN class="keyword token">from</SPAN> sde<SPAN class="punctuation token">.</SPAN>versions v
<SPAN class="keyword token">join</SPAN> sde<SPAN class="punctuation token">.</SPAN>states s <SPAN class="keyword token">on</SPAN> v<SPAN class="punctuation token">.</SPAN>state_id <SPAN class="operator token">=</SPAN> s<SPAN class="punctuation token">.</SPAN>state_id<SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">select</SPAN> <SPAN class="operator token">*</SPAN> <SPAN class="keyword token">from</SPAN> lina
<SPAN class="keyword token">full</SPAN> <SPAN class="keyword token">outer</SPAN> <SPAN class="keyword token">join</SPAN> linver <SPAN class="keyword token">on</SPAN> lina<SPAN class="punctuation token">.</SPAN>lineage_name <SPAN class="operator token">=</SPAN> linver<SPAN class="punctuation token">.</SPAN>lineage_name‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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?

BirajaNayak

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

forestknutsen1

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.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.