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; } } }

Versioned data - view on base table or EVW views?

robert_at_work

Hi there,

we have our data in Oracle (11.2.)and the feature classes are registered as versioned.

I need to create a view to get data from multiple tables in order to export the aggregated data conveniently to Excel. Export needs to be done in ArcView (10.5.1). We also use third party app.

When I create the view based on the versioned tables then newly added data is missing and deleted data is included in my view. Lets call that "view1".
When I create the view based on the EVW views of the tables I get the data exactly how I want them including newly added data but without deleted data. Lets call that "view2".

Now, here comes the problem. The third party app comes with a nice query tool for tables. But when I perform a query on view2 I get an ESRI error message -

esriDataSourceGDB.SdeWorkspace.1: column not found [ORA-00904: "V_30674"."VERTRAG_REF": invalid identifier][VMG.vmg_v_dbv_vertrag_eig][STATE_ID = 16579]

When I run the same query on view1 there is no error message (but my results might be wrong as new data is missing, deleted data is shown). Also - when I look which query has been executed by Oracle I do not see any difference between the executed query for view1 and view2.

Anyone having had a similar issue?
Any advice?

Thanks, Rob

Vind meer berichten getagd met

Accepted answers

robert_at_work

it seems the message is related to the thrid party tool. the query tool needs to be configured and you need to link the query to the data by means of an SQL statement(see below).

That SQL statement itself is a join between my view and some other tables. If I refer to the other tables in my join I get the error message but if I refer to the EVWs view of these tables I dont get an error message and everything appears to work like a charm.

This will work:

