Query - Bibs with no items

Polaris
1

Good evening all,

So many tasks I could easily take of with Sierra Create Lists and Global or Batch Update are not available in any of the Polaris reports and require SQL which I have access to now but am not familiar with at all. Not sure if this is the place to ask for query help but here goes.

I’m looking for a query to find all bibs with no items attached, that aren’t e-resource bibs and don’t have “archive.org” in the 856 tag and do display in PAC.

Thanks in advance!

2

You might want to check out the Polaris Repository it has lots of SQL code in it.

If you plan to use these in the find tool, you may need to make some adjustments. Here is the documentation for using SQL in the find tool. This documentation is for the desktop, but the same applies for leap, it is just the documentation for the desktop client is more thorough.

If nothing in the repository looks helpful, let us know and either @CyberpunkLibrarian or I will see what we can do. We are both heading out to IUG soon, so responses may be delayed. If you need something right away, you could also reach out to Innovative support and they may also be able to help you.

3

Try this:

select 
bibliographicrecordid,
marctypeofmaterial.description
from bibliographicrecords with (nolock)
left join marctypeofmaterial with (nolock) on bibliographicrecords.primarymarctomid=MARCTypeOfMaterialID
where bibliographicrecords.bibliographicrecordid not in  --bib record id isn't associated with any item records
	(select associatedbibrecordid from circitemrecords with (nolock)) 
and primarymarctomid not in (6,36,41,50,48,49) --the table MARCTypeOfMaterial will show you all the different options for this field if this still returns eresources
and bibliographicrecordid not in --bib record id isn't in the list of bibs that have an 856 field with "archive.org" in it
	(select bibliographicrecordid from bibliographictags with (nolock) 
	left join bibliographicsubfields with (nolock) on bibliographictags.bibliographictagid=bibliographicsubfields.bibliographictagid
	where tagnumber=856
	and data like '%archive.org%')
and displayinpac=1 --display in PAC is checked

Here’s a version that should work in the Find tool:

select 
bibliographicrecordid
from bibliographicrecords with (nolock)
left join marctypeofmaterial with (nolock) on bibliographicrecords.primarymarctomid=MARCTypeOfMaterialID
where bibliographicrecords.bibliographicrecordid not in  
	(select associatedbibrecordid from circitemrecords with (nolock)) 
and primarymarctomid not in (6,36,41,50,48,49) 
and bibliographicrecordid not in 
	(select bibliographicrecordid from bibliographictags with (nolock) 
	left join bibliographicsubfields with (nolock) on bibliographictags.bibliographictagid=bibliographicsubfields.bibliographictagid
	where tagnumber=856
	and data like '%archive.org%')
and displayinpac=1
2 Likes
4

That looks superb @ebrondermajor thanks for sharing!

5

Thank you both @wesochuck and @ebrondermajor !