Data leads and DBA's rely on SQL to query data. Data analysts unversed with LRS and/or versioning may struggle with query methods.
Note that there are 2 technical concepts in play:
- Branch Versioning (more fundamental geodatabase concept)
- ArcGIS Pipeline Referencing
- Time Awareness: FROMDATE/ TODATE fields (APR specific and applies to Routes and Events only)
- Location Error: LOCATIONERROR field (APR specific and applies to Events only)
The design considerations are discussed in the blog SQL Views and Branch Versioning
The following SQL code logic may be used to build queries off branch-versioned LRS datasets:
SELECT Name, Type -- Attributes
FROM GIS.PIPE_XX t -- Table
WHERE t.gdb_is_delete = 0 -- Start branch versioning logic
AND t.gdb_branch_id = 0
AND t.gdb_archive_oid = ( SELECT MAX(gdb_archive_oid)
FROM <table> bt
WHERE bt.globalID = t.globalID
AND bt.gdb_branch_id = 0 )
-- End branch versioning logic
-- Optional LRS filtering
AND TODATE IS NULL -- Current LRS records
AND LOCATIONERROR = ‘NO ERROR’ -- Event records with no LRS errors
