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 <p...@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 <p...@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 > _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha