topic Re: explain SQL statement for Branch version in ArcGIS Enterprise Questions

explain SQL statement for Branch version

DasheEbra — Sun, 20 Feb 2022 09:57:53 GMT

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' );

Re: explain SQL statement for Branch version

KimberlyGarbade — Sun, 20 Feb 2022 12:57:56 GMT

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?

Re: explain SQL statement for Branch version

DasheEbra — Mon, 21 Feb 2022 13:19:13 GMT

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!!