Useful SQL Queries for Arctos
Tips
Table of Contents
List of Agents (Preferred Agent Name) for an Institution
List of cataloged items entered by a specific Agent
List of Distinct Parts and Related Tissue Flags in Arctos
List of Specimens by Type Status
List of GenBank Numbers in Arctos
List of Parts with Tissue Flag = "0" for a Collection Code
QUERY SPECIMENS TO GET SKINS OR SKELETONS NOT IN ETHANOL, FOR HERPS
QUERY DUPLICATE HERP PARTS WHERE PART_NAME='WHOLE ANIMAL'
GET COUNT OF DISTINCT PART COMBINATIONS BY COLLECTION:
revised 21 Jan 2011 with concatenated parts:
GET COUNT OF DISTINCT CONCATENATED PART COMBINATIONS, BY COLLECTION:
To get the cat_num for a particular combo of concatenated parts:
Count of number of cataloged items with ‘is_tissue' part, by collection:
Count of number of collection objects with ‘is_tissue' part, by collection within an institution:
images showing specimens owned by a collection
media views during a date range
Count of specimens by GUID Prefix
Count of tissues cataloged by year (to show Tissue collection growth)
List and count of cataloged specimens with tissues by family, genus, and species:
Count of specimens that have tissue flag and are whole organisms for a collection
COUNTS OF PARTS BY TYPE AND COLLECTION:
for counts of Consumable only loans:
Count of loans by collection, in a time span (Collection names- MVZ:Mamm, MVZ:Bird, MVZ:Herp):
Count of the barcoded containers by collection:
Count of Media in an Institution
Count of media by license type:
Count by country, state for a specific collection:
Count of records by distinct countries by institution with media of a specific type:
Count of media by media type with certain label values
Count of # records by collection type/code associated with a specific media type
Count of # species by institution with media of a specific type:
Count of species in collection:
List of the unique genera in a collection
Count of Cataloged Items Where Class = AMPHIBIA
Get Count for specimens with no Class
Get Count for specimens by Class
Get Count for hybrids by Class
How many herp species are in Arctos?
Find specimens which “belong to” NPS but aren’t in the NPS loan
Count of loans, related specimen parts and specimens
Count of useless loans, just because
Count of shipments by preferred agent name for a certain time period:
List Higher Geography Used by an Institution
List Unique Localities by Accession
Get a vertical list of positions, barcodes and barcode labels for all cryovials in a freezer box
Checking tissue parts where disposition = missing and vial is barcoded (thus found in collection)
Checking tissue parts where disposition = in collection and vial not barcoded:
Check for parent container_id = 0:
This checks for the MVZ Parentless Void container
Remove cryovial barcodes scanned into a freezer box and return them to “container label”
Find taxon names which are used by MVZ:Mamm and have no Arctos classification data
Find taxon names which are used by MVZ:Mamm and have no classification data
Find taxon names which have no classification data
Query Specimens with Tissues for Class assigned - Herps example
Query Specimens for Catalog Numbers with NO Class assigned - example Herps, tissues
Same query for specimens without tissues
TAXONOMY FILTERED BY COLLECTION (MVZ)
SQL Queries Used in Grinnell Resurvey Project (for Mammal Trapline DB joins in Access)
Replace highlighted text with Institution Acronym of the institution of interest
SELECT distinct agent.preferred_agent_name
FROM cataloged_item, collection, agent, collector
WHERE agent.agent_ID=collector.agent_ID
AND collector.collection_object_ID=cataloged_item.collection_object_ID
AND collection.collection_id=cataloged_item.collection_id
AND collection.institution_acronym='MVZ'
AND agent_name.agent_name_type='preferred'
select count(*) from bulkloader where enteredby in ('rsetsuda','setsudar')
select
distinct specimen_part.part_name,
ctspecimen_part_name.is_tissue
from
ctspecimen_part_name,
specimen_part
where
ctspecimen_part_name.part_name=specimen_part.part_name
group by
specimen_part.part_name, 3.
ctspecimen_part_name.is_tissue
This example for MVZ:Herp gives holotypes, paratypes, etc.
Replace highlighted text with the GUID Prefix of the collection of interest
select type_status,count(*) n from flat,citation where flat.collection_object_id=citation.collection_object_id and guid like 'MVZ:Herp:%' group by type_status
select count(*) from coll_obj_other_id_num where other_id_type='GenBank'
List of Specimens with GenBank Numbers in Arctos
select count(distinct(collection_object_id)) from coll_obj_other_id_num where other_id_type='GenBank'
Replace highlighted text with the collection code of interest.
SELECT ci.cat_num, ci.collection_cde
FROM specimen_part sp, cataloged_item ci
WHERE sp.derived_from_cat_item = ci.collection_object_id
and sp.sampled_from_obj_id is null
and sp.part_name like '%tissue%'
and sp.is_tissue = 0
and collection_cde = 'Mamm'
SELECT ci.cat_num, ci.collection_cde, sp.part_name
FROM specimen_part sp, cataloged_item ci
WHERE sp.derived_from_cat_item = ci.collection_object_id
and sp.sampled_from_obj_id is null
and sp.part_name like '%whole organism (frozen)'
and collection_cde = 'Herp'
QUERY SPECIMENS TO GET PARTS OTHER THAN SOMETHING, E.G., ALL HERP PARTS THAT ARE NOT 'WHOLE ORGANISM' OR LIKE 'TISSUE'
SELECT
cataloged_item.cat_num,
specimen_part.part_name,
specimen_part.preserve_method,
specimen_part.part_modifier
FROM cataloged_item, specimen_part, collection
WHERE cataloged_item.collection_
object_ID = specimen_part.derived_from_cat_item
AND collection.collection_id = cataloged_item.collection_id
AND collection.institution_acronym = 'MVZ'
AND collection.collection_cde = 'Herp'
AND specimen_part.derived_from_cat_item NOT in (
select distinct derived_from_cat_item from specimen_part
where (part_name = 'whole organism' or part_name like '%tissue%'))
order by cataloged_item.cat_num
select
cataloged_item.cat_num,
specimen_part.part_name,
specimen_part.preserve_method,
specimen_part.part_modifier
from
cataloged_item,
specimen_part,
collection,
(SELECT distinct spa.derived_from_cat_item
FROM specimen_part spa, specimen_part spb
where spa.derived_from_cat_item = spb.derived_from_cat_item
and spa.part_name = 'skin'
and nvl(spa.preserve_method, 'NULL') != 'ethanol'
and spb.part_name = 'skeleton'
MINUS
SELECT distinct spc.derived_from_cat_item
FROM specimen_part spc
where spc.part_name not in ('skin', 'skeleton')
) sk
WHERE cataloged_item.collection_object_id = specimen_part.derived_from_cat_item
AND specimen_part.derived_from_cat_item = sk.derived_from_cat_item
AND collection.collection_id = cataloged_item.collection_id
AND collection.collection_id = 30
order by cataloged_item.collection_object_id, specimen_part.part_name
SELECT
cataloged_item.cat_num,
specimen_part.part_name,
specimen_part.preserve_method,
specimen_part.part_modifier
FROM cataloged_item, specimen_part, collection
WHERE cataloged_item.collection_object_ID = specimen_part.derived_from_cat_item
AND collection.collection_id = cataloged_item.collection_id
AND collection.institution_acronym = 'MVZ'
AND collection.collection_cde = 'Herp'
AND specimen_part.derived_from_cat_item in (
select distinct derived_from_cat_item from specimen_part
where part_name = 'whole organism'
group by derived_from_cat_item
having count(*) > 1)
order by cataloged_item.cat_num
select collection.collection, cataloged_item.cat_num from collection, cataloged_item, specimen_part
where collection.collection_id = cataloged_item.collection_id
and cataloged_item.collection_object_id=specimen_part.derived_From_cat_item (+)
and specimen_part.derived_from_cat_item is null
and collection.collection_id=30
order by collection.collection, cat_num
select
count(specimen_part.part_name) mammalparts, specimen_part.part_name, specimen_part.part_modifier, specimen_part.preserve_method
from specimen_part, cataloged_item, collection
WHERE cataloged_item.collection_object_ID = specimen_part.derived_from_cat_item
AND collection.collection_id = cataloged_item.collection_id
AND collection.institution_acronym = 'MVZ'
AND collection.collection_cde = 'Mamm'
GROUP by specimen_part.part_name, specimen_part.part_modifier, specimen_part.preserve_method
ORDER by specimen_part.part_name, specimen_part.part_modifier, specimen_part.preserve_method
select count(specimen_part.part_name) mammalparts, specimen_part.part_name
from specimen_part, cataloged_item, collection
WHERE cataloged_item.collection_object_ID = specimen_part.derived_from_cat_item
AND collection.collection_id = cataloged_item.collection_id
AND collection.institution_acronym = 'MVZ'
AND collection.collection_cde = 'Mamm'
GROUP by specimen_part.part_name
ORDER by specimen_part.part_name
select
count(*) c,CONCATPARTS(cataloged_item.collection_object_ID) parts
from cataloged_item, collection
WHERE
collection.collection_id = cataloged_item.collection_id
AND collection.institution_acronym = 'MVZ'
AND collection.collection_cde = 'Mamm'
GROUP by CONCATPARTS(cataloged_item.collection_object_ID)
ORDER by CONCATPARTS(cataloged_item.collection_object_ID)
select cat_num,collection from cataloged_item, collection
WHERE
collection.collection_id = cataloged_item.collection_id
AND cataloged_item.collection_cde='Mamm'
AND CONCATPARTS(cataloged_item.collection_object_ID)='complete skull; complete skull; study skin; study skin; tissue (frozen); tissue (frozen); tissue (frozen); tissue (frozen)'
select count(distinct(ci.collection_object_id))
from cataloged_item ci,
specimen_part sp,
ctspecimen_part_name cspn
where sp.derived_from_cat_item = ci.collection_object_id
and sp.part_name = cspn.part_name
and cspn.is_tissue = 1
and ci.collection_id='29' [birds]
ci.collection_id='30' [herps] and ‘28' [mammals]
select collection, count(distinct(specimen_part.collection_object_id)) numParts
from
cataloged_item,
collection,
specimen_part,
ctspecimen_part_name
where
cataloged_item.collection_id=collection.collection_id and
cataloged_item.collection_object_id=specimen_part.derived_from_Cat_item and
specimen_part.part_name=ctspecimen_part_name.part_name and
is_tissue=1
and institution_acronym='MVZ'
group by
collection
Annual Report Data
select
count(distinct(media.media_uri)) c
from
media,
media_relations,
cataloged_item,
collection
where
media.media_id=media_relations.media_id and
media_relations.related_primary_key=cataloged_item.collection_object_id and
media_relations.media_relationship like '% cataloged_item' and
cataloged_item.collection_id=collection.collection_id and
collection.guid_prefix='UAM:EH'
select
count(*) c
from
media,
exit_link,
media_relations,
cataloged_item,
collection
where
media.media_id=media_relations.media_id and
media_relations.related_primary_key=cataloged_item.collection_object_id and
media_relations.media_relationship like '% cataloged_item' and
cataloged_item.collection_id=collection.collection_id and
collection.guid_prefix='UAM:EH' and
exit_link.when_date between '2014-07-01' and '2015-06-30' and
-- deal with things being moved around on TACC
-- is probably imperfect, but close
(
media.media_uri=exit_link.HTTP_TARGET or
replace(media.media_uri,'http://web.corral.tacc.utexas.edu/UAF/arctos/')=replace(exit_link.HTTP_TARGET,'http://arctos.database.museum/')
)
select
to_char(qs.create_date, 'YYYY') yr,
count(qsc.query_id) numberQueries,
sum(qsc.rec_count) numberRecordViews
from
uam_query.query_stats qs,
uam_query.query_stats_coll qsc
where
qs.query_id = qsc.query_id
group by to_char(qs.create_date, 'YYYY')
order by to_char(qs.create_date, 'YYYY')
;
select to_char(DOWNLOAD_DATE, 'YYYY') yr,
count(*) numDownloads,
sum(NUM_RECORDS) NumRecordsDL
from
cf_download
group by to_char(DOWNLOAD_DATE, 'YYYY') order by to_char(DOWNLOAD_DATE, 'YYYY');
select guid_prefix,count(*) numspecs from cataloged_item,collection where cataloged_item.collection_id=collection.collection_id group by guid_prefix
select
count(*),
to_char(fl.ENTEREDDATE, 'YYYY') year
from
specimen_part sp,
ctspecimen_part_name cspn,
flat fl
where sp.derived_from_cat_item = fl.collection_object_id
and sp.part_name = cspn.part_name
and cspn.is_tissue = 1
and fl.institution_acronym = 'MVZ'
group by to_char(fl.ENTEREDDATE, 'YYYY')
order by to_char(fl.ENTEREDDATE, 'YYYY');
Replace highlighted text with GUID prefix of collection of interest
select
count(distinct(flat.collection_object_id)) specimencount,
flat.family,
flat.genus,
flat.species
from
flat,
specimen_part,
ctspecimen_part_name
where
flat.collection_object_id=specimen_part.derived_from_cat_item and
specimen_part.part_name=ctspecimen_part_name.part_name and
ctspecimen_part_name.is_tissue=1 and
flat.guid like 'MVZ:Bird:%'
group by
flat.family,
flat.genus,
Flat.species
Replace highlighted text with GUID Prefix of collection of interest
select
count(distinct(CAT_NUM))
FROM
flat,
specimen_part,
ctspecimen_part_name
where
flat.collection_object_id = specimen_part.derived_from_
cat_item and
specimen_part.part_name=ctspecimen_part_name.part_name and
upper(PARTS) LIKE '%WHOLE ORGANISM%' AND
ctspecimen_part_name.is_tissue = 1
AND guid like 'MVZ:Herp%'
select count(*), sp.part_name
from
specimen_part sp,
cataloged_item ci,
collection c
where sp.derived_from_cat_item = ci.collection_object_id
and ci.collection_id = c.collection_id
and c.institution_acronym = 'MVZ'
and c.collection_cde = 'Bird'
and sp.part_name like ‘%whole organism (frozen)'
group by sp.part_name
order by sp.part_name
select count(*), sp.part_name
from specimen_part sp, cataloged_item ci, collection c
where sp.derived_from_cat_item = ci.collection_object_id
and ci.collection_id = c.collection_id
and c.guid_prefix = 'MVZ:Bird'
and sp.part_name like ‘%skeleton%'
group by sp.part_name
order by sp.part_name
select count(*) from loan,trans,collection where loan.transaction_id=trans.transaction_id and trans.collection_id=collection.collection_id and guid_prefix='MVZ:Herp' and trans_date between to_date ('2015-01-01', 'yyyy-mm-dd') AND to_date ('2015-05-17', 'yyyy-mm-dd')
select count(*) from loan,trans,collection where loan.transaction_id=trans.transaction_id and trans.collection_id=collection.collection_id and collection='MVZ:Herp' and loan_type IN ('consumable', 'return of loan') and trans_date between to_date ('2015-01-01', 'yyyy-mm-dd') AND to_date ('2015-05-0', 'yyyy-mm-dd')
select count(*) from loan,trans,collection where loan.transaction_id=trans.transaction_id and trans.collection_id=collection.collection_id and guid_prefix='MVZ:Mamm' and trans_date between to_date ('2014-07-01', 'yyyy-mm-dd') AND to_date ('2015-06-30', 'yyyy-mm-dd') and loan_type IN ('consumable')
select count(*)
from loan, trans, collection
where loan.transaction_id=trans.transaction_id
and trans.collection_id=collection.collection_id
and collection='MVZ:Herp'
and trans_date between to_date ('2011-01-01', 'yyyy-mm-dd') AND to_date ('2011-01-31', 'yyyy-mm-dd')
select count(*) from (
select distinct coch.container_id
from
cataloged_item ci,
collection coll,
specimen_part sp,
ctspecimen_part_name cspn,
container cont,
container pcont,
coll_obj_cont_hist coch
where sp.derived_from_cat_item = ci.collection_object_id
and ci.collection_id = coll.collection_id
and sp.part_name = cspn.part_name
and cspn.is_tissue = 1
and sp.collection_object_id = coch.collection_object_id
and coch.container_id = cont.container_id
and cont.parent_container_id = pcont.container_id
and pcont.container_type = 'cryovial'
and pcont.barcode is not null
and coll.institution_acronym = 'MVZ'
and coll.collection_cde= 'Bird'
)
Replace highlighted text with Institutional Acronym of institution of interest
select
media.media_id,
media.media_URI,
media.preview_URI,
label_value
from
media,
media_labels
where
media.media_id=media_labels.media_id and
media_labels.MEDIA_LABEL='image number' and
media.media_type='image' and
LABEL_VALUE like '%MVZ%'
select count(*), m.media_type, ct.display
from media m, ctmedia_license ct, media_labels ml
where ml.media_id = m.media_id
and ml.media_label in ('image number', 'audio cut number')
and ml.label_value like 'MVZ %'
and m.media_license_id = ct.media_license_id
group by m.media_type, ct.display;
Replace highlighted text with the GUID prefix of interest
select country,state_prov,count(*) from flat
where guid like 'COA:Bird:%'
group by country,state_prov order by country,state_prov
select country,count(*) from flat,media_relations,media where flat.collection_object_id=media_relations.related_primary_key and media_relans.media_relationship like '% cataloged_item' and
2 media_relations.media_id=media.media_id and media.media_type='audio' and flat.guid like 'MVZ%' group by country
select count(distinct(media.media_id)) from
media,media_labels
where
media.media_id=media_labels.media_id and
media_type='image' and
media_label='description' and
label_value = 'Photograph of eggs.'
select count(distinct(media.media_id)) from
media,media_labels
where
media.media_id=media_labels.media_id and
media_type='image' and
media_label='description' and
lower(label_value) like '%photograph % egg%'
returns the same thing
select count(*), c.institution_acronym, c.collection_cde
from cataloged_item ci, collection c, media m, media_relations mr
where ci.collection_id = c.collection_id
and ci.collection_object_id = mr.related_primary_key
and mr.media_relationship = 'shows cataloged_item'
and mr.media_id = m.media_id
and m.media_type = 'audio'
group by c.institution_acronym, c.collection_cde
select count(distinct(species)) from flat,media_relations,media where flat.collection_object_id=media_relations.related_primary_key and media_relations.media_relationship like '% cataloged_item' and
media_relations.media_id=media.media_id and media.media_type='audio' and flat.guid like 'MVZ%
select count(distinct(get_taxonomy(
cataloged_item.collection_object_id,'genus') || ' ' || get_taxonomy(cataloged_item.collection_object_id,'species'))) gensp
from
cataloged_item,
collection
where
cataloged_item.collection_id=collection.collection_id and
collection.institution_acronym='MVZ' and
collection.collection_cde='Mamm'
Replace the highlighted text with the GUID Prefix of the collection of interest
SELECT
phylclass,
phylorder,
family,
genus
from flat where guid like 'MVZ:Mamm:%' group by phylclass, phylorder, family, genus
select count(*)
from cataloged_item,
identification,
identification_taxonomy,
taxonomy
where cataloged_item.collection_object_id = identification.collection_object_id
and cataloged_item.collection_id = 30
and identification.identification_id = identification_taxonomy.identification_id
and identification.accepted_id_fg = 1
and identification_taxonomy.taxon_name_id = taxonomy.taxon_name_id
and taxonomy.phylclass = 'Amphibia';
select count(*) from flat where collection_id=30 and phylclass=’Amphibia';
-- but most things in FLAT have an UPPER() index so this will perform better:
select count(*) from flat where collection_id=30 and upper(phylclass)='AMPHIBIA';
select count(*) || chr(9) || taxonomy.scientific_name
from cataloged_item, identification, identification_taxonomy, taxonomy
where cataloged_item.collection_object_id = identification.collection_object_id
and cataloged_item.collection_id = 30
and identification.identification_id = identification_taxonomy.identification_id
and identification.accepted_id_fg = 1
and identification_taxonomy.taxon_name_id = taxonomy.taxon_name_id
and taxonomy.phylclass is null
group by taxonomy.scientific_name
order by taxonomy.scientific_name;
(single taxon count-- this will not count the hybrids twice)
select count(*) || chr(9) || taxonomy.phylclass
from cataloged_item, identification, identification_taxonomy, taxonomy
where cataloged_item.collection_object_id = identification.collection_object_id
and cataloged_item.collection_id = 30
and identification.identification_id = identification_taxonomy.identification_id
and identification.accepted_id_fg = 1
and identification_taxonomy.taxon_name_id = taxonomy.taxon_name_id
and identification.taxa_formula not like '%B%'
group by taxonomy.phylclass
order by taxonomy.phylclass;
select count(*) || chr(9) || t1.phylclass || chr(9) || t2.phylclass
from
cataloged_item,
identification,
identification_taxonomy it1,
identification_taxonomy it2,
taxonomy t1,
taxonomy t2
where cataloged_item.collection_object_id = identification.collection_object_id
and cataloged_item.collection_id = 30
and identification.identification_id = it1.identification_id
and identification.identification_id = it2.identification_id
and identification.accepted_id_fg = 1
and identification.taxa_formula like '%B%'
and it1.taxon_name_id = t1.taxon_name_id
and it1.variable = 'A'
and it2.taxon_name_id = t2.taxon_name_id
and it2.variable = 'B'
group by t1.phylclass, t2.phylclass
order by t1.phylclass, t2.phylclass;
IN CF BOX (results in table):
select
scientific_name,
count(*)
from
cataloged_item,
collection,
identification
where
cataloged_item.collection_id=collection.collection_id and
collection.collection_cde='Herp' and
collection.institution_acronym='MVZ' and
cataloged_item.collection_object_id=identification.collection_object_id and
accepted_id_fg=1
group by
scientific_name
select
count(distinct(get_taxonomy(
cataloged_item.collection_object_id,'genus') || ' ' || get_taxonomy(cataloged_item.collection_object_id,'species'))) gensp
from
cataloged_item,
collection
where
cataloged_item.collection_id=collection.collection_id and
collection.institution_acronym='MVZ' and
collection.collection_cde='Herp';
select loan_number from loan,trans,collection where loan.transaction_id=trans.transaction_id and trans.collection_id=collection.collection_id and collection.institution_acronym like 'MVZ%' and loan_number like '2012%' order by loan_number
Symptom: PK_LOAN_ITEM violated
First get the URL of the specimens you’re trying to add; you may need to ‘show/hide search terms’ and ‘requery.’
To the end of the URL, append
&debug=1
And ENTER. You should get some SQL at the top of the page.
SELECT distinct flat.collection_object_id ,concatSingleOtherId(flat.collection_object_id,'collector number') AS CustomID, 'collector number' as myCustomIdType, to_number(ConcatSingleOtherIdInt(flat.collection_object_id,'collector number')) AS CustomIDInt,flat.guid guid,flat.scientific_name scientific_name,flat.identification_remarks identification_remarks,flat.typestatus citations,flat.country country,flat.state_prov state_prov,flat.spec_locality spec_locality,flat.verbatim_date verbatim_date,flat.dec_lat dec_lat,flat.dec_long dec_long,flat.coordinateuncertaintyinmeters coordinateuncertaintyinmeters,concatAttributeValue(flat.collection_object_id,'age') age,concatAttributeValue(flat.collection_object_id,'age class') age_class,concatAttributeValue(flat.collection_object_id,'ear from notch') ear_from_notch,concatAttributeValue(flat.collection_object_id,'hind foot with claw') hind_foot_with_claw,flat.sex sex,concatAttributeValue(flat.collection_object_id,'tail length') tail_length,concatAttributeValue(flat.collection_object_id,'total length') total_length,concatAttributeValue(flat.collection_object_id,'weight') weight FROM flat INNER JOIN v_attributes tbl_description ON (flat.collection_object_id = tbl_description.collection_object_id) WHERE flat.collection_object_id IS NOT NULL AND flat.collection_id IN ( select collection_id from collection where upper(guid_prefix) IN ('UAM:EH') ) AND flat.collection_object_id NOT IN (23832947,23818026,23839350,23815681,23815039,23818109,23844948,23818237,23837646,23834912,23818787,23819216,23845941,23843742,23817685,23810266,23817692,23819126,23839422,23829773,23840022,23841807,23829779,23839821,23817994,23838087,23817777,23810362,23841579,23818980,23839818,23823896,28143511,28143540,23815714,23817530,28044537,23836559,28031815,23835782,28030217,23831060,23817688,23834642,28031821,28031818,23818189,23827565,23804525,23803247,23803253,23804855,23818261,23818267,23844056,23805257,23813851,23845653,28178288) AND tbl_description.attribute_type = 'description' AND upper(tbl_description.attribute_value) like '%GUT%'
Edit loan; grab the transaction_id from the URL
….?transaction_id=21123234….
Patch those together to form….
Select guid
{the debug-query starting with the word “from”}
and guid in (
select guid from flat,specimen_part,loan_item,loan where flat.collection_object_id=specimen_part.derived_from_cat_item and specimen_part.collection_object_id=loan_item.collection_object_id and
loan.transaction_id=loan_item.transaction_id and loan.transaction_id=
{the transaction_id of the loan}
)
Full example:
SELECT guid from flat INNER JOIN v_attributes tbl_description ON (flat.collection_object_id = tbl_description.collection_object_id) WHERE flat.collection_object_id IS NOT NULL AND flat.collection_id IN ( select collection_id from collection where upper(guid_prefix) IN ('UAM:EH') ) AND flat.collection_object_id NOT IN (23832947,23818026,23839350,23815681,23815039,23818109,23844948,23818237,23837646,23834912,23818787,23819216,23845941,23843742,23817685,23810266,23817692,23819126,23839422,23829773,23840022,23841807,23829779,23839821,23817994,23838087,23817777,23810362,23841579,23818980,23839818,23823896,28143511,28143540,23815714,23817530,28044537,23836559,28031815,23835782,28030217,23831060,23817688,23834642,28031821,28031818,23818189,23827565,23804525,23803247,23803253,23804855,23818261,23818267,23844056,23805257,23813851,23845653,28178288) AND tbl_description.attribute_type = 'description' AND upper(tbl_description.attribute_value) like '%GUT%'
and guid in (
select guid from flat,specimen_part,loan_item,loan where flat.collection_object_id=specimen_part.derived_from_cat_item and specimen_part.collection_object_id=loan_item.collection_object_id and
loan.transaction_id=loan_item.transaction_id and loan.transaction_id=21123234
)
Use WriteSQL to find the items which are in both the query and the loan.
select
guid
from
flat,
coll_obj_other_id_num
where
flat.collection_object_id=coll_obj_other_id_num.collection_object_id and
(flat.guid like 'UAM:Herb:%' or flat.guid like 'UAM:Alg:%' or flat.guid like 'UAMb:Herb:%') and
-- this only works for data loans
-- pull transaction_id from edit loan
flat.collection_object_id not in (select collection_object_id from loan_item where transaction_id=21110932) and
other_id_type in ('U. S. National Park Service accession','U. S. National Park Service catalog')
;
note that these loan counts below do not include loans where tissues or specimens are uncat or where they are numbers mentioned in description but parts not attached:
select
count(distinct(loan.loan_number)) number_of_loans,
count(distinct(specimen_part.collection_object_id)) number_of_parts,
count(distinct(specimen_part.derived_from_cat_item)) number_of_catitems
from
loan,
trans,
collection,
loan_item,
specimen_part
where
loan.transaction_id=trans.transaction_id and
trans.collection_id=collection.collection_id and
collection.collection='MVZ Herps' and
loan.transaction_id=loan_item.transaction_id and
loan_item.collection_object_id=specimen_part.collection_object_id and
trans_date between to_date ('2008-07-01', 'yyyy-mm-dd') AND to_date ('2009-06-30', 'yyyy-mm-dd')
NUMBER_OF_LOANS NUMBER_OF_PARTS NUMBER_OF_CATITEMS
--------------- --------------- ------------------
67 1403 1353
select
count(distinct(loan.loan_number)) number_of_loans,
count(distinct(cataloged_item.collection_object_id)) number_of_catitems
from
loan,
trans,
collection,
loan_item,
cataloged_item
where
loan.transaction_id=trans.transaction_id and
trans.collection_id=collection.collection_id and
collection.collection='MVZ Herps' and
loan.transaction_id=loan_item.transaction_id and
loan_item.collection_object_id=cataloged_item.collection_object_id and
trans_date between to_date ('2008-07-01', 'yyyy-mm-dd') AND to_date ('2009-06-30', 'yyyy-mm-dd')
NUMBER_OF_LOANS NUMBER_OF_CATITEMS
--------------- ------------------
0 0
select
count(distinct(loan.loan_number)) number_of_loans
from
loan,
trans,
collection,
loan_item
where
loan.transaction_id=trans.transaction_id and
trans.collection_id=collection.collection_id and
collection.collection='MVZ Herps' and
loan.transaction_id=loan_item.transaction_id (+) and
trans_date between to_date ('2008-07-01', 'yyyy-mm-dd') AND to_date ('2009-06-30', 'yyyy-mm-dd') and
loan_item.transaction_id is null
NUMBER_OF_LOANS
---------------
37
select
count(*) c,
getPreferredAgentName(PACKED_BY_AGENT_ID) packedby
from
shipment,
loan,
trans,
collection
where
shipment.transaction_id=loan.transaction_id and
loan.transaction_id=trans.transaction_id and
trans.collection_id=collection.collection_id and
collection.institution_acronym='MVZ' and
loan_type in ( 'consumable' , 'in house' , 'mixed' , 'return of loan' , 'returnable' , 'transfer of custody' ) and
trans.TRANS_DATE between '2005-06-01' and '2015-05-31'
group by
getPreferredAgentName(PACKED_BY_AGENT_ID)
Replace highlighted text with the Institutional Acronym of the institution of interest
SELECT distinct geog_auth_rec.geog_auth_rec_id, geog_auth_rec.country, geog_auth_rec.state_prov, geog_auth_rec.county, geog_auth_rec.island, geog_auth_rec.island_group
FROM geog_auth_rec, cataloged_item, collection, locality, collecting_event
WHERE geog_auth_rec.geog_auth_rec_id=locality.geog_auth_rec_id
AND locality.locality_ID=collecting_event.locality_ID
AND collecting_event.collecting_event_ID=cataloged_item.collecting_event_ID
AND collection.collection_id=cataloged_item.collection_id
AND collection.institution_acronym='MVZ'
Replace highlighted text with the accession number of interest
SELECT distinct Locality_ID, Spec_locality FROM filtered_flat
WHERE accession = 14490
select began_date,guid from flat where year<1700
select began_date,guid from flat where year>to_char(sysdate,'YYYY')
Replace highlighted text with the barcode of the freezer box for which you want the list.
select
c.barcode vial_bc,
c.label vial_lbl,
p.label position_lbl
from
container b,
container p,
container c
where
b.container_id=p.parent_container_id and
p.container_id=c.parent_container_id and
b.barcode='DGR18808'
order by CAST (position_lbl AS int)
select distinct coll.institution_acronym, coll.collection_cde, ci.cat_num, sp.part_name
from
cataloged_item ci,
collection coll,
specimen_part sp,
ctspecimen_part_name cspn,
coll_object co,
container cont,
container pcont,
coll_obj_cont_hist coch
where sp.derived_from_cat_item = ci.collection_object_id
and ci.collection_id = coll.collection_id
and sp.part_name = cspn.part_name
and cspn.is_tissue = 1
and sp.collection_object_id = co.collection_object_id
and co.coll_obj_disposition = 'missing'
and sp.collection_object_id = coch.collection_object_id
and coch.container_id = cont.container_id
and cont.parent_container_id = pcont.container_id
and pcont.container_type = 'cryovial'
and pcont.barcode is not null
and coll.institution_acronym = 'MVZ'
and coll.collection_cde= 'Bird';
Checking tissue parts where disposition = missing and vial is not barcoded
select distinct
coll.institution_acronym,
coll.collection_cde,
ci.cat_num,
sp.part_name
from
cataloged_item ci,
collection coll,
specimen_part sp,
ctspecimen_part_name cspn,
coll_object co,
container cont,
container pcont,
coll_obj_cont_hist coch
where sp.derived_from_cat_item = ci.collection_object_id
and ci.collection_id = coll.collection_id
and coll.institution_acronym = 'MVZ'
and coll.collection_cde = 'Herp'
and sp.sampled_from_obj_id is null
and sp.part_name = cspn.part_name
and cspn.is_tissue = 1
and sp.collection_object_id = co.collection_object_id
and co.coll_obj_disposition = 'missing'
and sp.collection_object_id = coch.collection_object_id
and coch.container_id = cont.container_id
and cont.parent_container_id = pcont.container_id
and pcont.barcode is null;
select distinct coll.institution_acronym, coll.collection_cde, ci.cat_num, sp.part_name
from
cataloged_item ci,
collection coll,
specimen_part sp,
ctspecimen_part_name cspn,
coll_object co,
container cont,
container pcont,
coll_obj_cont_hist coch
where sp.derived_from_cat_item = ci.collection_object_id
and ci.collection_id = coll.collection_id
and sp.part_name = cspn.part_name
and cspn.is_tissue = 1
and sp.collection_object_id = co.collection_object_id
and co.coll_obj_disposition = 'in collection'
and sp.collection_object_id = coch.collection_object_id
and coch.container_id = cont.container_id
and cont.parent_container_id = pcont.container_id
and pcont.container_type = 'cryovial'
and pcont.barcode is null
and coll.institution_acronym = 'MVZ'
and coll.collection_cde= 'Bird'
select distinct
coll.institution_acronym,
coll.collection_cde,
ci.cat_num,
sp.part_name
from
cataloged_item ci,
collection coll,
specimen_part sp,
ctspecimen_part_name cspn,
coll_object co,
container cont,
container pcont,
coll_obj_cont_hist coch
where sp.derived_from_cat_item = ci.collection_object_id
and ci.collection_id = coll.collection_id
and coll.institution_acronym = 'MVZ'
and coll.collection_cde = 'Herp'
and sp.sampled_from_obj_id is null
and sp.part_name = cspn.part_name
and cspn.is_tissue = 1
and sp.collection_object_id = co.collection_object_id
and co.coll_obj_disposition = 'in collection'
and sp.collection_object_id = coch.collection_object_id
and coch.container_id = cont.container_id
and cont.parent_container_id = pcont.container_id
and pcont.barcode is null;
select distinct coll.institution_acronym, coll.collection_cde, ci.cat_num, sp.part_name
from
cataloged_item ci,
collection coll,
specimen_part sp,
ctspecimen_part_name cspn,
coll_object co,
container cont,
coll_obj_cont_hist coch
where sp.derived_from_cat_item = ci.collection_object_id
and ci.collection_id = coll.collection_id
and sp.part_name = cspn.part_name
and cspn.is_tissue = 1
and sp.collection_object_id = co.collection_object_id
and co.coll_obj_disposition = 'missing'
and sp.collection_object_id = coch.collection_object_id
and coch.container_id = cont.container_id
and cont.parent_container_id = 0
and coll.institution_acronym = 'MVZ'
and coll.collection_cde= 'Bird'
select distinct
coll.institution_acronym, coll.collection_cde, ci.cat_num, sp.part_name
from
cataloged_item ci,
collection coll,
specimen_part sp,
ctspecimen_part_name cspn,
coll_object co,
container cont,
coll_obj_cont_hist coch
where sp.derived_from_cat_item = ci.collection_object_id
and ci.collection_id = coll.collection_id
and sp.part_name = cspn.part_name
and cspn.is_tissue = 1
and sp.collection_object_id = co.collection_object_id
and co.coll_obj_disposition = 'missing'
and sp.collection_object_id = coch.collection_object_id
and coch.container_id = cont.container_id
and cont.parent_container_id = 397630
and coll.institution_acronym = 'MVZ'
and coll.collection_cde= 'Bird';
Note: cryovials cannot have parts “in” them
Replace highlighted text with the appropriate freezer box barcode
--containers in positions in box
select container_id, barcode from container where parent_container_id in (
select container_id from container where parent_container_id in (
select container_id from container where barcode='DGR17156'
)
);
-- containers in containers in positions in box; die if this finds anything
select * from container where parent_container_id in (
select container_id from container where parent_container_id in (
select container_id from container where parent_container_id in (
select container_id from container where barcode='DGR17156'
)
)
);
-- move positions in box to parentless 'container label'
update container set parent_container_id=0,container_type='container label' where container_id in (
select container_id from container where parent_container_id in (
select container_id from container where parent_container_id in (
select container_id from container where barcode='DGR17156'
)
)
)
select distinct publication_id,full_citation from (
select publication.publication_id,full_citation from publication where lower(full_citation) like '%unknown%'
union
select publication.publication_id,full_citation from publication,publication_agent where publication.publication_id=publication_agent.publication_id and agent_id=0
union
select publication.publication_id,full_citation from publication where publication_id not in (select publication_id from publication_agent)
)
select distinct
taxon_name.scientific_name
from
taxon_name,
identification_taxonomy,
identification,
cataloged_item,
collection
where
taxon_name.taxon_name_id=identification_taxonomy.taxon_name_id and
identification_taxonomy.identification_id=identification.identification_id and
identification.collection_object_id=cataloged_item.collection_object_id and
cataloged_item.collection_id=collection.collection_id and
guid_prefix='MVZ:Mamm' and
taxon_name.taxon_name_id not in (
select taxon_name_id from taxon_term where source='Arctos'
)
order by
scientific_name
select distinct
taxon_name.scientific_name
from
taxon_name,
taxon_term,
identification_taxonomy,
identification,
cataloged_item,
collection
where
taxon_name.taxon_name_id=taxon_term.taxon_name_id (+) and
taxon_term.taxon_name_id is null and
taxon_name.taxon_name_id=identification_taxonomy.taxon_name_id and
identification_taxonomy.identification_id=identification.identification_id and
identification.collection_object_id=cataloged_item.collection_object_id and
cataloged_item.collection_id=collection.collection_id and
guid_prefix='MVZ:Mamm'
order by
scientific_name
select
scientific_name
from
taxon_name,
taxon_term
where
taxon_name.taxon_name_id=taxon_term.taxon_name_id (+) and
taxon_term.taxon_name_id is null
order by
Scientific_name
select
count(distinct(flat.collection_object_id)) specimencount,
flat.phylclass
from
flat,
specimen_part,
ctspecimen_part_name
where
flat.collection_object_id=specimen_part.derived_from_cat_item and
specimen_part.part_name=ctspecimen_part_name.part_name and
ctspecimen_part_name.is_tissue=1 and
flat.guid like 'MVZ:Herp:%'
group by
Flat.phylclass
select
flat.cat_num
from
flat,
specimen_part,
ctspecimen_part_name
where
flat.collection_object_id=specimen_part.derived_from_cat_item and
specimen_part.part_name=ctspecimen_part_name.part_name and
ctspecimen_part_name.is_tissue=1 and
flat.guid like 'MVZ:Herp:%' and
flat.phylclass is null
select
flat.cat_num
from
flat,
specimen_part,
ctspecimen_part_name
where
flat.collection_object_id=specimen_part.derived_from_cat_item and
specimen_part.part_name=ctspecimen_part_name.part_name and
ctspecimen_part_name.is_tissue=1 and
flat.guid like 'MVZ:Herp:%' and
flat.phylclass is null
SELECT distinct taxonomy.taxon_name_id, taxonomy.phylclass, taxonomy.phylorder, taxonomy.family, taxonomy.scientific_name
FROM taxonomy, identification_taxonomy, identification, cataloged_item, collection
WHERE collection.collection_id=cataloged_item.collection_id and cataloged_item.collection_object_id=identification.collection_object_id and
identification.identification_id=identification_taxonomy.identification_id and identification_taxonomy.taxon_name_id=taxonomy.taxon_name_id and
collection.institution_acronym='MVZ'and (collection.collection_cde='Bird' or collection.collection_cde='Mamm' or collection.collection_cde='Herp')
SELECT
accession,
l.locality_remarks,
cat_num,
dec_lat,
dec_long
from flat, locality l
where flat.locality_ID=l.locality_ID
AND accession = 14462
AND collection_cde = 'Mamm'
(Used to join on cat_num and aggregate results at trapline level in Access DB for detectibility analysis-MK)
SELECT unique accession, l.locality_id, l.spec_locality, l.locality_remarks, county, datum, dec_lat, dec_long, coordinateuncertaintyinmeters, georefmethod, lat_long_determiner, lat_long_ref_source, lat_long_remarks, min_elev_in_m, MAX_ELEV_IN_M, orig_lat_long_units, verbatimelevation, verbatimlatitude, verbatimlongitude
from flat, locality l
where flat.locality_ID=l.locality_ID
AND accession = 14462
AND collection_cde = 'Mamm'