Versioned Views

TedCronin — Wed, 18 Jan 2012 17:24:02 GMT

Look promising.

SELECT        b.OBJECTID + 0 OBJECTID, b.APN, b.City, b.AOI, b.Description, b.GlobalID, b.StateTRA, b.Request, b.MajorDistrict, b.Redev_Agency, b.LAFCO_Numb, b.CFD_Numb,
                         b.Shape, b.OBJECTID - b.OBJECTID SDE_STATE_ID
FROM            ASSESSORGIS.ACR_GIS.STATEPROPOSALS b LEFT HASH JOIN
                             (SELECT        SDE_DELETES_ROW_ID, SDE_STATE_ID
                               FROM            ASSESSORGIS.ACR_GIS.d22
                               WHERE        SDE_STATE_ID = 0 AND DELETED_AT IN
                                                             (SELECT        l.lineage_id
                                                               FROM            AssessorGIS.dbo.SDE_states s INNER LOOP JOIN
                                                                                         AssessorGIS.dbo.SDE_state_lineages l ON l.lineage_name = s.lineage_name
                                                               WHERE        s.state_id = AssessorGIS.dbo.SDE_get_view_state() AND l.lineage_id <= s.state_id)) d ON
                         b.OBJECTID = d .SDE_DELETES_ROW_ID
WHERE        d .SDE_STATE_ID IS NULL
UNION ALL
SELECT        a.OBJECTID + 0 OBJECTID, a.APN, a.City, a.AOI, a.Description, a.GlobalID, a.StateTRA, a.Request, a.MajorDistrict, a.Redev_Agency, a.LAFCO_Numb, a.CFD_Numb,
                         a.Shape, a.SDE_STATE_ID
FROM            ASSESSORGIS.ACR_GIS.a22 a LEFT HASH JOIN
                             (SELECT        SDE_DELETES_ROW_ID, SDE_STATE_ID
                               FROM            ASSESSORGIS.ACR_GIS.d22
                               WHERE        DELETED_AT IN
                                                             (SELECT        l.lineage_id
                                                               FROM            AssessorGIS.dbo.SDE_states s INNER LOOP JOIN
                                                                                         AssessorGIS.dbo.SDE_state_lineages l ON l.lineage_name = s.lineage_name
                                                               WHERE        s.state_id = AssessorGIS.dbo.SDE_get_view_state() AND l.lineage_id <= s.state_id)) d ON (a.OBJECTID = d .SDE_DELETES_ROW_ID)
                         AND (a.SDE_STATE_ID = d .SDE_STATE_ID)
WHERE        a.SDE_STATE_ID IN
                             (SELECT        l.lineage_id
                               FROM            AssessorGIS.dbo.SDE_states s INNER LOOP JOIN
                                                         AssessorGIS.dbo.SDE_state_lineages l ON l.lineage_name = s.lineage_name
                               WHERE        s.state_id = AssessorGIS.dbo.SDE_get_view_state() AND l.lineage_id <= s.state_id) AND d .SDE_STATE_ID IS NULL

topic Versioned Views in Data Management Questions

Versioned Views