Polaris SQL Forum
Program Description:
This will be a moderator led Q & A open discussion among Polaris SQL users. Have a special project that you conducted over the past year? Find a nifty way to script daily procedures? Develop any code that helped your system during COVID? Please share or come prepared with questions.
Time: 3/23/2021 2:00 - 3:00 PM (Virtual 3)
Forum Coordinators:
Derek Brown (Director of IT)
Rochester Hills Public Library
derek.brown@rhpl.org
We’ve been quarantining material over the last year through a bit of SQL magic and I thought I would share it with the group to see how else you’ve all managed the complexity of quarantining items at your libraries.
This is a snip of our code that looks through existing material in the “wild” and moves it to a different branch location. We do this since our main library branch leverages an automated material returns machine and we wanted the material to come off of the patrons record when it was returned.
This runs every hour…
UPDATE TOP (1000) Polaris.Polaris.CircItemRecords
-- Move Items OUT in the wild from MAIN library to RESTING branch WITHOUT holds
SET AssignedBranchID = '14', ShelfLocationID = sl2.ShelfLocationID, StatisticalCodeID = sc2.StatisticalCodeID
FROM Polaris.Polaris.CircItemRecords AS [cir] WITH (NOLOCK)
LEFT OUTER JOIN Polaris.Polaris.ShelfLocations AS [sl1] WITH (NOLOCK)
ON cir.AssignedBranchID = sl1.OrganizationID AND cir.ShelfLocationID = sl1.ShelfLocationID
LEFT OUTER JOIN Polaris.Polaris.ShelfLocations AS [sl2] WITH (NOLOCK)
ON sl1.Description = sl2.Description AND sl2.OrganizationID = '14'
LEFT OUTER JOIN Polaris.Polaris.StatisticalCodes AS [sc1] WITH (NOLOCK)
ON cir.AssignedBranchID = sc1.OrganizationID AND cir.StatisticalCodeID = sc1.StatisticalCodeID
LEFT OUTER JOIN Polaris.Polaris.StatisticalCodes AS [sc2] WITH (NOLOCK)
ON sc1.Description = sc2.Description AND sc2.OrganizationID = '14'
LEFT OUTER JOIN Polaris.Polaris.PatronAccount AS [pa] WITH (NOLOCK)
ON pa.ItemRecordID = cir.ItemRecordID
LEFT OUTER JOIN Polaris.polaris.Patrons AS [ps] WITH (NOLOCK)
ON ps.PatronID = pa.PatronID
-- Items assigned to our main library branch
WHERE AssignedBranchID = '3'
AND ItemStatusID = '2'
-- Ignore IIL
AND ILLFlag = '0'
-- Ignore all econtent
AND cir.Barcode NOT LIKE 'econtent%'
-- Ignore all melcat items out to a patron
-- AND ps.Barcode NOT LIKE 'INNREACH%'
AND cir.AssociatedBibRecordID NOT IN (SELECT BibliographicRecordID
FROM Polaris.Polaris.SysHoldRequests AS [shr] WITH (NOLOCK)
WHERE shr.SysHoldStatusID = '3')
AND cir.Barcode NOT IN (SELECT DISTINCT(ItemBarcode)
FROM Polaris.Polaris.SysHoldRequests AS [shr] WITH (NOLOCK)
WHERE shr.SysHoldStatusID = '3'
AND ItemBarcode IS NOT NULL)
This moves material checked out to our patrons to a specific “resting” branch that the automation machine is also set to. When an item comes across the belt it checks the item “in” and lives in that “resting” branch for a set period of time. Items on hold or that do not fit the standard category are put into the exceptions bin and are handled manually by our staff.
We then follow up with a removal of the quarantine once a day that will take the items out of the “resting” branch and move them back to the standard workflow for our library (based on our time allotted to quarantine).
UPDATE Polaris.Polaris.CircItemRecords
-- Move Items from RESTING branch to MAIN
SET AssignedBranchID = '3', ShelfLocationID = sl2.ShelfLocationID, StatisticalCodeID = sc2.StatisticalCodeID
FROM Polaris.Polaris.CircItemRecords AS [cir] WITH (NOLOCK)
LEFT OUTER JOIN Polaris.Polaris.ShelfLocations AS [sl1] WITH (NOLOCK)
ON cir.AssignedBranchID = sl1.OrganizationID AND cir.ShelfLocationID = sl1.ShelfLocationID
LEFT OUTER JOIN Polaris.Polaris.ShelfLocations AS [sl2] WITH (NOLOCK)
ON sl1.Description = sl2.Description AND sl2.OrganizationID = '3'
LEFT OUTER JOIN Polaris.Polaris.StatisticalCodes AS [sc1] WITH (NOLOCK)
ON cir.AssignedBranchID = sc1.OrganizationID AND cir.StatisticalCodeID = sc1.StatisticalCodeID
LEFT OUTER JOIN Polaris.Polaris.StatisticalCodes AS [sc2] WITH (NOLOCK)
ON sc1.Description = sc2.Description AND sc2.OrganizationID = '3'
WHERE AssignedBranchID = '14'
AND barcode NOT LIKE 'econtent%'
AND CheckInDate BETWEEN DATEADD(day,-3,GETDATE()) AND DATEADD(day,-2,GETDATE())
The process came with new challenges, but SQL helped manage the majority of our items and relieve stress for our limited staff.
Jason Tenter (Library System Administrator) from Saskatchewan Information and Library Services shared with us a fantastic report on hunting down their notification settings. I’ve added the details below and attached the script to the post.
… a report on notification settings at the system, library, and branch-level (if the branch’s settings don’t match the library). I think the code should be ready for any environment…
It’s missing details about some notice types we don’t use, like fines and serial claims, but as a consortium dealing with re-openings it’s been really handy to report on differences between library branches and their parent orgs…
Jason Tenter, MLIS
Library System Administrator
Saskatchewan Information and Library Services
jtenter@sasklibraries.ca
306-570-5562
SA Notice Settings.txt (23.2 KB)
I believe many of us already have our own versions of this code, but I was unable to successfully find a sample of it on the IUG Polaris SQL forum. I thought I’d share it here if it may make some bulk changes a bit easier for the newer SQL admins.
The code attached allows you bulk change loan limits (max items out and max holds) for your organization without having to go line by line. Our example will not run directly in your environment but it can act as a plug’n’play template.
bulkchangingloans.txt (26.4 KB)
Jason over at Saskatchewan is on a roll! He shared with us their customized branch closure process with a helpful guide:
We find this closed branch process fairly straightforward for managing various aspects of a temporary or even less temporary closure.
The instructions included are for utilizing the closed branch process in our system, and reference customized jobs that aren’t provided. However, it should provide enough context to establish in your own system.
Note: we extend checkouts and hold until dates by 2 weeks, but this can be customized in the ‘Maintenance’ file.
Jason Tenter, MLIS
Library System Administrator
Saskatchewan Information and Library Services
jtenter@sasklibraries.ca
306-570-5562
SILS_ClosedBranch_Restore.txt (5.2 KB)
SILS_ClosedBranch_OriginalHours.txt (390 Bytes)
SILS_ClosedBranch_Maintenance.txt (5.6 KB)
SILS_ClosedBranch_List.txt (479 Bytes)
Notes & Instructions.docx (20.2 KB)
SILS - Closed Branch Maintenance Process.zip (28.0 KB)
SILS_RPT_ClosedBranches.txt (4.8 KB)
SILS_Holds_Inactivations.txt (5.9 KB)
SILS_HoldDenials_Suspension.txt (374 Bytes)
SILS_Hold_Inactivations.txt (347 Bytes)
Wes Osborn shared CLC’s custom reports with us and I wanted to provide the direct link to the full list.
One that jumped out at me was their custom purging of patrons:
Library staff can determine which patrons they may want to purge (delete) from the Polaris system using this report. The report shows basic patron information including listing via column heads if the patron account cannot be deleted because of one or more UNbreakable account statuses. The report output can also be limited to ONLY those patrons with UNbreakable statuses so libraries can have staff manually clean up those accounts.
We are in the process of moving to a custom method much like this and it maybe helpful to other Polaris SAs.
Daniel shared his local repository of Polaris SQL. Thanks and I believe a lot of this can be helpful for others to look through!
Useful Polaris SQL by Daniel Messerhttp://tanglewoodhill.ddns.net:8085/daniel/useful-polaris-sql
I’m not a SQL expert.
But I use it everyday.
I’ve spoken with lots of Polaris ILS Administrators who wind up in their jobs because they’re the “librarian who knows computers.” At one library conference, that was literally the reason given to me by a young librarian for her promotion. The previous ILS Admin retired and, since she was the one that knew the most about computers, it was all “Congratulations on your promotion!”
Polaris uses Microsoft SQL Server on the back end, and its database architecture is standardised across installations. That makes it easy, and worthwhile, to share SQL code between librarians; especially those who might be new to Polaris and/or new to SQL.
So that’s what this is. I’ll be adding queries here and there as I write them and revise them. I’ll make sure the queries are safe, especially for the new folks. Many of them are just questions - a SELECT statement looking for data in return. There will be some queries that update the database, but those will be clearly marked. Queries that write information to the database should be taken very seriously and tried first on a testing/training server to make sure they work as intended before you ever let that code go near a production server.
Questions? Well, I’ll do my best. As I said, I’m not an expert.
Thanks for the kind words, y’all!
Just a heads up, that git repo lives on a server that’s literally underneath my desk. Sometimes it goes down for maintenance and backup (usually Monday mornings). If y’all have the interest, I can pretty easily migrate it to a GitLab or a GitHub or something like that. Wasn’t sure if it was worth the effort.
I keep a lot of the CLC’s sql stuff in a public repo as well, though not organized quite as nicely for public consumption as Daniel’s: Bitbucket
This query is for the Find Tool. It locates bibs with tags with specific data. From there you can easily create a record set and use the native bulk bib change to update tags. I hope this helps somebody!
select bibliographicrecordid
from bibliographictags bt (nolock)
inner join bibliographicsubfields bs (nolock)
on bt.bibliographictagid = bs.bibliographictagid
where tagnumber = xxx
and subfield = 'v'
and data like '%data%'
I use a SQL like Heather’s often and insert underscores as sort of a wild card space.
Example: I want to find everything in a 945 field with initials and dates in March 2021
and data like ‘ls03__21’
This will retrieve records for a single cataloger for every day in March.
Hi all,
I’ve gone ahead and moved the SQL git repo I was working on to a real place that’s not a VM running on a Mac Mini underneath my desk. You’ll find it on GitLab via:
Contributions are encouraged and pull requests are welcome. 
I’ve got a few more things I’m going to throw in there and I’ll keep adding as things come up! Unless someone has an objection, I’ll add in some of the queries that were shared here on the forum and in the session!
Give me a day or so and I’ll put a notice on the original repo that the repo has moved.
Dankon tiom, amikoj!