Generating reports of values in 001
Hello, there have been previous posts on this issue but no satisfactory answers yet. I have posted the following query in Nicole Engard's ByWater Solutions website and she has referred me to this group in case the SQL queries <http://wiki.koha-community.org/wiki/SQL_Reports_Library#Withdrawn_Titles_Li st_to_Send_to_OCLC> she pointed me to don't work: "I still can't find a good way to generate a report of the values stored in the 001 tag, either from Koha's report wizard or creating SQL queries using "ExtractValue(marcxml,'//controlfield[@tag="001"]') AS OCLC Control Number". And there's no easy way to map a Koha field to 001 and/or 003 to be able to run reports that include these control fields. I would appreciate some help." Is this a data issue? Why don't the ExtractValue function work? There's data in the 'marcxml' or 'marc' fields when I simply select them in a query? Many, many thanks for any help. Glenda B. Claborne Library Director The American University of Iraq, Sulaimani (AUIS) +964 (0)53-330-1011, ext.1218 Skype: glenda.claborne
Hi Glenda, we are using 001 (Control number) excessively, and the ExtractValue should work ok. What's the exact problem you have? Can you describe it a bit more? What kind of report do you need? I notice that the report you link to is not using 001 but querying 035$a? SELECT b.title, b.author, ExtractValue(m.marcxml, '//datafield[@tag="035"]/subfield[@code="a"]') AS 'OCLC Number' FROM biblio b LEFT JOIN items i USING (biblionumber) LEFT JOIN biblioitems m USING (biblionumber) WHERE i.wthdrawn > 0 Katrin
-----Original Message----- From: koha-bounces@lists.katipo.co.nz [mailto:koha- bounces@lists.katipo.co.nz] On Behalf Of glenda.claborne Sent: Wednesday, June 19, 2013 2:30 PM To: koha@lists.katipo.co.nz Subject: [Koha] Generating reports of values in 001
Hello, there have been previous posts on this issue but no satisfactory answers yet. I have posted the following query in Nicole Engard's ByWater Solutions website and she has referred me to this group in case the SQL queries <http://wiki.koha- community.org/wiki/SQL_Reports_Library#Withdrawn_Titles_Li st_to_Send_to_OCLC> she pointed me to don't work:
"I still can't find a good way to generate a report of the values stored in the 001 tag, either from Koha's report wizard or creating SQL queries using "ExtractValue(marcxml,'//controlfield[@tag="001"]') AS OCLC Control Number". And there's no easy way to map a Koha field to 001 and/or 003 to be able to run reports that include these control fields. I would appreciate some help."
Is this a data issue? Why don't the ExtractValue function work? There's data in the 'marcxml' or 'marc' fields when I simply select them in a query?
Many, many thanks for any help.
Glenda B. Claborne
Library Director
The American University of Iraq, Sulaimani (AUIS)
+964 (0)53-330-1011, ext.1218
Skype: glenda.claborne
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Thanks Katrin and Jared. We may have to upgrade. We are using WorldCat Local as our discovery service and I want to make sure all our records have OCLC numbers in them (we store these in the 001) so that when users click on the catalog link in WorldCat, they'll get through to the catalog record. OCLC's batchload process takes care of most of this but there are patches that we have not covered. That's why I want to run a report to see which records still have those non-OCLC numbers in them. Also, we've had the default Koha OSt control number identifier in the 003 (which is quite wrong) but I don't know how to refer to that tag in my SQL query. Many thanks, Glenda -----Original Message----- From: Fischer, Katrin [mailto:Katrin.Fischer@bsz-bw.de] Sent: Wednesday, June 19, 2013 3:53 PM To: glenda.claborne; koha@lists.katipo.co.nz Subject: RE: [Koha] Generating reports of values in 001 Hi Glenda, we are using 001 (Control number) excessively, and the ExtractValue should work ok. What's the exact problem you have? Can you describe it a bit more? What kind of report do you need? I notice that the report you link to is not using 001 but querying 035$a? SELECT b.title, b.author, ExtractValue(m.marcxml, '//datafield[@tag="035"]/subfield[@code="a"]') AS 'OCLC Number' FROM biblio b LEFT JOIN items i USING (biblionumber) LEFT JOIN biblioitems m USING (biblionumber) WHERE i.wthdrawn > 0 Katrin
-----Original Message----- From: koha-bounces@lists.katipo.co.nz [mailto:koha- bounces@lists.katipo.co.nz] On Behalf Of glenda.claborne Sent: Wednesday, June 19, 2013 2:30 PM To: koha@lists.katipo.co.nz Subject: [Koha] Generating reports of values in 001
Hello, there have been previous posts on this issue but no satisfactory answers yet. I have posted the following query in Nicole Engard's ByWater Solutions website and she has referred me to this group in case the SQL queries <http://wiki.koha- community.org/wiki/SQL_Reports_Library#Withdrawn_Titles_Li st_to_Send_to_OCLC> she pointed me to don't work:
"I still can't find a good way to generate a report of the values stored in the 001 tag, either from Koha's report wizard or creating SQL queries using "ExtractValue(marcxml,'//controlfield[@tag="001"]') AS OCLC Control Number". And there's no easy way to map a Koha field to 001 and/or 003 to be able to run reports that include these control fields. I would appreciate some help."
Is this a data issue? Why don't the ExtractValue function work? There's data in the 'marcxml' or 'marc' fields when I simply select them in a query?
Many, many thanks for any help.
Glenda B. Claborne
Library Director
The American University of Iraq, Sulaimani (AUIS)
+964 (0)53-330-1011, ext.1218
Skype: glenda.claborne
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Glenda, Hello, there have been previous posts on this issue but no satisfactory
answers yet. I have posted the following query in Nicole Engard's ByWater Solutions website and she has referred me to this group in case the SQL queries < http://wiki.koha-community.org/wiki/SQL_Reports_Library#Withdrawn_Titles_Li st_to_Send_to_OCLC> she pointed me to don't work:
"I still can't find a good way to generate a report of the values stored in the 001 tag, either from Koha's report wizard or creating SQL queries using "ExtractValue(marcxml,'//controlfield[@tag="001"]') AS OCLC Control Number". And there's no easy way to map a Koha field to 001 and/or 003 to be able to run reports that include these control fields. I would appreciate some help."
Is this a data issue? Why don't the ExtractValue function work? There's data in the 'marcxml' or 'marc' fields when I simply select them in a query?
I'm not sure what the problem you are encountering is, but I wanted to make sure to point out that the 001 field does not necessarily contain the OCLC#, and if you want the OCLC# you should always get it from the 035, like this: ExtractValue(marcxml, '//datafield[@tag="035"]/subfield[@code="a" and contains(text(), "OCoLC")]') AS 'OCLC Accession Number'. I also noticed that in the quoted section in your e-mail 'OCLC Control Number' is not in quotes, but should be, which may be the problem you are encountering. A final suggestion is that the version of MySQL you have installed may be too old to support ExtractValue, in which case the only solution would be to upgrade. Regards, Jared -- Jared Camins-Esakov Bibliographer, C & P Bibliography Services, LLC (phone) +1 (917) 727-3445 (e-mail) jcamins@cpbibliography.com (web) http://www.cpbibliography.com/
participants (3)
-
Fischer, Katrin -
glenda.claborne -
Jared Camins-Esakov