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.
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@lists.katipo.co.nz] On Behalf Of Craig Butosi Sent: October-21-16 12:54 PM To: koha@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@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.
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@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@lists.katipo.co.nz] On Behalf Of Craig Butosi Sent: October-21-16 12:54 PM To: koha@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@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@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
participants (2)
-
Craig Butosi -
Josef Moravec