adjusting MARC field SQL query for indicator?

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

adjusting MARC field SQL query for indicator?

Jason Vasche
Greetings everyone!  I have another newbie SQL question.  I want to run reports that return all bibs with MARC 100 0_ or 100 3_ fields.  I found the "Bibs with a specific MARC field" in the reports library, but don't know how to modify it to include the specific indicator.  Can anyone point me in the right direction?  Thanks for your time and assistance!

Regards,

Jason Vasché
Catalog Librarian
Round Rock Public Library


_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: adjusting MARC field SQL query for indicator?

Eric Phetteplace
Hi Jason,

Indicators can be retrieved from the usual biblio_metadata database field.
Much like you use "//datafield[@tag={code}]" to retrieve specific MARC
fields, you can use "//datafield[@ind1={value}]" to get fields with
particular indicator values, and you can combine these two expressions to
look at both like "//datafield[@tag={code}][@ind1={value}]". So to answer
your specific question:

SELECT biblio.biblionumber, biblio.title
FROM biblio_metadata
JOIN biblio ON (biblio_metadata.biblionumber = biblio.biblionumber)
WHERE ExtractValue (metadata, '//datafield[@tag="100"][@ind1="0"]') <> ''

You can also combine multiple attribute expressions (the bracketed ones
with "@" symbols) in this manner: '//datafield[@tag="100" and @ind1="0"]',
either way will work. Changing the "@ind1=0" to "@ind1=3" will suit the
other half of your query.

---

As an aside, for anyone who may be interested, this strange-looking second
argument to ExtractValue() isn't random magic, it's an XPath expression and
you can learn its syntax and then use it to navigate MARCXML (or any XML)
documents. Maybe a bit overkill once you've memorized the 2-3 things you
need to do in Koha reports but XPath is a generally useful tool I
find. The W3Schools
tutorials <https://www.w3schools.com/xml/xml_xpath.asp> are pretty good and
obviously our Koha catalogs provide a bevy of XML records to work with.

Best,

ERIC PHETTEPLACE Systems Librarian (he/him)

[hidden email] | o 510.594.3660

5212 Broadway | Oakland, CA | 94618

:(){ :|: & };:


On Wed, May 15, 2019 at 6:47 AM Jason Vasche <[hidden email]>
wrote:

> Greetings everyone!  I have another newbie SQL question.  I want to run
> reports that return all bibs with MARC 100 0_ or 100 3_ fields.  I found
> the "Bibs with a specific MARC field" in the reports library, but don't
> know how to modify it to include the specific indicator.  Can anyone point
> me in the right direction?  Thanks for your time and assistance!
>
> Regards,
>
> Jason Vasché
> Catalog Librarian
> Round Rock Public Library
>
>
> _______________________________________________
> Koha mailing list  http://koha-community.org
> [hidden email]
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha