[Koha] Report help needed

[Elaine Bradtke]

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 at 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 at 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 at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>