[Koha] Need report help for authorities

[Bernardo Gonzalez Kriegel]

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 at gmail.com