We have a view that uses several multi versioned views to export our parcels. We are joining a number of attributes from different sources to the parcel shape.
For years, this view was working fine, but now, it is not returning all the shapes for the parcels.
Are there any tips or tricks for performing such a join?
We are using the multiversioned view as we don't have the attributes in the base tables.
SELECT Addr.OBJECTID,
Addr.asset_id,
Addr.parcel_code,
Addr.insert_date,
Owner.owners_name,
Addr.fulladdr,
Addr.zip_code,
Addr.qs_num,
subdiv_name.subdiv_name,
Addr.mcr_number,
Addr.lot_number,
Addr.tract_name,
Addr.full_zoning,
Addr.full_fema,
Addr.char_area,
Addr.use_code,
addr.juris_code,
Owner.owners_deed_number,
Owner.owners_deed_date,
Owner.owner_use_code,
Owner.legal_class,
Owner.mail_address,
Owner.mail_city,
Owner.mail_state,
Owner.mail_zip,
Parcels.mslink,
parcels.sq_ft,
Parcels.acres,
Parcels.Shape
FROM
(
(SELECT OBJECTID, parcel_centroid_id, asset_id, apn AS parcel_code, creation_date AS insert_date, site_address AS fulladdr, zip_code, qs_num,
mcr_num AS mcr_number, lot_num AS lot_number, tract_name, full_zoning, full_fema, char_area, use_code, jurisdiction AS juris_code
FROM LMS_SDE.lms.PARCEL_ADDRESS_POINTS_EVW
WHERE (use_code NOT IN ('R', 'S')) AND (feature_state = 'ACTIVE')) AS Addr
LEFT OUTER JOIN
(Select MSLINK, Shape, Shape.STArea() AS sq_ft, Shape.STArea() / 43560 AS acres
from lms_sde.lms.Fabric_parcels_polys_evw) AS Parcels ON Addr.parcel_centroid_id = Parcels.MSLINK LEFT OUTER JOIN
(SELECT DISTINCT
OWNER_NAME AS owners_name, APN, deed_num AS owners_deed_number, DEED_DATE AS owners_deed_date, property_use_code AS owner_use_code, Legal_Class, CASE WHEN mail_addres_2 IS NULL THEN mail_address_1 ELSE mail_address_1 + ' ' + mail_addres_2 END AS mail_address,
mail_addres_2, MAIL_CITY, MAIL_STATE, MAIL_ZIP
FROM LMS_SDE.lms.COUNTY_PARCELS) AS Owner ON Addr.parcel_code = Owner.APN LEFT OUTER JOIN
(SELECT DISTINCT mcr_num
FROM LMS_SDE.lms.FABRIC_SUBDIVISION_BOUNDARY_POLYS_EVW) AS Subdiv OUTER APPLY
(SELECT TOP 1 name as subdiv_name, last_edit_date
FROM LMS_SDE.lms.FABRIC_SUBDIVISION_BOUNDARY_POLYS_EVW
WHERE Subdiv.mcr_num = mcr_num) Subdiv_Name ON Addr.mcr_number = subdiv.mcr_num
)
