[Koha] Report help

[Church, Beverly]

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 at liblime.com
skype: beverlychurch


On Wed, May 25, 2011 at 12:10 PM, Nicole Engard <nengard at 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 at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.katipo.co.nz/pipermail/koha/attachments/20110525/b4e19348/attachment.htm