[Koha] Report help needed

[Paul Hoffman]

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)