[Koha] New SQL Report - Terms not in the Authorities

[Cab Vinton]

Formatted a bit strangely on the SQL Reports page; hopefully cleaned up below.

Cab Vinton
Sanbornton Public Library

Developer: Adapted from a report by Bernardo Ganzalex Kriegel
Module: Catalog
Purpose: List of terms found on biblio records in 6XX fields that are
not linked to authority records
Status: Production

SELECT DISTINCT heading FROM (
SELECT ExtractValue(marcxml,'//datafield[@tag="650"]/subfield[@code="a"]')
AS heading
FROM biblioitems
WHERE length(ExtractValue(marcxml,'//datafield[@tag="650"]/subfield[@code="a"]'))
!= 0 AND
 length(ExtractValue(marcxml,'//datafield[@tag="650"]/subfield[@code="9"]'))
= 0
UNION
SELECT ExtractValue(marcxml,'//datafield[@tag="610"]/subfield[@code="a"]')
AS heading
FROM biblioitems
WHERE length(ExtractValue(marcxml,'//datafield[@tag="610"]/subfield[@code="a"]'))
!= 0 AND
 length(ExtractValue(marcxml,'//datafield[@tag="610"]/subfield[@code="9"]'))
= 0
UNION
SELECT ExtractValue(marcxml,'//datafield[@tag="600"]/subfield[@code="a"]')
AS heading
FROM biblioitems
WHERE length(ExtractValue(marcxml,'//datafield[@tag="600"]/subfield[@code="a"]'))
!= 0 AND
 length(ExtractValue(marcxml,'//datafield[@tag="600"]/subfield[@code="9"]'))
= 0
UNION
SELECT ExtractValue(marcxml,'//datafield[@tag="611"]/subfield[@code="a"]')
AS heading
FROM biblioitems
WHERE
 length(ExtractValue(marcxml,'//datafield[@tag="611"]/subfield[@code="a"]'))
!= 0 AND
 length(ExtractValue(marcxml,'//datafield[@tag="611"]/subfield[@code="9"]'))
= 0 ) AS heads
ORDER BY heading