Archivado en 9 junio 2011

Chequeo de registros

9 junio, 2011

La siguiente es una consulta SQL que puede utilizarse en los Informes de Koha para listar los registros que contienen autores principales simultaneamente en campos 100, 110 y/o 111. Lo cual es incorrecto en MARC21.

[sql]
SELECT biblionumberurl as biblionumber,title as titulo FROM
(SELECT CONCAT(‘<a href="http://’,
(SELECT value FROM systempreferences WHERE variable=’staffClientBaseURL’),
‘/cgi-bin/koha/catalogue/detail.pl?biblionumber=’,
biblionumber,
‘">’,
biblionumber,
‘</a>’) AS ‘biblionumberurl’, biblionumber
FROM biblioitems
WHERE
(ExtractValue(biblioitems.marcxml,’//datafield[@tag="100"]/subfield[@code="a"]’)<>” AND
ExtractValue(biblioitems.marcxml,’//datafield[@tag="110"]/subfield[@code="a"]’)<>” AND
ExtractValue(biblioitems.marcxml,’//datafield[@tag="111"]/subfield[@code="a"]’)<>”)
OR
(ExtractValue(biblioitems.marcxml,’//datafield[@tag="100"]/subfield[@code="a"]’)<>” AND
ExtractValue(biblioitems.marcxml,’//datafield[@tag="110"]/subfield[@code="a"]’)<>”)
OR
(ExtractValue(biblioitems.marcxml,’//datafield[@tag="110"]/subfield[@code="a"]’)<>” AND
ExtractValue(biblioitems.marcxml,’//datafield[@tag="111"]/subfield[@code="a"]’)<>”)
OR
(ExtractValue(biblioitems.marcxml,’//datafield[@tag="100"]/subfield[@code="a"]’)<>” AND
ExtractValue(biblioitems.marcxml,’//datafield[@tag="111"]/subfield[@code="a"]’)<>”)
) url
LEFT JOIN
(SELECT biblionumber,title
FROM biblio
) tit
ON (url.biblionumber=tit.biblionumber)
[/sql]

This is a SQL query that can be used to create a Koha report. It lists all records that simultaneously contain a principal author in fields 100, 110 and/or 111, which is just not right in MARC21.

[sql]
SELECT biblionumberurl as biblionumber,title FROM
(SELECT CONCAT(‘<a href="http://’,
(SELECT value FROM systempreferences WHERE variable=’staffClientBaseURL’),
‘/cgi-bin/koha/catalogue/detail.pl?biblionumber=’,
biblionumber,
‘">’,
biblionumber,
‘</a>’) AS ‘biblionumberurl’, biblionumber
FROM biblioitems
WHERE
(ExtractValue(biblioitems.marcxml,’//datafield[@tag="100"]/subfield[@code="a"]’)<>” AND
ExtractValue(biblioitems.marcxml,’//datafield[@tag="110"]/subfield[@code="a"]’)<>” AND
ExtractValue(biblioitems.marcxml,’//datafield[@tag="111"]/subfield[@code="a"]’)<>”)
OR
(ExtractValue(biblioitems.marcxml,’//datafield[@tag="100"]/subfield[@code="a"]’)<>” AND
ExtractValue(biblioitems.marcxml,’//datafield[@tag="110"]/subfield[@code="a"]’)<>”)
OR
(ExtractValue(biblioitems.marcxml,’//datafield[@tag="110"]/subfield[@code="a"]’)<>” AND
ExtractValue(biblioitems.marcxml,’//datafield[@tag="111"]/subfield[@code="a"]’)<>”)
OR
(ExtractValue(biblioitems.marcxml,’//datafield[@tag="100"]/subfield[@code="a"]’)<>” AND
ExtractValue(biblioitems.marcxml,’//datafield[@tag="111"]/subfield[@code="a"]’)<>”)
) url
LEFT JOIN
(SELECT biblionumber,title
FROM biblio
) tit
ON (url.biblionumber=tit.biblionumber)
[/sql]