For those who are curious, cait found my answer and it's on the wiki: http://wiki.koha-community.org/wiki/SQL_Reports_Library#Title.2FSubtitle_List
Nicole On Wed, May 25, 2011 at 1:49 PM, Nicole Engard <[email protected]> wrote: > Bev, > > I was thinking more along the lines of this type of thing: > http://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC > which I can do with the version of MySQL that I have installed. Just > wants sure how to fit it in to the report. > > Nicole > > On Wed, May 25, 2011 at 11:59 AM, Church, Beverly <[email protected]> wrote: >> 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: [email protected] >> skype: beverlychurch >> >> >> On Wed, May 25, 2011 at 12:10 PM, Nicole Engard <[email protected]> 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 >>> [email protected] >>> http://lists.katipo.co.nz/mailman/listinfo/koha >> >> > _______________________________________________ Koha mailing list http://koha-community.org [email protected] http://lists.katipo.co.nz/mailman/listinfo/koha
