Need report help for authorities
We're running 3.10 and enjoying the enhanced authority features, even if it showed up a problem with our data. Some of our records were imported with terms in the wrong fields so that linking to authority records didn't work (in the biblio as a topical heading when the authority record is for a corporate name, for example). So, ideally I need: 1) A list of authority records that are not attached to biblios ( it would be really useful if this included the term and type of authority). 2) A list of names and terms in authority controlled fields that aren't linked to authorities. I realize I'll probably have to build a separate list for each relevant MARC field. I tried the following from the Reports Library, but it timed out: Authors not in the Authorities Developer: MJ Ray, software.coop Module: Catalog Purpose: List of author names found on biblio records but not authority records Status: Production SELECT DISTINCT(author) AS heading FROM biblio WHERE author NOT IN (SELECT ExtractValue(marcxml,'//datafield[@tag="100"]/subfield[@code="a"]') AS heading FROM auth_header WHERE authtypecode='PERSO_NAME') ORDER BY heading Any thoughts? My IT Guru says he will poke around in the nether regions of the database tonight to see what he can find, but the more information I can gather on the state of the problem, the sooner we can fix it, I hope. Thanks and happy new year! -- Elaine Bradtke Data Wrangler VWML English Folk Dance and Song Society | http://www.efdss.org Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY Tel +44 (0) 20 7485 2206 (This number is for the English Folk Dance and Song Society in London, England. If you wish to phone me personally, send an e-mail first. I work off site) -------------------------------------------------------------------------- Registered Company No. 297142 Charity Registered in England and Wales No. 305999 --------------------------------------------------------------------------- "Writing about music is like dancing about architecture" --Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
Elaine,
So, ideally I need:
1) A list of authority records that are not attached to biblios ( it would be really useful if this included the term and type of authority).
2) A list of names and terms in authority controlled fields that aren't linked to authorities. I realize I'll probably have to build a separate list for each relevant MARC field.
for 1) I still don't have an answer, but for 2) you could find which fields are NOT linked looking at '9' subfield. That is the place used by Koha to fill in with the corresponding authid. So, for example, this query will extract entries in the 1XX fields that are not linked: SELECT DISTINCT heading FROM ( SELECT ExtractValue(marcxml, '//datafield[@tag="100"]/subfield[@code="a"]') AS heading FROM biblioitems WHERE length(ExtractValue(marcxml, '//datafield[@tag="100"]/subfield[@code="a"]')) != 0 AND length(ExtractValue(marcxml, '//datafield[@tag="100"]/subfield[@code="9"]')) = 0 UNION SELECT ExtractValue(marcxml, '//datafield[@tag="110"]/subfield[@code="a"]') AS heading FROM biblioitems WHERE length(ExtractValue(marcxml, '//datafield[@tag="110"]/subfield[@code="a"]')) != 0 AND length(ExtractValue(marcxml, '//datafield[@tag="110"]/subfield[@code="9"]')) = 0 UNION SELECT ExtractValue(marcxml, '//datafield[@tag="111"]/subfield[@code="a"]') AS heading FROM biblioitems WHERE length(ExtractValue(marcxml, '//datafield[@tag="111"]/subfield[@code="a"]')) != 0 AND length(ExtractValue(marcxml, '//datafield[@tag="111"]/subfield[@code="9"]')) = 0 UNION SELECT ExtractValue(marcxml, '//datafield[@tag="130"]/subfield[@code="a"]') AS heading FROM biblioitems WHERE length(ExtractValue(marcxml, '//datafield[@tag="130"]/subfield[@code="a"]')) != 0 AND length(ExtractValue(marcxml, '//datafield[@tag="130"]/subfield[@code="9"]')) = 0 ) AS heads ORDER BY heading; Not all records have 1XXa, so they will have length 0 in that field/subfield. Hope that helps, regards, Bernardo -- Bernardo Gonzalez Kriegel bgkriegel@gmail.com
Thanks Bernardo, that is very helpful. I think I can edit it for the other fields as needed. On Thu, Jan 3, 2013 at 2:10 PM, Bernardo Gonzalez Kriegel < bgkriegel@gmail.com> wrote:
extract entries in the 1XX fields that are not linked
-- Elaine Bradtke Data Wrangler VWML English Folk Dance and Song Society | http://www.efdss.org Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY Tel +44 (0) 20 7485 2206 (This number is for the English Folk Dance and Song Society in London, England. If you wish to phone me personally, send an e-mail first. I work off site) -------------------------------------------------------------------------- Registered Company No. 297142 Charity Registered in England and Wales No. 305999 --------------------------------------------------------------------------- "Writing about music is like dancing about architecture" --Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
participants (2)
-
Bernardo Gonzalez Kriegel -
Elaine Bradtke