<font size="2"><font face="verdana,sans-serif">Have you tried it? I can't get it to work.<br><br>Bev<br clear="all"></font></font><br>* * * * <br>Beverly Church <br>LibLime Project Manager<br><br>phone: 1-888-564-2457 ext. 717 <br>
1-301-654-8088 ext. 292<br>email: <a href="mailto:bchurch@liblime.com" target="_blank">bchurch@liblime.com</a><br>skype: beverlychurch<br>
<br><br><div class="gmail_quote">On Wed, May 25, 2011 at 3:08 PM, Nicole Engard <span dir="ltr"><<a href="mailto:nengard@gmail.com">nengard@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
For those who are curious, cait found my answer and it's on the wiki:<br>
<a href="http://wiki.koha-community.org/wiki/SQL_Reports_Library#Title.2FSubtitle_List" target="_blank">http://wiki.koha-community.org/wiki/SQL_Reports_Library#Title.2FSubtitle_List</a><br>
<font color="#888888"><br>
Nicole<br>
</font><div><div></div><div class="h5"><br>
On Wed, May 25, 2011 at 1:49 PM, Nicole Engard <<a href="mailto:nengard@gmail.com">nengard@gmail.com</a>> wrote:<br>
> Bev,<br>
><br>
> I was thinking more along the lines of this type of thing:<br>
> <a href="http://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC" target="_blank">http://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC</a><br>
> which I can do with the version of MySQL that I have installed. Just<br>
> wants sure how to fit it in to the report.<br>
><br>
> Nicole<br>
><br>
> On Wed, May 25, 2011 at 11:59 AM, Church, Beverly <<a href="mailto:bchurch@liblime.com">bchurch@liblime.com</a>> wrote:<br>
>> Hi Nicole,<br>
>><br>
>> This query should work:<br>
>><br>
>> select title, IF( LOCATE('<datafield tag="245"', biblioitems.marcxml) = 0 OR<br>
>> LOCATE('<subfield code="b">', biblioitems.marcxml, LOCATE('<datafield<br>
>> tag="245"', biblioitems.marcxml)) = 0 OR LOCATE('<subfield code="b">',<br>
>> biblioitems.marcxml, LOCATE('<datafield tag="245"', biblioitems.marcxml)) ><br>
>> LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield tag="245"',<br>
>> biblioitems.marcxml)), '', SUBSTRING( biblioitems.marcxml, LOCATE('<subfield<br>
>> code="b">', biblioitems.marcxml, LOCATE('<datafield tag="245"',<br>
>> biblioitems.marcxml)) + 19, LOCATE('</subfield>', biblioitems.marcxml,<br>
>> LOCATE('<subfield code="b">', biblioitems.marcxml, LOCATE('<datafield<br>
>> tag="245"', biblioitems.marcxml)) + 19) - (LOCATE('<subfield code="b">',<br>
>> biblioitems.marcxml, LOCATE('<datafield tag="245"', biblioitems.marcxml)) +<br>
>> 19))) AS 'Sub-title' FROM biblioitems, biblio where<br>
>> biblioitems.biblionumber = biblio.biblionumber and LOCATE('<datafield<br>
>> tag="245"', biblioitems.marcxml) != 0 ORDER BY title asc<br>
>><br>
>><br>
>><br>
>> * * * *<br>
>> Beverly Church<br>
>> LibLime Project Manager<br>
>><br>
>> phone: 1-888-564-2457 ext. 717<br>
>> 1-301-654-8088 ext. 292<br>
>> email: <a href="mailto:bchurch@liblime.com">bchurch@liblime.com</a><br>
>> skype: beverlychurch<br>
>><br>
>><br>
>> On Wed, May 25, 2011 at 12:10 PM, Nicole Engard <<a href="mailto:nengard@gmail.com">nengard@gmail.com</a>> wrote:<br>
>>><br>
>>> I'm looking for some help with using the marc xml reports trick :)<br>
>>><br>
>>> I need a report that shows the title and the subtitle, how would I do<br>
>>> that?<br>
>>><br>
>>> Thanks in advance!<br>
>>> Nicole<br>
>>> _______________________________________________<br>
>>> Koha mailing list <a href="http://koha-community.org" target="_blank">http://koha-community.org</a><br>
>>> <a href="mailto:Koha@lists.katipo.co.nz">Koha@lists.katipo.co.nz</a><br>
>>> <a href="http://lists.katipo.co.nz/mailman/listinfo/koha" target="_blank">http://lists.katipo.co.nz/mailman/listinfo/koha</a><br>
>><br>
>><br>
><br>
</div></div></blockquote></div><br>