Koha 16.05 - SQL Report on Koha Control Numbers - Finding blank 001s
Hi all, Koha 16.05 o Ubuntu 14.04 LTS Package install Would a kind soul help me with a basic SQL report which compiles a list of all bibliographic records that have a blank 001, but which have a biblionumber? Columns selected for the report = biblionumber, title, author. I have tried a number of queries but can't seem to get the syntax correct. Many thanks! 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.
On 2/8/2017 11:13 AM, Craig Butosi wrote:
Hi all,
Koha 16.05 o Ubuntu 14.04 LTS Package install
Would a kind soul help me with a basic SQL report which compiles a list of all bibliographic records that have a blank 001, but which have a biblionumber? Columns selected for the report = biblionumber, title, author.
I have tried a number of queries but can't seem to get the syntax correct.
We have one that just gives you "clickable links" to allow editing the biblio: SELECT GROUP_CONCAT('<a target="_blank" href=\"/ cgi-bin/koha/cataloguing/addbiblio.pl?biblionumber=',biblionumber,'\">',biblionumber,'</a>' SEPARATOR ', ') AS BibNums, EXTRACTVALUE(marcxml,'//controlfield[@tag="001"]') AS Id FROM biblioitems GROUP BY Id HAVING count(Id) = 0; Best -- Paul
Hi Paul, Thanks very much for this. Your query actually helped me to identify my syntax problem. I was using "IS NOT NULL" in my WHERE clause, which includes the XML for 001, when I should have been using "= 0", as in: SELECT biblionumber FROM biblioitems WHERE EXTRACTVALUE(marcxml,'//controlfield[@tag="001"]') = 0 The above query successfully returns all biblionumbers with no 001s. Thanks for your time on this! All best, 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 Paul A Sent: February-08-17 11:54 AM To: koha@lists.katipo.co.nz Subject: Re: [Koha] Koha 16.05 - SQL Report on Koha Control Numbers - Finding blank 001s On 2/8/2017 11:13 AM, Craig Butosi wrote:
Hi all,
Koha 16.05 o Ubuntu 14.04 LTS Package install
Would a kind soul help me with a basic SQL report which compiles a list of all bibliographic records that have a blank 001, but which have a biblionumber? Columns selected for the report = biblionumber, title, author.
I have tried a number of queries but can't seem to get the syntax correct.
We have one that just gives you "clickable links" to allow editing the biblio: SELECT GROUP_CONCAT('<a target="_blank" href=\"/ cgi-bin/koha/cataloguing/addbiblio.pl?biblionumber=',biblionumber,'\">',biblionumber,'</a>' SEPARATOR ', ') AS BibNums, EXTRACTVALUE(marcxml,'//controlfield[@tag="001"]') AS Id FROM biblioitems GROUP BY Id HAVING count(Id) = 0; Best -- Paul _______________________________________________ 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.
On 2/8/2017 12:14 PM, Craig Butosi wrote:
Hi Paul,
Thanks very much for this. Your query actually helped me to identify my syntax problem. I was using "IS NOT NULL" in my WHERE clause, which includes the XML for 001, when I should have been using "= 0", as in:
SELECT biblionumber FROM biblioitems WHERE EXTRACTVALUE(marcxml,'//controlfield[@tag="001"]') = 0
The above query successfully returns all biblionumbers with no 001s.
Perhaps accidental serendipity? It's highly unlikely that you have any 001s with 0 as value, so it works... In fact my query looks for the COUNT, so "= 0" (no records) can be replaced by "> 1" and find duplicate records ;=} Warmest regards -- Paul
Thanks for your time on this!
All best,
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 Paul A Sent: February-08-17 11:54 AM To: koha@lists.katipo.co.nz Subject: Re: [Koha] Koha 16.05 - SQL Report on Koha Control Numbers - Finding blank 001s
On 2/8/2017 11:13 AM, Craig Butosi wrote:
Hi all,
Koha 16.05 o Ubuntu 14.04 LTS Package install
Would a kind soul help me with a basic SQL report which compiles a list of all bibliographic records that have a blank 001, but which have a biblionumber? Columns selected for the report = biblionumber, title, author.
I have tried a number of queries but can't seem to get the syntax correct.
We have one that just gives you "clickable links" to allow editing the biblio:
SELECT GROUP_CONCAT('<a target="_blank" href=\"/ cgi-bin/koha/cataloguing/addbiblio.pl?biblionumber=',biblionumber,'\">',biblionumber,'</a>' SEPARATOR ', ') AS BibNums, EXTRACTVALUE(marcxml,'//controlfield[@tag="001"]') AS Id FROM biblioitems GROUP BY Id HAVING count(Id) = 0;
Best -- Paul
_______________________________________________ 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.
participants (2)
-
Craig Butosi -
Paul A