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_Lis... Nicole On Wed, May 25, 2011 at 1:49 PM, Nicole Engard <nengard@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 <bchurch@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: bchurch@liblime.com skype: beverlychurch
On Wed, May 25, 2011 at 12:10 PM, Nicole Engard <nengard@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