Page Comparison

...

sql query

view:

...

SELECT 
 acc.objectID acquisitionID,
 acc.objectNumber acquisitionReferenceNumber
FROM
 Objects acc
WHERE
 acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
UNION
SELECT DISTINCT
 NULL acquisitionID,
 upper((CASE WHEN oa.acquisitionNumber NOT LIKE 'acc.%' THEN 'acc.' END) + REPLACE(oa.acquisitionNumber, ' ', '')) acquisitionReferenceNumber
FROM
 ObjAccession oa
WHERE 
 oa.acquisitionNumber IS NOT NULL AND len(oa.acquisitionNumber) > 0 
 AND upper((CASE WHEN oa.acquisitionNumber NOT LIKE 'acc.%' THEN 'acc.' END) + REPLACE(oa.acquisitionNumber, ' ', '')) NOT IN
  (SELECT acc.objectNumber
  FROM Objects acc
  WHERE acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
  )
ORDER BY acquisitionReferenceNumber 
;

notes

...

...

...

Accessions detail extract

field mapping

sql query

view:

SELECT 
 acc.objectID acquisitionID,
 acc.objectNumber acquisitionReferenceNumber,
 (CASE WHEN acc2.AccessionMethodID != 0 THEN m.AccessionMethod ELSE 'unknown' END) acquisitionMethod,
-- acc2.AccessionValue,
 acc2.AcqJustification acquisitionReason,
 acc2.AcquisitionTerms acquisitionNote_1,
 acc2.Source acquisitionNote_2
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
;

notes

• this query is separate from the primary Accessions one above because it includes fields of type TEXT that cannot be included in a union distinct query
• AccessionValue field can be imported manually later, because only a few records have values ObjAccession.AcquisitionLot duplicates data in AcquisitionNumber, and so does not need to be imported
• ObjAccession.AccessionValue has data for only a few (<60) records, and we aren't sure where to put it; this data can be updated manually later, per Michael (1/19/12)
• the acquisitionNote_1 and acquisitionNote_2 fields in this query should be concatenated into a single field acquisitionNote, but that cannot be done in this query because fields of type TEXT do not allow concatenation
• TODO: include AccessionISODate field (here or in a separate query?)...

Accessions-Objects Relationships extact

field mapping

sql query

view:

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 
;

notes

• TODO: include relationships for accessions created from ObjAccession.acquisitionNumber...