Page Comparison

...

TMS table name	TMS field name	CSpace field name	notes
Objects	ObjectNumber	acquisitionReferenceNumber
ObjAccession	AccessionMethodID	acquisitionMethod	FK to AccessionMethods table
ObjAccession	AcqJustification	acquisitionReason

sql query

view: cs_accessions (refreshed 2012-03-15)

Code Block

	sql
	sql

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
;

...

view:

Code Block

	sql
	sql

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

notes

first half of the query includes objects linked to accessions via the Associations table
second half of the query includes objects that are not linked to any accession record via the Associations table, but do have an accession number in the ObjAccession table which references (after removing spaces and prepending 'ACC.') an existing accession record
objects that have an accession number in the ObjAccession table which does not reference an existing accession record, or which references an accession record that is different than one the object is linked to via the Associations table, are not imported

...

Code Block

	sql
	sql

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
;

...

Versions Compared

Old Version 11

New Version 12

Key

sql query

notes