[Koha] Report help

[Nicole Engard]

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_List

Nicole

On Wed, May 25, 2011 at 1:49 PM, Nicole Engard <nengard at 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 at 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 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
>>
>>
>