I didn't realize that we could
use this information too, not until I read your question. But our volume
statements for the volume records are just too widely ranging (TV shows, travel
books, sets of movies) so I couldn't see how create lists could find this for
us. (The item-volume link is not an available search field for
items. It probably should be.)
So since we're all Sierra sites
on this list, how about SQL? I found 257 items on bib records with volume
records, which aren't attached to a volume, in 31 sec. from among 1.2 million
item record links. The brir. and ir.record_ids aren't useful to
see, except for performing the search, as I'm really after just the Item Record
Numbers.
Dan McMahon
MARINet, Novato CA
=====================================================
SELECT DISTINCT
brir.item_record_id,
ir.record_id,
iv.record_num as "Item Record Number"
FROM sierra_view.bib_record_item_record_link brir
JOIN
sierra_view.item_record ir on brir.item_record_id = ir.record_id
JOIN sierra_view.item_view iv on ir.record_id = iv.id
JOIN sierra_view.bib_record_volume_record_link brvr on brir.bib_record_id
= brvr.bib_record_id
WHERE brvr.bib_record_id IS NOT NULL AND
brir.item_record_id
NOT IN
(SELECT
brir.item_record_id
FROM sierra_view.bib_record_item_record_link brir
JOIN sierra_view.bib_record_volume_record_link brvr on brir.bib_record_id
= brvr.bib_record_id
JOIN sierra_view.volume_record_item_record_link vrir on
brvr.volume_record_id = vrir.volume_record_id
WHERE
vrir.item_record_id = brir.item_record_id
)
ORDER BY 3 ASC
From: Scott, Sharon [mailto:sscott@ccpa.net]
Sent: Wednesday, September 10, 2014 11:02 AM
To: Sierra Users List
Subject: [sierra] Create Lists - Find items not attached to volumes
Sent: Wednesday, September 10, 2014 11:02 AM
To: Sierra Users List
Subject: [sierra] Create Lists - Find items not attached to volumes
Does anyone know how to find items that are not attached to
volumes for bib records that have volumes? We have started separating
multi-disc DVDs so that each disc circulates separately. These are mostly
TV series, e.g., NCIS. The complete first season. We’ve put them into
volumes to make it easy for patrons to place holds on only the ones they want.
Some of our libraries have not yet completed splitting their items and I need
to find a list of those where the bib already has volumes set up, but a given
item is not yet linked to the volume record.
Thanks,
Sharon
---------------------------------
My own folks wanted a revision
to add the volume field info, and I stuck the bib number and title in there as
well since that seems handy.
Dan McMahon
MARINet
===========================================
SELECT DISTINCT vv.record_num,
vfv.field_content,
bv.record_num,
bv.title
FROM sierra_view.volume_record vr
JOIN sierra_view.volume_view vv on
vr.record_id = vv.id
JOIN sierra_view.varfield_view
vfv ON vr.record_id = vfv.record_id
JOIN
sierra_view.bib_record_volume_record_link brvr ON vr.record_id =
brvr.volume_record_id
JOIN sierra_view.bib_record br ON
brvr.bib_record_id = br.id
JOIN sierra_view.bib_view bv ON bv.id
= br.record_id
WHERE vfv.record_type_code
= 'j'
AND vfv.varfield_type_code
= 'v'
AND vr.record_id NOT IN
(SELECT vr.record_id FROM
sierra_view.volume_record vr
JOIN sierra_view.volume_record_item_record_link vrir on vrir.volume_record_id =
vr.record_id
)
=============================================
No comments:
Post a Comment