MySQL 5.1 or greater is required. The queries should work either from the reports module, or directly on the MySQL command line.<br><br><br>-Ian<br><br><div class="gmail_quote">2011/5/25 Church, Beverly <span dir="ltr"><<a href="mailto:bchurch@liblime.com">bchurch@liblime.com</a>></span><br>
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><font size="2"><font face="verdana,sans-serif">Is this run at the MySQL level or in the reports module?<br>
<br><br clear="all"></font></font><br>* * * * <br><div class="im">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><div><div></div><div class="h5"><div class="gmail_quote">On Wed, May 25, 2011 at 4:23 PM, Nicole Engard <span dir="ltr"><<a href="mailto:nengard@gmail.com" target="_blank">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;">
I did try it and it does work. You need a more recent version of MySQL<br>
for it to work ... 5. something ... It's in Debian Squueze.<br>
<font color="#888888"><br>
Nicole<br>
</font><div><div></div><div><br>
On Wed, May 25, 2011 at 3:21 PM, Church, Beverly <<a href="mailto:bchurch@liblime.com" target="_blank">bchurch@liblime.com</a>> wrote:<br>
> Have you tried it? I can't get it to work.<br>
><br>
> Bev<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" target="_blank">bchurch@liblime.com</a><br>
> skype: beverlychurch<br>
><br>
><br>
> On Wed, May 25, 2011 at 3:08 PM, Nicole Engard <<a href="mailto:nengard@gmail.com" target="_blank">nengard@gmail.com</a>> wrote:<br>
>><br>
>> For those who are curious, cait found my answer and it's on the wiki:<br>
>><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>
>><br>
>> Nicole<br>
>><br>
>> On Wed, May 25, 2011 at 1:49 PM, Nicole Engard <<a href="mailto:nengard@gmail.com" target="_blank">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" target="_blank">bchurch@liblime.com</a>><br>
>> > wrote:<br>
>> >> Hi Nicole,<br>
>> >><br>
>> >> This query should work:<br>
>> >><br>
>> >> select title, IF( LOCATE('<datafield tag="245"', biblioitems.marcxml) =<br>
>> >> 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"',<br>
>> >> biblioitems.marcxml)) ><br>
>> >> LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield<br>
>> >> tag="245"',<br>
>> >> biblioitems.marcxml)), '', SUBSTRING( biblioitems.marcxml,<br>
>> >> 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<br>
>> >> code="b">',<br>
>> >> biblioitems.marcxml, LOCATE('<datafield tag="245"',<br>
>> >> 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" target="_blank">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" target="_blank">nengard@gmail.com</a>><br>
>> >> 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" target="_blank">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>
><br>
><br>
</div></div></blockquote></div><br>
</div></div><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></blockquote></div><br><br clear="all"><br>-- <br>Ian Walls<br>Lead Development Specialist<br>ByWater Solutions<br>ALA Booth 732<br>Phone # (888) 900-8944<br><a href="http://bywatersolutions.com" target="_blank">http://bywatersolutions.com</a><br>
<a href="mailto:ian.walls@bywatersolutions.com" target="_blank">ian.walls@bywatersolutions.com</a><br>Twitter: @sekjal<br>