Hi Nicole, This query should work:
select title, IF( LOCATE('<datafield tag="245"', biblioitems.marcxml) = 0 OR LOCATE('<subfield code="b">', biblioitems.marcxml, LOCATE('<datafield tag="245"', biblioitems.marcxml)) = 0 OR LOCATE('<subfield code="b">', biblioitems.marcxml, LOCATE('<datafield tag="245"', biblioitems.marcxml)) > LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield tag="245"', biblioitems.marcxml)), '', SUBSTRING( biblioitems.marcxml, LOCATE('<subfield code="b">', biblioitems.marcxml, LOCATE('<datafield tag="245"', biblioitems.marcxml)) + 19, LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield code="b">', biblioitems.marcxml, LOCATE('<datafield tag="245"', biblioitems.marcxml)) + 19) - (LOCATE('<subfield code="b">', biblioitems.marcxml, LOCATE('<datafield tag="245"', biblioitems.marcxml)) + 19))) AS 'Sub-title' FROM biblioitems, biblio where biblioitems.biblionumber = biblio.biblionumber and LOCATE('<datafield tag="245"', biblioitems.marcxml) != 0 ORDER BY title asc * * * * Beverly Church LibLime Project Manager phone: 1-888-564-2457 ext. 717 1-301-654-8088 ext. 292 email: bchu...@liblime.com skype: beverlychurch On Wed, May 25, 2011 at 12:10 PM, Nicole Engard <neng...@gmail.com> wrote: > I'm looking for some help with using the marc xml reports trick :) > > I need a report that shows the title and the subtitle, how would I do that? > > Thanks in advance! > Nicole > _______________________________________________ > Koha mailing list http://koha-community.org > Koha@lists.katipo.co.nz > http://lists.katipo.co.nz/mailman/listinfo/koha >
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha