Have you tried it? I can't get it to work. Bev
* * * * 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 3:08 PM, Nicole Engard <neng...@gmail.com> wrote: > 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 <neng...@gmail.com> 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 <bchu...@liblime.com> > 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: 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