...
Code Block | ||||
---|---|---|---|---|
| ||||
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
- NOTE: 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
- 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)
- ObjAccession.Source also contains data for some records that are linked to objects rather than accessions, and that data is not included here; whether it needs to be imported at all is an open question...
...