Page Comparison

SELECT
 acc.objectID acquisitionID,
 acc.objectNumber acquisitionReferenceNumber,
 (CASE WHEN acc2.AccessionMethodID != 0 THEN m.AccessionMethod ELSE 'unknown' END) acquisitionMethod,
-- acc2.AccessionValue,
 acc2.AcqJustification acquisitionReason
FROM
 Objects acc,
 ObjAccession acc2,
 AccessionMethods m
WHERE
 acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
 AND acc.objectID = acc2.objectID
 AND acc2.AccessionMethodID = m.AccessionMethodID
;

SELECT
 obj.objectID objectID,
 acc.objectID acquisitionID
FROM
 Objects obj,
 Associations oa,
 Objects acc
WHERE
 obj.IsVirtual = 0 AND obj.IsTemplate = 0
 AND acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
 AND obj.objectID = oa.ID2
 AND oa.ID1 = acc.objectID
UNION ALL
SELECT
 obj.objectID objectID,
 acc.objectID acquisitionID
FROM
 Objects obj,
 ObjAccession oa,
 Objects acc
WHERE
 obj.IsVirtual = 0 AND obj.IsTemplate = 0
 AND acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
 AND obj.objectID = oa.objectID
 AND (CASE WHEN oa.acquisitionNumber NOT LIKE 'ACC.%' THEN 'ACC.' END) + REPLACE(oa.acquisitionNumber, ' ', '') = acc.objectNumber
 AND obj.objectID NOT IN
  (SELECT
    obj.objectID
  FROM
    Objects obj,
    Associations oa,
    Objects acc
  WHERE
    obj.IsVirtual = 0 AND obj.IsTemplate = 0
    AND acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
    AND obj.objectID = oa.ID2
    AND oa.ID1 = acc.objectID
  )
;

SELECT
 acc.objectID acquisitionID,
 oa.AccessionISODate acquisitionDate
FROM
 Objects obj,
 Associations oaa,
 Objects acc,
 ObjAccession oa
WHERE
 obj.IsVirtual = 0 AND obj.IsTemplate = 0
 AND acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
 AND obj.objectID = oaa.ID2
 AND oaa.ID1 = acc.objectID
 AND obj.objectID = oa.objectID
 AND len(oa.AccessionISODate) > 0
UNION
SELECT
 acc.objectID acquisitionID,
 oa.AccessionISODate acquisitionDate
FROM
 Objects obj,
 ObjAccession oa,
 Objects acc
WHERE
 obj.IsVirtual = 0 AND obj.IsTemplate = 0
 AND acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
 AND obj.objectID = oa.objectID
 AND (CASE WHEN oa.acquisitionNumber NOT LIKE 'ACC.%' THEN 'ACC.' END) + REPLACE(oa.acquisitionNumber, ' ', '') = acc.objectNumber
 AND obj.objectID NOT IN
  (SELECT
    obj.objectID
  FROM
    Objects obj,
    Associations oa,
    Objects acc
  WHERE
    obj.IsVirtual = 0 AND obj.IsTemplate = 0
    AND acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
    AND obj.objectID = oa.ID2
    AND oa.ID1 = acc.objectID
  )
 AND len(oa.AccessionISODate) > 0
;

SELECT
 acc.objectID acquisitionID,
 x.constituentID owner
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN objects acc ON x.ID = acc.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
 AND lower(r.role) IN ('donor', 'seller')
;

SELECT
 acc.objectID acquisitionID,
 acc2.AcquisitionTerms acquisitionNote
FROM
 Objects acc,
 ObjAccession acc2
WHERE
 acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
 AND acc.objectID = acc2.objectID
 AND acc2.AcquisitionTerms IS NOT NULL
UNION ALL
SELECT
 acc.objectID acquisitionID,
 acc2.Source acquisitionNote
FROM
 Objects acc,
 ObjAccession acc2
WHERE
 acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
 AND acc.objectID = acc2.objectID
 AND acc2.Source IS NOT NULL
UNION ALL
SELECT
 acc.objectID acquisitionID,
 'TMS Donor: ' + x.remarks acquisitionNote
FROM
 ConXrefs x
 JOIN Roles r ON x.roleID = r.roleID
 JOIN Objects acc ON x.ID = acc.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
 AND lower(r.role) IN ('donor', 'seller')
 AND x.remarks IS NOT NULL
;