Create database view which will show records of a feature class based on versioned view (from a specific child version in traditional versioning)

[ZeusThompson]

Hello Community,

I am searching for a way, so that I can create a database view which would dynamically update the records based on versioned view , however I would require to check those for a specific child version (as the edits are performed in child version).

I came across the following article ( https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/read-sqlserver-versioned...) which mentions from SQL Server we can set the session to a particular child version and then run select statement to check the records within a particular feature class, however am unable to figure out how to create a database view out of this.

I would like to not perform reconcile/post and compress operation for updating the view as this would be a tedious task for our organization.

Any kind of help would be highly appreciated. 

[DavidHoy]

if you read that article carefully, you will see the example :

SELECT id, species, reporter
FROM sightings_ev
WHERE reporter = 'chuck';

the clue here is that the FROM clause is using the "Versioned View" that is already created in the geodatabase for any versioned class.

SO, you don't need to create a view - it should be there already. It will be named <featureclassname>_ev

If you want to use this _ev in your own view of a view - that should work (but won't be very fast and you won't be able to edit that view of a versioned view).

[ZeusThompson]

Thank you for the suggestion David!

If the select statement is issued against versioned view it does show records, but as per my understanding it would do for the default version.  As per my organizations' requirement I need to filter out the records from a specific child version. In SQL Server, we can set the version  using following query

EXEC sde.set_current_version ' child_version'

And then issue SELECT statement which would shows records present specifically in the child version. But how should I frame a database view out of that (which would show compartmentalized records from child version )

[DavidHoy]

if you create a view that includes the _ev versioned view you don't define which version you are accessing in the view definition (as you say, without further action it will return records from the "DEFAULT" version)
But, if you want to retrieve records from some other version, you need to run the 
EXEC sde.set_current_version ' child_version'

This will mean all queries (in that session) after you have run that set_current_version will retrieve from the version requested. That includes the SELECT statements on the versioned view that are part of your view definition

So, your view of a view will do what I think you want, but you will need to set the version as an initial step in your SQL Script before you run SELECT statements against the "view of a view".

[ZeusThompson]

Thank you!!

While creating the view setting the child version workspace and then mentioning the SELECT statement is throwing a syntax error while using "Create Database View" from ArcGIS Pro

[DavidHoy]

If I understand you correctly - you tried to set the version as part of the view definition.
That's not what I meant.
Just create the new view in a SQL client (not using Pro GP tool) using something like:

CREATE VIEW new_view AS
SELECT *
FROM your_table_EV
WHERE some_condition;

Once your new view is created, then use the set_version command before running a query against your view

[forestknutsen1]

@ZeusThompson, what is your goal? Are you just trying to get the rows for a version for yourself? Or are you trying to create a view for use by an application or end users who might not have a sql tool? Is it is the version that you want to query always going to have the same name?

If it is just for you I would do as @DavidHoy  suggested. If you are trying to create something more user friendly you could use python with sql or an oracle stored procedure.

Oracle:

https://community.esri.com/t5/geodatabase-questions/using-sde-version-util-set-current-version-in/td...

Python:

# use cx_orcale to create cusror 
cursor.callproc('sde.version_util.set_current_version', [version])
rows = cursor.execute(version_sql).fetchall()