Analytics FAQs: List of Holdings with Note

I’m looking for a specific note “Committed to retain” in the holdings records (852 $z in this case).  Is it possible to pull a list with all of the holdings that have this special note using SQL?

Yes, there are multiple ways of extracting holdings data with a specific note, such as through Alma’s repository search (Advanced Search) or through Analytics.  This example focuses on creating an Analytics report using an SQL filter to retrieve holdings records with the special note in the 852 $$z field.

Example SQL filter query: “Holding Details”.”852 MARC” LIKE ‘%$$z Committed to retain%’

Example analysis filter:

Analytics filter with an SQl query.

Steps for creating an SQL filter to run this report:

1. From the “Criteria” tab, create a filter under 852 MARC.

New filter with 852 MARC selected

2. Choose “contains any” and enter the local note “$$z Committed to retain” (an example) in the Values field.

New filter definition with operator contains any and value $$z committed to retain

3. Check the box “Convert this filter to SQL” and click “OK”

New filter with checkbox for convert this filter to SQL

4. The converted SQL filter query should look like this.

Advanced SQL Filter with function entered in textarea

5. Save the report and run it to see all the 852 $$z fields retrieved that have the special note “Committed to retain.”

Results table with Committed to retain in the 852 MARC column

IMPORTANT TO NOTE: Spaces or capitalization matter when you enter your textual note in the SQL filter.  An extra space or case can make your query fail.  Also, this Analytics report can be customized to any notes (internal or external) in most holdings fields, such as 866 $x, 866 $$z, 852 $$z, or 852 $$x, etc.

6. The 852 $$z note displays in Primo:

Primo record with Committed to retain in the holdings note