Page Comparison

...

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

...

• 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...

Accessions-Objects Relationships extact

...

• the join structure of this query mirrors the structure of the Accessions-Objects Relationships query above

...

Owner extact

field mapping

...

view:

SELECT
 oacc.objectID acquisitionID,
 x.constituentID owner
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN objects o 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')
;

notes

Acquisition Note extract

field mapping

sql query

view:

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 = oacc.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND oacc.IsVirtual = 1 AND oacc.IsTemplate = 0 AND oacc.objectNumber LIKE 'ACC%'
 AND lower(r.ROLErole) IN ('donor', 'seller')
 AND x.remarks IS NOT NULL
;

notes

• NOTE: multiple acquisitionNote values per Accession record should be concatenated into a single field, separated by line breaks
• 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...