explain SQL statement for Branch version

603
2
02-20-2022 01:57 AM
DasheEbra
Occasional Contributor

dears i am working on Branch version, i want to view a specific version so i search and found the following statement but i don't  understand it well, so would some one explain some terms like MB_, ROW_NUMBER, PARTITION, rn.

SELECT Objectid, shape 
FROM Buildings
WHERE Buildings.GDB_ARCHIVE_OID IN
(Select MB_.GDB_ARCHIVE_OID
From
(SELECT GDB_ARCHIVE_OID,
ROW_NUMBER() OVER (PARTITION BY OBJECTID
ORDER BY gdb_from_date DESC) rn, gdb_is_delete
FROM Buildings
WHERE (gdb_branch_id = 0 AND
gdb_from_date <= '12.31.9999 23:59:59.000')) MB_
WHERE rn = 1 AND gdb_is_delete = '0' );

2 Replies
KimGarbade
Occasional Contributor III

MB_ is the reference (name) assigned to the records returned by the query encased in parenthesis directly preceding it.  You can think of it as a variable that can be referenced (It is referenced before it is "set" when the code says "Select MB_.GDB_ARCHIVE_OID
From"). In this case you are selecting the GDB_ARCHIVE_OID field from the result set of the query:

(SELECT GDB_ARCHIVE_OID,
ROW_NUMBER() OVER (PARTITION BY OBJECTID
ORDER BY gdb_from_date DESC) rn, gdb_is_delete
FROM Buildings
WHERE (gdb_branch_id = 0 AND
gdb_from_date <= '12.31.9999 23:59:59.000'))

Partition is to complicated to go into here, but its used to perform calculations on subsets.  Here's a link:https://www.sqlshack.com/sql-partition-by-clause-overview/

DESC just tells the query (subquery in this case) to order the returned records in descending order

"rn" and 'gdb_is_delete" are fields returned by the query being referenced by MB_

Looks like this query is getting all existing (non-deleted) buildings from a FC base table name "Buildings" in the Default version (gdb_branch_id = 0); and its using three select statements to do it...

To me its some pretty heady stuff, but might point me to the answer to a question I have, which is how to I pull a reference to just my existing features from a branch versioned (BV) database.  Sort of an "versioned view" for BV...  Can you share where you found the SQL code?

 

DasheEbra
Occasional Contributor

thanks Kim,

this statement from ESRI on youtube, here's the link.

https://www.youtube.com/watch?v=Ln0shBwhvaU&t=847s

one more thing if you know how could i use named branch version in map viewer!!

This session will present some of the technical details behind the branch versioning model that was developed for ArcGIS Pro and ArcGIS Enterprise. We will review the ArcGIS Pro user experience and discuss the new functionality of partial post enabled in the Version Management Server and its REST