Try this: SELECT biblioitems.biblionumber, biblio.copyrightdate, biblioitems.marcxml FROM biblioitems JOIN biblio ON biblioitems.biblionumber=biblio.biblionumber WHERE ExtractValue( marcxml, '//datafield[@tag="505"]/*' ) = "" and biblio.copyrightdate is null and ExtractValue( marcxml, '//datafield[@tag="260"]/*' ) = "" and ExtractValue( marcxml, '//datafield[@tag="264"]/*' ) = "" ORDER BY biblio.copyrightdate desc
For more information you can read this: https://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC Regards, Ramiro 2017-03-08 2:30 GMT-03:00 Kerrie Stevens <kstev...@harvest.edu.au>: > I have a report that picks out items that have no contents (505) added and > also have no date entered. The problem is that the report is picking up > materials that do have dates entered in both the 260 or 264 fields, so it > is not working 100% correctly. > > Can anyone give me some pointers to get it working properly? > > SELECT biblioitems.biblionumber, biblio.copyrightdate, biblioitems.marcxml > FROM biblioitems > JOIN biblio > ON biblioitems.biblionumber=biblio.biblionumber > WHERE biblioitems.marcxml not like "%505%" and biblio.copyrightdate is null > ORDER BY biblio.copyrightdate desc > > Results that come back includes bibs with date entries in the 260 and 264 > fields - why would they be selected as having nothing in the date field, > when they clearly do? Eg: both formats: '1958' or '[19--]' might appear in > the fields > > Thanks for any help you may provide... > > > Kerrie Stevens > Harvest Bible College > _______________________________________________ > Koha mailing list http://koha-community.org > Koha@lists.katipo.co.nz > https://lists.katipo.co.nz/mailman/listinfo/koha > _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha