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
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@liblime.com skype: beverlychurch On Wed, May 25, 2011 at 12:10 PM, Nicole Engard <nengard@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
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@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@liblime.com skype: beverlychurch
On Wed, May 25, 2011 at 12:10 PM, Nicole Engard <nengard@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
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_Lis... Nicole On Wed, May 25, 2011 at 1:49 PM, Nicole Engard <nengard@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@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@liblime.com skype: beverlychurch
On Wed, May 25, 2011 at 12:10 PM, Nicole Engard <nengard@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
Have you tried it? I can't get it to work. Bev * * * * Beverly Church LibLime Project Manager phone: 1-888-564-2457 ext. 717 1-301-654-8088 ext. 292 email: bchurch@liblime.com skype: beverlychurch On Wed, May 25, 2011 at 3:08 PM, Nicole Engard <nengard@gmail.com> wrote:
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_Lis...
Nicole
On Wed, May 25, 2011 at 1:49 PM, Nicole Engard <nengard@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@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@liblime.com skype: beverlychurch
On Wed, May 25, 2011 at 12:10 PM, Nicole Engard <nengard@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
I did try it and it does work. You need a more recent version of MySQL for it to work ... 5. something ... It's in Debian Squueze. Nicole On Wed, May 25, 2011 at 3:21 PM, Church, Beverly <bchurch@liblime.com> wrote:
Have you tried it? I can't get it to work.
Bev
* * * * Beverly Church LibLime Project Manager
phone: 1-888-564-2457 ext. 717 1-301-654-8088 ext. 292 email: bchurch@liblime.com skype: beverlychurch
On Wed, May 25, 2011 at 3:08 PM, Nicole Engard <nengard@gmail.com> wrote:
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_Lis...
Nicole
On Wed, May 25, 2011 at 1:49 PM, Nicole Engard <nengard@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@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@liblime.com skype: beverlychurch
On Wed, May 25, 2011 at 12:10 PM, Nicole Engard <nengard@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
Is this run at the MySQL level or in the reports module? * * * * Beverly Church LibLime Project Manager phone: 1-888-564-2457 ext. 717 1-301-654-8088 ext. 292 email: bchurch@liblime.com skype: beverlychurch On Wed, May 25, 2011 at 4:23 PM, Nicole Engard <nengard@gmail.com> wrote:
I did try it and it does work. You need a more recent version of MySQL for it to work ... 5. something ... It's in Debian Squueze.
Nicole
On Wed, May 25, 2011 at 3:21 PM, Church, Beverly <bchurch@liblime.com> wrote:
Have you tried it? I can't get it to work.
Bev
* * * * Beverly Church LibLime Project Manager
phone: 1-888-564-2457 ext. 717 1-301-654-8088 ext. 292 email: bchurch@liblime.com skype: beverlychurch
On Wed, May 25, 2011 at 3:08 PM, Nicole Engard <nengard@gmail.com> wrote:
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_Lis...
Nicole
On Wed, May 25, 2011 at 1:49 PM, Nicole Engard <nengard@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@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@liblime.com skype: beverlychurch
On Wed, May 25, 2011 at 12:10 PM, Nicole Engard <nengard@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
MySQL 5.1 or greater is required. The queries should work either from the reports module, or directly on the MySQL command line. -Ian 2011/5/25 Church, Beverly <bchurch@liblime.com>
Is this run at the MySQL level or in the reports module?
* * * * Beverly Church LibLime Project Manager
phone: 1-888-564-2457 ext. 717 1-301-654-8088 ext. 292 email: bchurch@liblime.com skype: beverlychurch
On Wed, May 25, 2011 at 4:23 PM, Nicole Engard <nengard@gmail.com> wrote:
I did try it and it does work. You need a more recent version of MySQL for it to work ... 5. something ... It's in Debian Squueze.
Nicole
On Wed, May 25, 2011 at 3:21 PM, Church, Beverly <bchurch@liblime.com> wrote:
Have you tried it? I can't get it to work.
Bev
* * * * Beverly Church LibLime Project Manager
phone: 1-888-564-2457 ext. 717 1-301-654-8088 ext. 292 email: bchurch@liblime.com skype: beverlychurch
On Wed, May 25, 2011 at 3:08 PM, Nicole Engard <nengard@gmail.com> wrote:
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_Lis...
Nicole
On Wed, May 25, 2011 at 1:49 PM, Nicole Engard <nengard@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@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@liblime.com skype: beverlychurch
On Wed, May 25, 2011 at 12:10 PM, Nicole Engard <nengard@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
-- Ian Walls Lead Development Specialist ByWater Solutions ALA Booth 732 Phone # (888) 900-8944 http://bywatersolutions.com ian.walls@bywatersolutions.com Twitter: @sekjal
Bev, I wrote that gnarly marcxml-parsing query you offered below because at the time, LLEK was running on a pre-5.1 version of MySQL and I couldn't use the simpler ExtractValue(). I just ran cait's query that Nicole offered from the SQL Reports Library against our LLEK report server and it worked fine, but it bombs when I run it as a report in our production instance. I suspect LLEK's production version is still pre-5.1. Cheers, Daniel Grobani Library Technology Specialist John A. Graziano Memorial Library Samuel Merritt University -- View this message in context: http://koha.1045719.n5.nabble.com/Report-help-tp4425796p4426913.html Sent from the Koha - Discuss mailing list archive at Nabble.com.
participants (5)
-
Chris Cormack -
Church, Beverly -
Daniel Grobani -
Ian Walls -
Nicole Engard