<SPAN class="operator token"><</SPAN>sql<SPAN class="operator token">></SPAN>
				<SPAN class="keyword token">select</SPAN> <SPAN class="keyword token">distinct</SPAN> vmg_v_dbv_vertrag_eig<SPAN class="punctuation token">.</SPAN>objectid							
				<SPAN class="keyword token">from</SPAN> vmg_v_dbv_vertrag_eig  <SPAN class="keyword token">left</SPAN> <SPAN class="keyword token">join</SPAN> vmgr_vertrag_eigentuemer_evw  <SPAN class="keyword token">on</SPAN> vmg_v_dbv_vertrag_eig<SPAN class="punctuation token">.</SPAN>globalid <SPAN class="operator token">=</SPAN> vmgr_vertrag_eigentuemer_evw<SPAN class="punctuation token">.</SPAN>vertrag_ref
				<SPAN class="keyword token">left</SPAN> <SPAN class="keyword token">join</SPAN> vmg_eigentuemer_evw <SPAN class="keyword token">on</SPAN> vmgr_vertrag_eigentuemer_evw<SPAN class="punctuation token">.</SPAN>eigentuemer_ref <SPAN class="operator token">=</SPAN> vmg_eigentuemer_evw<SPAN class="punctuation token">.</SPAN>globalid						
				<SPAN class="keyword token">where</SPAN> <SPAN class="number token">1</SPAN><SPAN class="operator token">=</SPAN><SPAN class="number token">1</SPAN>
				{<SPAN class="operator token">and</SPAN> upper<SPAN class="punctuation token">(</SPAN>vmg_v_dbv_vertrag_eig<SPAN class="punctuation token">.</SPAN>dokument_nr<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">like</SPAN> upper<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>cmbdoknr<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>}
				<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>
				
			<SPAN class="operator token"><</SPAN><SPAN class="operator token">/</SPAN>sql<SPAN class="operator token">></SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

But this one will throw the error message:

<SPAN class="operator token"><</SPAN>sql<SPAN class="operator token">></SPAN>
				<SPAN class="keyword token">select</SPAN> <SPAN class="keyword token">distinct</SPAN> vmg_v_dbv_vertrag_eig<SPAN class="punctuation token">.</SPAN>objectid							
				<SPAN class="keyword token">from</SPAN> vmg_v_dbv_vertrag_eig  <SPAN class="keyword token">left</SPAN> <SPAN class="keyword token">join</SPAN> vmgr_vertrag_eigentuemer  <SPAN class="keyword token">on</SPAN> vmg_v_dbv_vertrag_eig<SPAN class="punctuation token">.</SPAN>globalid <SPAN class="operator token">=</SPAN> vmgr_vertrag_eigentuemer<SPAN class="punctuation token">.</SPAN>vertrag_ref
				<SPAN class="keyword token">left</SPAN> <SPAN class="keyword token">join</SPAN> vmg_eigentuemer <SPAN class="keyword token">on</SPAN> vmgr_vertrag_eigentuemer<SPAN class="punctuation token">.</SPAN>eigentuemer_ref <SPAN class="operator token">=</SPAN> vmg_eigentuemer<SPAN class="punctuation token">.</SPAN>globalid						
				<SPAN class="keyword token">where</SPAN> <SPAN class="number token">1</SPAN><SPAN class="operator token">=</SPAN><SPAN class="number token">1</SPAN>
				{<SPAN class="operator token">and</SPAN> upper<SPAN class="punctuation token">(</SPAN>vmg_v_dbv_vertrag_eig<SPAN class="punctuation token">.</SPAN>dokument_nr<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">like</SPAN> upper<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>cmbdoknr<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>}
				<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>
			<SPAN class="operator token"><</SPAN><SPAN class="operator token">/</SPAN>sql<SPAN class="operator token">></SPAN>‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

thanks to all of you for your suggestions.

All comments

Asrujit_SenGupta

Is Oracle Extproc configured for this Geodatabase? Try configuring that and then check with View2.

Configure the Oracle extproc to access the geodatabase with SQL—Help | ArcGIS Desktop

robert_at_work

No, Extproc is not configured. It would involve quite some work and money to get it done as Oracle is managed by kind of external service provider. Is that just a general suggestion by yours or do you have specific knowledge that configuring extproc solves the problem?

robert_at_work

forgot to mention - no spatial data involved here....

George_Thompson

When you look at the versioned view it is looking at the data for that version. For example, you are connecting to EVW and looking at that data. It will be looking at the DEFAULT version of the data. Any edits that have been added in another version, i.e. EDIT, will not show up till a reconcile/post is performed.

Can you query all the data in the EVW with the tool and no extra joins? If that does not work, then you will need to configure the EXTPROC as the version views relay on the complete configuration of the geodatabase (including the EXTPROC).

robert_at_work

@George_Thompson-esristaff - I did a quick test and created a view for an existing EVW view:

<SPAN class="keyword token">create</SPAN> <SPAN class="keyword token">view</SPAN> vmg_v_dbv_vertrag_test1 <SPAN class="keyword token">as</SPAN> <SPAN class="keyword token">select</SPAN> <SPAN class="operator token">*</SPAN> <SPAN class="keyword token">from</SPAN> vmg_dbv_vertrag_evw<SPAN class="punctuation token">;</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

The third party query tool returns the same error message as before. So, basically I cannot query any EVW based data source.

Now, just to give that little bit of extra information on how versioning works in our setup: whenever we edit data the third party app we use creates a new version automaticlly and after editing is finished it closes the version and perfoms an reconcile/post all in the background. We do not have any "open" versions for more than a few seconds - completely hidden to the user. The only thing we - as users/admins - need to do is do a compress on a regular basis. So when I query EVW views I see everything I need.

back to the problem - why do I need to have EXPROC configured? The docs meantion only that EXPROC is required if you need to run (external) SQL on the spatial data. The tables I'm working on right now do not have spatial data at all. I dont mind setting up EXPROC - its just that I cant do it myself and it will cost us having it done. I want to be sure that missing EXPROC configuration is just more then a good guess as cause for the problem.

robert_at_work

I did a bit of further testing....I dont think it has anything to do with EXPROC. When I create a simple view like this:

create or replace test1 as select * from simple_table_evw;

and then query "test1" with the third party query tool it gets executed without any error message.

But my view is more complex and contains joins. I also guess that the third party query tool actually executes two queries - the one I can see successfully executed in Oracle (by looking for it in v$sql view) and a second, subsequent one that fails due to a complex view defintion and maybe a bug the query tool and for which I get the error message. I now have one option left - rewriting the join and instead of using ANSI SQL apply the old Oracle Join syntax...but thats something for next week ...good weekend all readers, rob

JoshuaBixby

If you open SQLPLUS and query against vmg_v_dbv_vertrag_test1 does it generate the same error? I am wondering if it is something the third-party app is doing and not anything with the views themselves.

robert_at_work

it seems the message is related to the thrid party tool. the query tool needs to be configured and you need to link the query to the data by means of an SQL statement(see below).

This will work:

<SPAN class="operator token"><</SPAN>sql<SPAN class="operator token">></SPAN>
				<SPAN class="keyword token">select</SPAN> <SPAN class="keyword token">distinct</SPAN> vmg_v_dbv_vertrag_eig<SPAN class="punctuation token">.</SPAN>objectid							
				<SPAN class="keyword token">from</SPAN> vmg_v_dbv_vertrag_eig  <SPAN class="keyword token">left</SPAN> <SPAN class="keyword token">join</SPAN> vmgr_vertrag_eigentuemer_evw  <SPAN class="keyword token">on</SPAN> vmg_v_dbv_vertrag_eig<SPAN class="punctuation token">.</SPAN>globalid <SPAN class="operator token">=</SPAN> vmgr_vertrag_eigentuemer_evw<SPAN class="punctuation token">.</SPAN>vertrag_ref
				<SPAN class="keyword token">left</SPAN> <SPAN class="keyword token">join</SPAN> vmg_eigentuemer_evw <SPAN class="keyword token">on</SPAN> vmgr_vertrag_eigentuemer_evw<SPAN class="punctuation token">.</SPAN>eigentuemer_ref <SPAN class="operator token">=</SPAN> vmg_eigentuemer_evw<SPAN class="punctuation token">.</SPAN>globalid						
				<SPAN class="keyword token">where</SPAN> <SPAN class="number token">1</SPAN><SPAN class="operator token">=</SPAN><SPAN class="number token">1</SPAN>
				{<SPAN class="operator token">and</SPAN> upper<SPAN class="punctuation token">(</SPAN>vmg_v_dbv_vertrag_eig<SPAN class="punctuation token">.</SPAN>dokument_nr<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">like</SPAN> upper<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>cmbdoknr<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>}
				<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>
				
			<SPAN class="operator token"><</SPAN><SPAN class="operator token">/</SPAN>sql<SPAN class="operator token">></SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

But this one will throw the error message:

<SPAN class="operator token"><</SPAN>sql<SPAN class="operator token">></SPAN>
				<SPAN class="keyword token">select</SPAN> <SPAN class="keyword token">distinct</SPAN> vmg_v_dbv_vertrag_eig<SPAN class="punctuation token">.</SPAN>objectid							
				<SPAN class="keyword token">from</SPAN> vmg_v_dbv_vertrag_eig  <SPAN class="keyword token">left</SPAN> <SPAN class="keyword token">join</SPAN> vmgr_vertrag_eigentuemer  <SPAN class="keyword token">on</SPAN> vmg_v_dbv_vertrag_eig<SPAN class="punctuation token">.</SPAN>globalid <SPAN class="operator token">=</SPAN> vmgr_vertrag_eigentuemer<SPAN class="punctuation token">.</SPAN>vertrag_ref
				<SPAN class="keyword token">left</SPAN> <SPAN class="keyword token">join</SPAN> vmg_eigentuemer <SPAN class="keyword token">on</SPAN> vmgr_vertrag_eigentuemer<SPAN class="punctuation token">.</SPAN>eigentuemer_ref <SPAN class="operator token">=</SPAN> vmg_eigentuemer<SPAN class="punctuation token">.</SPAN>globalid						
				<SPAN class="keyword token">where</SPAN> <SPAN class="number token">1</SPAN><SPAN class="operator token">=</SPAN><SPAN class="number token">1</SPAN>
				{<SPAN class="operator token">and</SPAN> upper<SPAN class="punctuation token">(</SPAN>vmg_v_dbv_vertrag_eig<SPAN class="punctuation token">.</SPAN>dokument_nr<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">like</SPAN> upper<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>cmbdoknr<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>}
				<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>
			<SPAN class="operator token"><</SPAN><SPAN class="operator token">/</SPAN>sql<SPAN class="operator token">></SPAN>‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

thanks to all of you for your suggestions.

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

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