Yes, that did the trick! Thanks This is the revised report: SELECT DISTINCT biblionumber, heading FROM ( SELECT biblionumber, ExtractValue(metadata, '//datafield[@tag="100"]/subfield[@code="a"]') AS heading FROM biblio_metadata WHERE length(ExtractValue(metadata, '//datafield[@tag="100"]/subfield[@code="a"]')) != 0 AND length(ExtractValue(metadata, '//datafield[@tag="100"]/subfield[@code="9"]')) = 0 UNION SELECT biblionumber, ExtractValue(metadata, '//datafield[@tag="110"]/subfield[@code="a"]') AS heading FROM biblio_metadata WHERE length(ExtractValue(metadata, '//datafield[@tag="110"]/subfield[@code="a"]')) != 0 AND length(ExtractValue(metadata, '//datafield[@tag="110"]/subfield[@code="9"]')) = 0 UNION SELECT biblionumber, ExtractValue(metadata, '//datafield[@tag="111"]/subfield[@code="a"]') AS heading FROM biblio_metadata WHERE length(ExtractValue(metadata, '//datafield[@tag="111"]/subfield[@code="a"]')) != 0 AND length(ExtractValue(metadata, '//datafield[@tag="111"]/subfield[@code="9"]')) = 0 UNION SELECT biblionumber, ExtractValue(metadata, '//datafield[@tag="130"]/subfield[@code="a"]') AS heading FROM biblio_metadata WHERE length(ExtractValue(metadata, '//datafield[@tag="130"]/subfield[@code="a"]')) != 0 AND length(ExtractValue(metadata, '//datafield[@tag="130"]/subfield[@code="9"]')) = 0 ) AS heads ORDER BY heading Elaine Bradtke 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) On Fri, Jul 13, 2018 at 11:53 AM, Paul Hoffman <paul@flo.org> wrote:
On Fri, Jul 13, 2018 at 10:06:56AM -0700, Elaine Bradtke wrote:
This report finds Authors not linked to authorities. But it only lists their names (some of them are quite prolific, and sometimes it's only one biblio that needs checking). How can I make the following give the biblio number as well as the name?
SELECT DISTINCT heading FROM ( SELECT ExtractValue(metadata, '//datafield[@tag="100"]/subfield[@code="a"]') AS heading FROM biblio_metadata WHERE length(ExtractValue(metadata, '//datafield[@tag="100"]/subfield[@code="a"]')) != 0 AND length(ExtractValue(metadata, '//datafield[@tag="100"]/subfield[@code="9"]')) = 0 UNION [...]
I assume that you can just add biblionumber to the output everywhere that a heading appears, something like this (untested!):
SELECT DISTINCT biblionumber, heading FROM ( SELECT biblionumber, ExtractValue(metadata, '//datafield[@tag="100"]/subfield[@code="a"]') AS heading FROM biblio_metadata WHERE length(ExtractValue(metadata, '//datafield[@tag="100"]/subfield[@code="a"]')) != 0 AND length(ExtractValue(metadata, '//datafield[@tag="100"]/subfield[@code="9"]')) = 0 UNION [...]
Paul.
-- Paul Hoffman <paul@flo.org> Software Manager Fenway Libraries Online c/o Wentworth Institute of Technology 550 Huntington Ave. Boston, MA 02115 (617) 442-2384 (FLO main number) _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha