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@cca.edu | o 510.594.3660 5212 Broadway | Oakland, CA | 94618 :(){ :|: & };: On Thu, May 9, 2019 at 6:49 AM Jason Vasche <jvasche@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@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha