[Koha] help with SQL query for the existence of a MARC field?

[Eric Phetteplace]

Hi Jason,

I think the syntax you want is just to check an ExtractValue statement
against the text being an empty string. So, for instance, to modify your
report to return only those records with 490 fields it would be:

SELECT biblio.biblionumber, biblio.title
FROM biblio_metadata
JOIN biblio ON (biblio_metadata.biblionumber = biblio.biblionumber)
WHERE ExtractValue (metadata, '//datafield[@tag="092"]/subfield[@code="a"]')
= 'ROM'
AND ExtractValue (metadata, '//datafield[@tag="490"]') <> ''

That checks the whole 490 field but if you wanted to check just for the
presence of a subfield you could do so as well, it'd look similar to your
original WHERE line except end in <> '' ("not equal to empty string") as
well.

Best,

ERIC PHETTEPLACE Systems Librarian (he/him)

ephetteplace at cca.edu | o 510.594.3660

5212 Broadway | Oakland, CA | 94618

:(){ :|: & };:


On Thu, May 9, 2019 at 6:49 AM Jason Vasche <jvasche at roundrocktexas.gov>
wrote:

> Greetings Koha Community!
>
> First-time poster here, I'm fairly new to Koha and SQL.  I've been trying
> to write a specific report with no luck. Here's what's happening:
>
> We're updating series authorities for our fiction collection.  To start, I
> need to create a list of all our romance novels that have series fields
> (490, 8XX).  This query returns the list of all our romance novels:
>
> SELECT biblio.biblionumber, biblio.title
> FROM biblio_metadata
> JOIN biblio ON (biblio_metadata.biblionumber = biblio.biblionumber)
> WHERE ExtractValue (metadata,
> '//datafield[@tag="092"]/subfield[@code="a"]') = 'ROM'
>
> The obstacle is trying to figure out what syntax to use to indicate the
> presence of a field (the contents of the field are irrelevant to this
> task.)  I've tried various AND WHERE and AND EXISTS lines, but I'm fumbling
> around in the dark.  Can anyone point me in the right direction?  Thanks
> for your time and attention.
>
> Regards,
>
> Jason Vasché
> Catalog Librarian
> Round Rock Public Library
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>