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
