[Koha] SQL help - Retrieve itemnumbers based on local subject keyword

[Josef Moravec]

Or without subquery:

SELECT i.itemnumber
FROM items i
JOIN biblioitems bi ON i.biblionumber = bi.biblionumber
WHERE
ExtractValue(bi.marcxml,'//datafield[@tag="690"]/subfield[@code>="a"]')
LIKE <<Enter keyword surrounded BY %>>



pá 21. 10. 2016 v 19:26 odesílatel Craig Butosi <craig.butosi at rcmusic.ca>
napsal:

> Hi all,
>
> Call of the dogs. A kind soul has recommended the following, and it worked:
>
> SELECT itemnumber
> FROM items
> WHERE biblionumber IN (SELECT biblionumber FROM biblioitems WHERE
> ExtractValue(marcxml, '//datafield[@tag="690"]/subfield[@code>="a"]') LIKE
> <<Enter keyword surrounded by %>>)
>
> Thanks all,
>
> Craig Butosi, MA, MLIS, B Mus (Hons.)
> LIBRARY SERVICES MANAGER
> THE ROYAL CONSERVATORY
> TELUS Centre for Performance and Learning
> 273 Bloor Street West
> Toronto, ON M5S 1W2
> 416.408.2824 x338
> www.rcmusic.ca
>
>
> -----Original Message-----
> From: Koha [mailto:koha-bounces at lists.katipo.co.nz] On Behalf Of Craig
> Butosi
> Sent: October-21-16 12:54 PM
> To: koha at lists.katipo.co.nz
> Subject: [Koha] SQL help - Retrieve itemnumbers based on local subject
> keyword
>
> Hi all,
>
> Koha 16.05.03 on Ubuntu 14.04 LTS (package install)
>
>
> I need to batch modify a subset of records in our db. I have to use
> itemnumbers rather than barcodes to populate the list because not all of
> the records to be modified have a barcode. I've tried the following SQL to
> no avail:
>
> SELECT itemnumber
> FROM items
> WHERE (SELECT biblionumber,
> ExtractValue(marcxml,'//datafield[@tag="690"]/subfield[@code>="a"]')
> LIKE <<Enter keyword surrounded BY %>>
>
> As you can see, I need to retrieve item numbers based on a keyword query
> from the 690$a field (MARC21).
>
> Would someone be so kind as to lend a hand?
>
> Much appreciated!
>
> C
>
> Craig Butosi, MA, MLIS, B Mus (Hons.)
> LIBRARY SERVICES MANAGER
> the royal conservatory
> TELUS Centre for Performance and Learning
> 273 Bloor Street West
> Toronto, ON M5S 1W2
> 416.408.2824 x338
> www.rcmusic.ca<http://www.rcmusic.ca/>
>
>
>
> Note: This email message and any attachments are intended only for the use
> of the individual to which it is addressed, and may contain information
> that is privileged, confidential and exempt from disclosure under
> applicable law. If the recipient of this email is not the intended
> recipient (or the employee or agent responsible for delivering the email to
> the intended recipient), you are hereby notified that any review,
> dissemination, distribution or copying or other use of this message is
> strictly prohibited. If you have received this communication in error,
> please notify the sender immediately by return email and delete this
> message and attachments from your system, thank you.
> _______________________________________________
> Koha mailing list  http://koha-community.org Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
>
> Note: This email message and any attachments are intended only for the use
> of the individual to which it is addressed, and may contain information
> that is privileged, confidential and exempt from disclosure under
> applicable law. If the recipient of this email is not the intended
> recipient (or the employee or agent responsible for delivering the email to
> the intended recipient), you are hereby notified that any review,
> dissemination, distribution or copying or other use of this message is
> strictly prohibited. If you have received this communication in error,
> please notify the sender immediately by return email and delete this
> message and attachments from your system, thank you.
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>