Re: [Koha] Report help

[Liz Rea]

Hi,

Try this:

SELECT biblio.title, biblio.author, items.itemcallnumber, lcsh FROM
(SELECT biblionumber,
ExtractValue(marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]') AS
lcsh FROM biblioitems) AS subjects JOIN biblio USING(biblionumber) JOIN
items USING(biblionumber) WHERE lcsh LIKE "%KEYWORD%";


On 06/10/15 23:45, Charlotte Stock wrote:
> This must be simple to fix but I'm not fluent enough in Koha-speak to crack 
> the problem. Would anyone be able to help amend this SQL report, so that the 
> results show the title, author and call number for each entry, instead of a 
> hyperlinked biblionumber?
>
> All suggestions gratefully received!
>
>
>
> Charlotte Stock
> Librarian
> charlo...@bu-london.co.uk
>
> Boston University Study Abroad London
> 43 Harrington Gardens
> London, SW7 4JU
> T 0207 244 6255   F 0207 373 9430
> http://www.bu.edu/london/
>
> Registered company in England no. FC020819
> ---
> This email message has been delivered safely and archived online by Mimecast.
> For more information please visit http://www.mimecast.com 
> ---
>
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha

-- 
--
Liz Rea
Catalyst.Net Limited
Level 6, Catalyst House, 
150 Willis Street, Wellington.
P.O Box 11053, Manners Street, 
Wellington 6142

GPG: B149 A443 6B01 7386 C2C7 F481 B6c2 A49D 3726 38B7




signature.asc
Description: OpenPGP digital signature
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Katharina Penner]

We use 17.05, and so I have tried to substitute "marcxml" with
"biblio_metadata", like recommended at the Wiki page:
https://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC. But I
keep getting the following error message:

The following error was encountered:
The database returned the following error:
Unknown column 'biblio_metadata' in 'field list'
Please check the log for further details.
Return to previous page

So somehow probably more needs to happen than just substitution of words.
The 700's subfields are fine, thank you.

Katharina

On Wed, Feb 21, 2018 at 3:08 PM, Lee Jamison 
wrote:

> Hello Katharina, I believe something like this would work. Please note
> this should work on versions up to 16.11. It appears that starting in 17.05
> the marcxml attribute was removed from the biblioitems table, so the
> accurate query would depend on your Koha version if you'd care to specify
> what that would be. I am not familiar with the associated descriptions of
> the 700s subfields, so I referenced what appeared to be an old cataloging
> index to gather the identifiers.
>
> SELECT
> ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="a"]')
> AS Surname,
> ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="b"]')
> AS Firstname,
> ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="c"]')
> AS 'Additions',
> ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="f"]')
> AS Dates,
> ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="g"]')
> AS Expansion,
> ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="4"]')
> AS 'Relator Code'
> FROM biblioitems;
>
> On Wed, Feb 21, 2018 at 3:14 AM, Katharina Penner <
> katharina.pen...@gmail.com> wrote:
>
>> Dear all,
>>
>> I need to create a report to extract all metadata on all authors. I want
>> to
>> have each aspect - surname, name (or initials), dates, expansion of
>> initials, other possible fields - appear in a separate field/column, in a
>> delimited way. We're using UniMarc so these would be fields 700 a, b, c,
>> f,
>> g, $4. So far the data come up all in the same field.
>>
>> Please help!
>> Katharina Penner
>> ___
>> Koha mailing list  http://koha-community.org
>> Koha@lists.katipo.co.nz
>> https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
>
>
> --
> Lee Jamison
> Automated Library Technician
> Marywood University
> 570-961-4586 <(570)%20961-4586>
> ldjami...@marywood.edu
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Jonathan Druart]

Hello Katharina,

Do you see the "Update SQL" button on the report list? Did you try it?
It is supposed to update the query with the new table structure.

Regards,
Jonathan

On Wed, 21 Feb 2018 at 13:24 Katharina Penner 
wrote:

> We use 17.05, and so I have tried to substitute "marcxml" with
> "biblio_metadata", like recommended at the Wiki page:
> https://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC. But I
> keep getting the following error message:
>
> The following error was encountered:
> The database returned the following error:
> Unknown column 'biblio_metadata' in 'field list'
> Please check the log for further details.
> Return to previous page
>
> So somehow probably more needs to happen than just substitution of words.
> The 700's subfields are fine, thank you.
>
> Katharina
>
> On Wed, Feb 21, 2018 at 3:08 PM, Lee Jamison  >
> wrote:
>
> > Hello Katharina, I believe something like this would work. Please note
> > this should work on versions up to 16.11. It appears that starting in
> 17.05
> > the marcxml attribute was removed from the biblioitems table, so the
> > accurate query would depend on your Koha version if you'd care to specify
> > what that would be. I am not familiar with the associated descriptions of
> > the 700s subfields, so I referenced what appeared to be an old cataloging
> > index to gather the identifiers.
> >
> > SELECT
> > ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="a"]')
> > AS Surname,
> > ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="b"]')
> > AS Firstname,
> > ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="c"]')
> > AS 'Additions',
> > ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="f"]')
> > AS Dates,
> > ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="g"]')
> > AS Expansion,
> > ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="4"]')
> > AS 'Relator Code'
> > FROM biblioitems;
> >
> > On Wed, Feb 21, 2018 at 3:14 AM, Katharina Penner <
> > katharina.pen...@gmail.com> wrote:
> >
> >> Dear all,
> >>
> >> I need to create a report to extract all metadata on all authors. I want
> >> to
> >> have each aspect - surname, name (or initials), dates, expansion of
> >> initials, other possible fields - appear in a separate field/column, in
> a
> >> delimited way. We're using UniMarc so these would be fields 700 a, b, c,
> >> f,
> >> g, $4. So far the data come up all in the same field.
> >>
> >> Please help!
> >> Katharina Penner
> >> ___
> >> Koha mailing list  http://koha-community.org
> >> Koha@lists.katipo.co.nz
> >> https://lists.katipo.co.nz/mailman/listinfo/koha
> >>
> >
> >
> >
> > --
> > Lee Jamison
> > Automated Library Technician
> > Marywood University
> > 570-961-4586 <(570)%20961-4586>
> > ldjami...@marywood.edu
> >
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Katharina Penner]

Yes, sorry, I had done this before for other reports and now forgot to
return to it. That did the trick!!

Thank you!
Katharina

On Wed, Feb 21, 2018 at 6:16 PM, Jonathan Druart <
jonathan.dru...@bugs.koha-community.org> wrote:

> Hello Katharina,
>
> Do you see the "Update SQL" button on the report list? Did you try it?
> It is supposed to update the query with the new table structure.
>
> Regards,
> Jonathan
>
> On Wed, 21 Feb 2018 at 13:24 Katharina Penner 
> wrote:
>
>> We use 17.05, and so I have tried to substitute "marcxml" with
>> "biblio_metadata", like recommended at the Wiki page:
>> https://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC. But
>> I
>> keep getting the following error message:
>>
>> The following error was encountered:
>> The database returned the following error:
>> Unknown column 'biblio_metadata' in 'field list'
>> Please check the log for further details.
>> Return to previous page
>>
>> So somehow probably more needs to happen than just substitution of words.
>> The 700's subfields are fine, thank you.
>>
>> Katharina
>>
>> On Wed, Feb 21, 2018 at 3:08 PM, Lee Jamison <
>> ldjami...@maryu.marywood.edu>
>> wrote:
>>
>> > Hello Katharina, I believe something like this would work. Please note
>> > this should work on versions up to 16.11. It appears that starting in
>> 17.05
>> > the marcxml attribute was removed from the biblioitems table, so the
>> > accurate query would depend on your Koha version if you'd care to
>> specify
>> > what that would be. I am not familiar with the associated descriptions
>> of
>> > the 700s subfields, so I referenced what appeared to be an old
>> cataloging
>> > index to gather the identifiers.
>> >
>> > SELECT
>> > ExtractValue(marcxml,'//datafield[@tag="700"]/
>> subfield[@code>="a"]')
>> > AS Surname,
>> > ExtractValue(marcxml,'//datafield[@tag="700"]/
>> subfield[@code>="b"]')
>> > AS Firstname,
>> > ExtractValue(marcxml,'//datafield[@tag="700"]/
>> subfield[@code>="c"]')
>> > AS 'Additions',
>> > ExtractValue(marcxml,'//datafield[@tag="700"]/
>> subfield[@code>="f"]')
>> > AS Dates,
>> > ExtractValue(marcxml,'//datafield[@tag="700"]/
>> subfield[@code>="g"]')
>> > AS Expansion,
>> > ExtractValue(marcxml,'//datafield[@tag="700"]/
>> subfield[@code>="4"]')
>> > AS 'Relator Code'
>> > FROM biblioitems;
>> >
>> > On Wed, Feb 21, 2018 at 3:14 AM, Katharina Penner <
>> > katharina.pen...@gmail.com> wrote:
>> >
>> >> Dear all,
>> >>
>> >> I need to create a report to extract all metadata on all authors. I
>> want
>> >> to
>> >> have each aspect - surname, name (or initials), dates, expansion of
>> >> initials, other possible fields - appear in a separate field/column,
>> in a
>> >> delimited way. We're using UniMarc so these would be fields 700 a, b,
>> c,
>> >> f,
>> >> g, $4. So far the data come up all in the same field.
>> >>
>> >> Please help!
>> >> Katharina Penner
>> >> ___
>> >> Koha mailing list  http://koha-community.org
>> >> Koha@lists.katipo.co.nz
>> >> https://lists.katipo.co.nz/mailman/listinfo/koha
>> >>
>> >
>> >
>> >
>> > --
>> > Lee Jamison
>> > Automated Library Technician
>> > Marywood University
>> > 570-961-4586 <(570)%20961-4586> <(570)%20961-4586>
>> > ldjami...@marywood.edu
>> >
>> ___
>> Koha mailing list  http://koha-community.org
>> Koha@lists.katipo.co.nz
>> https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Eric Phetteplace]

Hi Mutasem,

Can you clarify what you mean by "number of search book in koha"? I can
think of a few different interpretations, including:

1) number of times someone searched the OPAC for a book
2) number of books that be retrieved by a search

#1 cannot be answered with a report, as far as I know, but would instead
require web analytics that track how often a search URL is visited. If you
have Google Analytics set up for your catalog, you can see how often URLs
like "cgi-bin/koha/opac-search.pl?q=" were accessed.

2) can be very simply or rather lengthy depending on what you want to
include. At its simplest, you can just count the number of bibliographic
records in your Koha database:

SELECT COUNT(*)
FROM biblio

If you want something a bit more refined, for instance only biblios that
have attached items or perhaps the number items with a "BOOK" type, then
that has to be modified but it's hard to know what you want based on your
original message.

Best,

ERIC PHETTEPLACE

Systems Librarian


libraries.cca.edu | vault.cca.edu | 510.594.3660

5212 Broadway, Oakland, CA 94618

 8th St., San Francisco, CA 94107

Preferred Pronoun(s): he/him

:(){ :|: & };:

2018-03-19 0:04 GMT-07:00 Mutasem Al Shami :

> Hi,
>
> any one can help me, i need report to count number of search book in koha.
>
> thank,
> Mutasem
>
> --
>
>
> --
>
> Disclaimer: This message and its attachment, if any, are confidential and
> may contain legally privileged information. If you are not the intended
> recipient, please contact the sender immediately and delete this message
> and its attachment, if any, from your system. You should not copy this
> message or disclose its contents to any other person or use it for any
> purpose. Statements and opinions expressed in this e-mail are those of the
> sender, and do not necessarily reflect those of Al Maarefa College (MCST).
> All opinions and/or views expressed in this email are solely those of the
> author and do not necessarily represent those of MCST, MCSTaccepts no
> liability for damage caused by any virus transmitted by this email.
>
>   تنويه: هذه الرسالة ومرفقاتها (إن وجدت) تمثل وثيقة سرية قد تحتوي على
> معلومات تتمتع بحماية وحصانة قانونية. إذا لم تكن الشخص المعني بهذه الرسالة
> يجب عليك تنبيه المُرسل بخطأ وصولها إليك، وحذف الرسالة ومرفقاتها. ولا يجوز
> لك نسخ هذه الرسالة أو مرفقاتها أو أي جزء منها، أو البوح بمحتوياتها لأي شخص
> أو استعمالها لأي غرض. علماً بأن الآراء والإفادات التي تحويها هذه الرسالة
> تعبر فقط عن رأي المُرسل وليس بالضرورة رأي كلية المعرفة، ولا تتحمل كلية
> المعرفة أي مسئولية عن الأضرار الناتجة عن أي فيروسات قد يحملها هذا البريد.
>
>
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Caroline Cyr-La-Rose]

Hi David,

Did you try with the statistics wizards? If you go in Reports > 
Statistics wizards > Patrons and then choose your first attribute "CC" 
as a row and "CD" as a column, it should give you a table with all the 
different combinations and the count of patrons with that combination.


Caroline

Caroline Cyr La Rose, M.S.I.
Bibl. prof. / Chargée de la formation et du soutien

Tél.  : 1 (833) 465-4276
caroline.cyr-la-r...@inlibro.com 

inLibro | pour esprit libre | www.inLibro.com 
Le 2018-08-03 à 11:57, David Hughes a écrit :

Hi,

I want to create a report that gives a grand total of records with various
combinations of two different patron attributes.

I have two different borrower_attributes.code values "CC" and "CD" and I
want to output the grand totals of records with all permutations of
borrower_attributes.attribute that have these codes.  So if
borrower_attributes.code CC has values 1, 2, 3 etc and
borrower_attributes.code CD has values A, B C  I'd want to output the grand
totals of all records having

CC value "1" CD value "A"
CC value "1" CD value "B"
CC value "1" CD value "C"
..
CC value "2" CD value "A"
CC value "2" CD value "B"

etc

This is outwith the scope of my very limited SQL, so I'd appreciate any
help that the community would be able to offer.  Thanks.

Kind regards

David Hughes

Systems Librarian
Dublin Business School
13-14 Aungier St.
Dublin 2
Ireland

00 353 1 417 8744

david.hug...@dbs.ie
*http://library.dbs.ie *



___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[David Hughes]

Thanks so much Caroline.  Perhaps I should look at the statistics wizards
more often!

Kind regards

David Hughes

Systems Librarian
Dublin Business School
13-14 Aungier St.
Dublin 2
Ireland

00 353 1 417 8744

david.hug...@dbs.ie
*http://library.dbs.ie *

On 3 August 2018 at 18:29, Caroline Cyr-La-Rose <
caroline.cyr-la-r...@inlibro.com> wrote:

> Hi David,
>
> Did you try with the statistics wizards? If you go in Reports > Statistics
> wizards > Patrons and then choose your first attribute "CC" as a row and
> "CD" as a column, it should give you a table with all the different
> combinations and the count of patrons with that combination.
>
> Caroline
> Caroline Cyr La Rose, M.S.I.
> Bibl. prof. / Chargée de la formation et du soutien
>
> Tél.  : 1 (833) 465-4276
> caroline.cyr-la-r...@inlibro.com
> inLibro | pour esprit libre | www.inLibro.com
> Le 2018-08-03 à 11:57, David Hughes a écrit :
>
> Hi,
>
> I want to create a report that gives a grand total of records with various
> combinations of two different patron attributes.
>
> I have two different borrower_attributes.code values "CC" and "CD" and I
> want to output the grand totals of records with all permutations of
> borrower_attributes.attribute that have these codes.  So if
> borrower_attributes.code CC has values 1, 2, 3 etc and
> borrower_attributes.code CD has values A, B C  I'd want to output the grand
> totals of all records having
>
> CC value "1" CD value "A"
> CC value "1" CD value "B"
> CC value "1" CD value "C"
> ..
> CC value "2" CD value "A"
> CC value "2" CD value "B"
>
> etc
>
> This is outwith the scope of my very limited SQL, so I'd appreciate any
> help that the community would be able to offer.  Thanks.
>
> Kind regards
>
> David Hughes
>
> Systems Librarian
> Dublin Business School
> 13-14 Aungier St.
> Dublin 2
> Ireland
>
> 00 353 1 417 8744
> david.hug...@dbs.ie
> *http://library.dbs.ie  
> *
>
>
>
>

-- 


*Data Protection: *The protection of your data is important to us.  
Please see our Privacy Notice  for more 
information on how we handle your data.




___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Nicole Engard]

Sorry - I was wrong - not patrons with overdues, but expired patrons -
that's what I'm looking for.


On Wed, Apr 10, 2013 at 1:25 PM, Nicole Engard  wrote:

> I'm trying to get a report to show patrons with overdues and if there is a
> guarantor I want that to show too. But what's happening is the Guarantees
> are showing as the Guarantors and the Guarantor is showing 2 times cause he
> has 2 Guarantees - can someone help me with my SQL:
>
>
> SELECT p.categorycode, p.dateofbirth, p.cardnumber, p.surname,
> p.firstname, p.dateexpiry,  IFNULL(concat(g.surname, ', ', g.firstname, '
> (', g.cardnumber, ')'),'') as guarantor, p.relationship,
> FORMAT(SUM(a.amountoutstanding),2) as due
> FROM borrowers p
> LEFT JOIN accountlines a USING (borrowernumber)
> left join borrowers g on (g.guarantorid=p.borrowernumber)
> WHERE p.dateexpiry < NOW() and p.surname = 'Meave'
> group by p.borrowernumber, g.borrowernumber
> ORDER BY p.dateexpiry asc ;
>
>
>
>
>
> +--+-++-+---++-+--++
> | categorycode | dateofbirth | cardnumber | surname | firstname |
> dateexpiry | guarantor   | relationship | due|
>
> +--+-++-+---++-+--++
> | TRES | 1996-08-24  | P0012129   | Meave   | Melina-Maria  |
> 2008-12-04 | | parent   | 5.90   |
> | TRES | 2000-08-30  | P0012128   | Meave   | Phoenix-Jonas |
> 2008-12-04 | | parent   | 2.30   |
> | STRICT   | 1956-09-23  | P0012127   | Meave   | Jose  |
> 2008-12-04 | Meave, Melina-Maria (P0012129)  |  | 351.53 |
> | STRICT   | 1956-09-23  | P0012127   | Meave   | Jose  |
> 2008-12-04 | Meave, Phoenix-Jonas (P0012128) |  | 351.53 |
>
> +--+-++-+---++-+--++
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Bernardo Gonzalez Kriegel]

Nicole,
what you need to match is p(atron).guarantorid with
g(uarantor).borrowernumber,
not g(uarantor).guarantorid with p(atron).borrowernumber
that is what causing you to get an inverted list.

For example this query will list all guarantees with corresponding
guarantor (if not null),

SELECT
patron.surname, patron.firstname, patron.relationship,
patron.guarantorid,
guarantor.borrowernumber, guarantor.surname, guarantor.firstname
FROM
borrowers as patron
LEFT JOIN
borrowers as guarantor
ON ( patron.guarantorid = guarantor.borrowernumber )
WHERE patron.guarantorid IS NOT NULL;


Regards,
Bernardo

-- 
Bernardo Gonzalez Kriegel
bgkrie...@gmail.com


On Wed, Apr 10, 2013 at 2:28 PM, Nicole Engard  wrote:

> Sorry - I was wrong - not patrons with overdues, but expired patrons -
> that's what I'm looking for.
>
>
> On Wed, Apr 10, 2013 at 1:25 PM, Nicole Engard  wrote:
>
> > I'm trying to get a report to show patrons with overdues and if there is
> a
> > guarantor I want that to show too. But what's happening is the Guarantees
> > are showing as the Guarantors and the Guarantor is showing 2 times cause
> he
> > has 2 Guarantees - can someone help me with my SQL:
> >
> >
> > SELECT p.categorycode, p.dateofbirth, p.cardnumber, p.surname,
> > p.firstname, p.dateexpiry,  IFNULL(concat(g.surname, ', ', g.firstname, '
> > (', g.cardnumber, ')'),'') as guarantor, p.relationship,
> > FORMAT(SUM(a.amountoutstanding),2) as due
> > FROM borrowers p
> > LEFT JOIN accountlines a USING (borrowernumber)
> > left join borrowers g on (g.guarantorid=p.borrowernumber)
> > WHERE p.dateexpiry < NOW() and p.surname = 'Meave'
> > group by p.borrowernumber, g.borrowernumber
> > ORDER BY p.dateexpiry asc ;
> >
> >
> >
> >
> >
> >
> +--+-++-+---++-+--++
> > | categorycode | dateofbirth | cardnumber | surname | firstname |
> > dateexpiry | guarantor   | relationship | due|
> >
> >
> +--+-++-+---++-+--++
> > | TRES | 1996-08-24  | P0012129   | Meave   | Melina-Maria  |
> > 2008-12-04 | | parent   | 5.90   |
> > | TRES | 2000-08-30  | P0012128   | Meave   | Phoenix-Jonas |
> > 2008-12-04 | | parent   | 2.30   |
> > | STRICT   | 1956-09-23  | P0012127   | Meave   | Jose  |
> > 2008-12-04 | Meave, Melina-Maria (P0012129)  |  | 351.53 |
> > | STRICT   | 1956-09-23  | P0012127   | Meave   | Jose  |
> > 2008-12-04 | Meave, Phoenix-Jonas (P0012128) |  | 351.53 |
> >
> >
> +--+-++-+---++-+--++
> >
> ___
> 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

Re: [Koha] Report Help

[Nicole Engard]

Thank you so much - so simple!! :)


On Wed, Apr 10, 2013 at 2:45 PM, Bernardo Gonzalez Kriegel <
bgkrie...@gmail.com> wrote:

> Nicole,
> what you need to match is p(atron).guarantorid with
> g(uarantor).borrowernumber,
> not g(uarantor).guarantorid with p(atron).borrowernumber
> that is what causing you to get an inverted list.
>
> For example this query will list all guarantees with corresponding
> guarantor (if not null),
>
> SELECT
> patron.surname, patron.firstname, patron.relationship,
> patron.guarantorid,
> guarantor.borrowernumber, guarantor.surname, guarantor.firstname
> FROM
> borrowers as patron
> LEFT JOIN
> borrowers as guarantor
> ON ( patron.guarantorid = guarantor.borrowernumber )
> WHERE patron.guarantorid IS NOT NULL;
>
>
> Regards,
> Bernardo
>
> --
> Bernardo Gonzalez Kriegel
> bgkrie...@gmail.com
>
>
> On Wed, Apr 10, 2013 at 2:28 PM, Nicole Engard  wrote:
>
>> Sorry - I was wrong - not patrons with overdues, but expired patrons -
>> that's what I'm looking for.
>>
>>
>> On Wed, Apr 10, 2013 at 1:25 PM, Nicole Engard  wrote:
>>
>> > I'm trying to get a report to show patrons with overdues and if there
>> is a
>> > guarantor I want that to show too. But what's happening is the
>> Guarantees
>> > are showing as the Guarantors and the Guarantor is showing 2 times
>> cause he
>> > has 2 Guarantees - can someone help me with my SQL:
>> >
>> >
>> > SELECT p.categorycode, p.dateofbirth, p.cardnumber, p.surname,
>> > p.firstname, p.dateexpiry,  IFNULL(concat(g.surname, ', ', g.firstname,
>> '
>> > (', g.cardnumber, ')'),'') as guarantor, p.relationship,
>> > FORMAT(SUM(a.amountoutstanding),2) as due
>> > FROM borrowers p
>> > LEFT JOIN accountlines a USING (borrowernumber)
>> > left join borrowers g on (g.guarantorid=p.borrowernumber)
>> > WHERE p.dateexpiry < NOW() and p.surname = 'Meave'
>> > group by p.borrowernumber, g.borrowernumber
>> > ORDER BY p.dateexpiry asc ;
>> >
>> >
>> >
>> >
>> >
>> >
>> +--+-++-+---++-+--++
>> > | categorycode | dateofbirth | cardnumber | surname | firstname |
>> > dateexpiry | guarantor   | relationship | due|
>> >
>> >
>> +--+-++-+---++-+--++
>> > | TRES | 1996-08-24  | P0012129   | Meave   | Melina-Maria  |
>> > 2008-12-04 | | parent   | 5.90   |
>> > | TRES | 2000-08-30  | P0012128   | Meave   | Phoenix-Jonas |
>> > 2008-12-04 | | parent   | 2.30   |
>> > | STRICT   | 1956-09-23  | P0012127   | Meave   | Jose  |
>> > 2008-12-04 | Meave, Melina-Maria (P0012129)  |  | 351.53 |
>> > | STRICT   | 1956-09-23  | P0012127   | Meave   | Jose  |
>> > 2008-12-04 | Meave, Phoenix-Jonas (P0012128) |  | 351.53 |
>> >
>> >
>> +--+-++-+---++-+--++
>> >
>> ___
>> 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

Re: [Koha] Report Help

[Manos PETRIDIS]

As the original request was "I'm trying to get a report to show patrons with
overdues and if there is a guarantor I want that to show too", might I
propose the following modification to Bernardo Gonzalez Kriegel's reply, so
that all patrons are shown in a single report:

select * from
(
SELECT
patron.borrowernumber   as borrower, 
patron.surname  as Surname, 
patron.firstnameas Firstname, 
patron.relationship as relationship, 
guarantor.borrowernumberas guarantor, 
guarantor.surname   as guarantor_Surname, 
guarantor.firstname as guarantor_Firstname
FROM
borrowers as patron
LEFT JOIN
borrowers as guarantor
ON ( patron.guarantorid = guarantor.borrowernumber ) WHERE
patron.guarantorid IS NOT NULL

UNION

SELECT
patron.borrowernumber   as borrower, 
patron.surname  as Surname, 
patron.firstnameas Firstname, 
patron.relationship as relationship, 
'-' as guarantor, 
'-' as guarantor_Surname, 
'-' as guarantor_Firstname
FROM
borrowers as patron
WHERE patron.guarantorid IS NULL
)   tmp
order by tmp.Surname, tmp.Firstname;


Kind regards,
Manos Petridis


-Original Message-
From: koha-boun...@lists.katipo.co.nz
[mailto:koha-boun...@lists.katipo.co.nz] On Behalf Of Bernardo Gonzalez
Kriegel
Sent: Wednesday, April 10, 2013 9:45 PM
To: Nicole Engard
Cc: Koha
Subject: Re: [Koha] Report Help

Nicole,
what you need to match is p(atron).guarantorid with
g(uarantor).borrowernumber, not g(uarantor).guarantorid with
p(atron).borrowernumber that is what causing you to get an inverted list.

For example this query will list all guarantees with corresponding guarantor
(if not null),

SELECT
patron.surname, patron.firstname, patron.relationship,
patron.guarantorid,
guarantor.borrowernumber, guarantor.surname, guarantor.firstname FROM
borrowers as patron
LEFT JOIN
borrowers as guarantor
ON ( patron.guarantorid = guarantor.borrowernumber ) WHERE
patron.guarantorid IS NOT NULL;


Regards,
Bernardo

--
Bernardo Gonzalez Kriegel
bgkrie...@gmail.com


On Wed, Apr 10, 2013 at 2:28 PM, Nicole Engard  wrote:

> Sorry - I was wrong - not patrons with overdues, but expired patrons - 
> that's what I'm looking for.
>
>
> On Wed, Apr 10, 2013 at 1:25 PM, Nicole Engard  wrote:
>
> > I'm trying to get a report to show patrons with overdues and if 
> > there is
> a
> > guarantor I want that to show too. But what's happening is the 
> > Guarantees are showing as the Guarantors and the Guarantor is 
> > showing 2 times cause
> he
> > has 2 Guarantees - can someone help me with my SQL:
> >
> >
> > SELECT p.categorycode, p.dateofbirth, p.cardnumber, p.surname, 
> > p.firstname, p.dateexpiry,  IFNULL(concat(g.surname, ', ', g.firstname,
'
> > (', g.cardnumber, ')'),'') as guarantor, p.relationship,
> > FORMAT(SUM(a.amountoutstanding),2) as due FROM borrowers p LEFT JOIN 
> > accountlines a USING (borrowernumber) left join borrowers g on 
> > (g.guarantorid=p.borrowernumber) WHERE p.dateexpiry < NOW() and 
> > p.surname = 'Meave'
> > group by p.borrowernumber, g.borrowernumber ORDER BY p.dateexpiry 
> > asc ;
> >
> >
> >
> >
> >
> >
>
+--+-++-+---+---
-+-+--++
> > | categorycode | dateofbirth | cardnumber | surname | firstname |
> > dateexpiry | guarantor   | relationship | due|
> >
> >
>
+--+-++-+---+---
-+-+--++
> > | TRES | 1996-08-24  | P0012129   | Meave   | Melina-Maria  |
> > 2008-12-04 | | parent   | 5.90   |
> > | TRES | 2000-08-30  | P0012128   | Meave   | Phoenix-Jonas |
> > 2008-12-04 | | parent   | 2.30   |
> > | STRICT   | 1956-09-23  | P0012127   | Meave   | Jose  |
> > 2008-12-04 | Meave, Melina-Maria (P0012129)  |  | 351.53 |
> > | STRICT   | 1956-09-23  | P0012127   | Meave   | Jose  |
> > 2008-12-04 | Meave, Phoenix-Jonas (P0012128) |  | 351.53 |
> >
> >
>
+--+-++-+---+---
-+-+--++
> >
> ___
> 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

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Bernardo Gonzalez Kriegel]

Nicole,
you want for each guarantor it's guarantees, Q: it's ok to repeat guarantor
information on each guarantee tuple?

you could use something like this (put any data you want in first select,
and change order by if needed)

SELECT
  IFNULL(concat(g.surname, ', ', g.firstname, ' (',g.cardnumber, ')'),'')
AS guarantor,
  IFNULL(concat(p.surname, ', ', p.firstname, ' (',p.cardnumber, ')'),'')
AS guarantee
FROM
(
  SELECT *
  FROM borrowers
  WHERE guarantorid  IS NOT NULL
) AS p
LEFT JOIN borrowers AS g
  ON p.guarantorid  = g.borrowernumber
ORDER BY g.borrowernumber
;

Bernardo

-- 
Bernardo Gonzalez Kriegel
bgkrie...@gmail.com


On Mon, Apr 22, 2013 at 4:18 PM, Nicole Engard  wrote:

> I'm looking to change this report around.  It shows patrons with their
> guarantor information.  I want a report that shows me the guarantees for
> each guarantor instead.
>
>
> SELECT p.categorycode, p.dateofbirth, p.cardnumber, p.surname, p.firstname,
> p.dateexpiry,  IFNULL(concat(g.surname, ', ', g.firstname, ' (',
> g.cardnumber, ')'),'') AS guarantor, p.relationship,
> ifnull(FORMAT(SUM(a.amountoutstanding),2),'0.00') AS due
> FROM borrowers p
> LEFT JOIN accountlines a USING (borrowernumber)
> LEFT JOIN borrowers g ON (p.guarantorid=g.borrowernumber)
> WHERE p.dateexpiry < NOW()
> GROUP BY p.borrowernumber
> ORDER BY p.dateexpiry ASC
>
>
>
> 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

Re: [Koha] Report Help

[Nicole Engard]

Thank you! That's a step in the right direction :)  I have shared it (in
your name) on the Koha wiki for others to benefit from.

Nicole


On Mon, Apr 22, 2013 at 9:19 PM, Bernardo Gonzalez Kriegel <
bgkrie...@gmail.com> wrote:

> Nicole,
> you want for each guarantor it's guarantees, Q: it's ok to repeat
> guarantor information on each guarantee tuple?
>
> you could use something like this (put any data you want in first select,
> and change order by if needed)
>
> SELECT
>   IFNULL(concat(g.surname, ', ', g.firstname, ' (',g.cardnumber, ')'),'')
> AS guarantor,
>   IFNULL(concat(p.surname, ', ', p.firstname, ' (',p.cardnumber, ')'),'')
> AS guarantee
> FROM
> (
>   SELECT *
>   FROM borrowers
>   WHERE guarantorid  IS NOT NULL
> ) AS p
> LEFT JOIN borrowers AS g
>   ON p.guarantorid  = g.borrowernumber
> ORDER BY g.borrowernumber
> ;
>
> Bernardo
>
> --
> Bernardo Gonzalez Kriegel
> bgkrie...@gmail.com
>
>
> On Mon, Apr 22, 2013 at 4:18 PM, Nicole Engard  wrote:
>
>> I'm looking to change this report around.  It shows patrons with their
>> guarantor information.  I want a report that shows me the guarantees for
>> each guarantor instead.
>>
>>
>> SELECT p.categorycode, p.dateofbirth, p.cardnumber, p.surname,
>> p.firstname,
>> p.dateexpiry,  IFNULL(concat(g.surname, ', ', g.firstname, ' (',
>> g.cardnumber, ')'),'') AS guarantor, p.relationship,
>> ifnull(FORMAT(SUM(a.amountoutstanding),2),'0.00') AS due
>> FROM borrowers p
>> LEFT JOIN accountlines a USING (borrowernumber)
>> LEFT JOIN borrowers g ON (p.guarantorid=g.borrowernumber)
>> WHERE p.dateexpiry < NOW()
>> GROUP BY p.borrowernumber
>> ORDER BY p.dateexpiry ASC
>>
>>
>>
>> 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

Re: [Koha] Report Help

[Arslan Farooq]

Hi,

Did you have a look in "5 Catalog/Bibliographic Reports" here?:
http://wiki.koha-community.org/wiki/SQL_Reports_Library

Arslan
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[manoj382093]

Dear Arslan,

I could not find it there

*Regards!*

*Manoj Kumar Misra*
M.Phil., M.L.I.Sc., B.L.I.Sc., M.Sc.(Phy), B.Sc. (P.C.M.)
Deputy Librarian
SRMGPC
Lucknow
Mob. No.: 09415422579
Alternate E-mail: manojmisrasrm...@gmail.com


On Sat, Apr 5, 2014 at 12:39 AM, arslan [via Koha] <
ml-node+s1045719n5793162...@n5.nabble.com> wrote:

> Hi,
>
> Did you have a look in "5 Catalog/Bibliographic Reports" here?:
> http://wiki.koha-community.org/wiki/SQL_Reports_Library
>
> Arslan
> ___
> Koha mailing list  http://koha-community.org
> [hidden email] 
> http://lists.katipo.co.nz/mailman/listinfo/koha
>
>
> --
>  If you reply to this email, your message will be added to the discussion
> below:
> http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793162.html
>  To unsubscribe from Report Help, click 
> here
> .
> NAML
>




--
View this message in context: 
http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793169.html
Sent from the Koha-general mailing list archive at Nabble.com.
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Liz Rea]

Give this a try:

SELECT b.title, count(i.itemnumber) AS "number of items", i.itype,
i.ccode FROM biblio b JOIN items i USING(biblionumber) GROUP BY
b.biblionumber

If it works you can add it to the report library. ;)

Cheers,
Liz Rea
Catalyst IT

On 05/04/14 21:31, manoj382093 wrote:
> Dear Friends,
>  I am looking for a report which has list of titles(biblio) their number of
> copies(items) by item type and collection code.
>
>
>
> --
> View this message in context: 
> http://koha.1045719.n5.nabble.com/Report-Help-tp5793161.html
> Sent from the Koha-general mailing list archive at Nabble.com.
> ___
> 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

Re: [Koha] Report Help

[manoj382093]

Thanks Liz, it is working but i want list with specific item type and
collection code. As the bib record may be attached to more than 1 item type
so it should be distinct.

*Regards!*

*Manoj Kumar Misra*
M.Phil., M.L.I.Sc., B.L.I.Sc., M.Sc.(Phy), B.Sc. (P.C.M.)
Deputy Librarian
SRMGPC
Lucknow
Mob. No.: 09415422579
Alternate E-mail: manojmisrasrm...@gmail.com


On Sun, Apr 6, 2014 at 5:09 PM, Liz Rea-3 [via Koha] <
ml-node+s1045719n579322...@n5.nabble.com> wrote:

> Give this a try:
>
> SELECT b.title, count(i.itemnumber) AS "number of items", i.itype,
> i.ccode FROM biblio b JOIN items i USING(biblionumber) GROUP BY
> b.biblionumber
>
> If it works you can add it to the report library. ;)
>
> Cheers,
> Liz Rea
> Catalyst IT
>
> On 05/04/14 21:31, manoj382093 wrote:
>
> > Dear Friends,
> >  I am looking for a report which has list of titles(biblio) their number
> of
> > copies(items) by item type and collection code.
> >
> >
> >
> > --
> > View this message in context:
> http://koha.1045719.n5.nabble.com/Report-Help-tp5793161.html
> > Sent from the Koha-general mailing list archive at Nabble.com.
> > ___
> > Koha mailing list  http://koha-community.org
> > [hidden email] 
> > http://lists.katipo.co.nz/mailman/listinfo/koha
>
>
>
> ___
> Koha mailing list  http://koha-community.org
> [hidden email] 
> http://lists.katipo.co.nz/mailman/listinfo/koha
>
>
> --
>  If you reply to this email, your message will be added to the discussion
> below:
> http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793227.html
>  To unsubscribe from Report Help, click 
> here
> .
> NAML
>




--
View this message in context: 
http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793244.html
Sent from the Koha-general mailing list archive at Nabble.com.
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[manoj382093]

Dear Chitralekha,
Take this example:
I have one book with 12 copies, 8copies with item type BTECHBKS and 4
copies with item type BCABKS. When i generate report it shows 12 copies for
itemtype BTECHBKS and none for BCABKS.

Is there any way of reporting so that i can get the correct report or i
would have to remove 4 copies and save it as different bib record.

*Regards!*

*Manoj Kumar Misra*
M.Phil., M.L.I.Sc., B.L.I.Sc., M.Sc.(Phy), B.Sc. (P.C.M.)
Deputy Librarian
SRMGPC
Lucknow
Mob. No.: 09415422579
Alternate E-mail: manojmisrasrm...@gmail.com


On Thu, Apr 10, 2014 at 2:06 AM, Chitralekha [via Koha] <
ml-node+s1045719n5793620...@n5.nabble.com> wrote:

> Please try this SQL report. This is sorted by count.
>
>
> SELECT CONCAT(' href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'')
>
> AS biblionumbers,
>biblio.author, biblio.title,
> COUNT(items.itemnumber) AS ItemCount, ccode, itype
> FROM items
> LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
> GROUP BY items.biblionumber HAVING (COUNT(*)>=1)
> ORDER BY ItemCount DESC
>
>
> On Sat, Apr 5, 2014 at 2:01 PM, manoj382093 [via Koha] <[hidden 
> email]
> > wrote:
>
>> Dear Friends,
>>  I am looking for a report which has list of titles(biblio) their number
>> of copies(items) by item type and collection code.
>>
>> --
>>  If you reply to this email, your message will be added to the
>> discussion below:
>> http://koha.1045719.n5.nabble.com/Report-Help-tp5793161.html
>>  To unsubscribe from Koha-general, click here.
>> NAML
>>
>
>
>
> --
>  If you reply to this email, your message will be added to the discussion
> below:
> http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793620.html
>  To unsubscribe from Report Help, click 
> here
> .
> NAML
>




--
View this message in context: 
http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793704.html
Sent from the Koha-general mailing list archive at Nabble.com.
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Katelyn Browne]

Liz's report should do what you want if you change the GROUP BY to say
"GROUP BY biblionumber, i.itype." (That will list, say, the 14 copies of
Book A that are type x, then the 3 copies that are type y. If you'd rather
have all the type x's list together, then all the type y's, just change the
order: GROUP BY i.itype, biblionumber).

--Katelyn.

Katelyn Browne
Middle/High School Librarian
Capital City Public Charter School
100 Peabody Street NW
Washington, DC 20011
(202) 387-0309 x1745
kbro...@ccpcs.org
http://www.ccpcs.org/library/


On Fri, Apr 11, 2014 at 3:47 AM, manoj382093 wrote:

> Dear Chitralekha,
> Take this example:
> I have one book with 12 copies, 8copies with item type BTECHBKS and 4
> copies with item type BCABKS. When i generate report it shows 12 copies for
> itemtype BTECHBKS and none for BCABKS.
>
> Is there any way of reporting so that i can get the correct report or i
> would have to remove 4 copies and save it as different bib record.
>
> *Regards!*
>
> *Manoj Kumar Misra*
> M.Phil., M.L.I.Sc., B.L.I.Sc., M.Sc.(Phy), B.Sc. (P.C.M.)
> Deputy Librarian
> SRMGPC
> Lucknow
> Mob. No.: 09415422579
> Alternate E-mail: manojmisrasrm...@gmail.com
>
>
> On Thu, Apr 10, 2014 at 2:06 AM, Chitralekha [via Koha] <
> ml-node+s1045719n5793620...@n5.nabble.com> wrote:
>
> > Please try this SQL report. This is sorted by count.
> >
> >
> > SELECT CONCAT(' ',biblio.biblionumber,'\">',biblio.biblionumber,'')
> >
> > AS biblionumbers,
> >biblio.author, biblio.title,
> > COUNT(items.itemnumber) AS ItemCount, ccode, itype
> > FROM items
> > LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
> > GROUP BY items.biblionumber HAVING (COUNT(*)>=1)
> > ORDER BY ItemCount DESC
> >
> >
> > On Sat, Apr 5, 2014 at 2:01 PM, manoj382093 [via Koha] <[hidden email]<
> http://user/SendEmail.jtp?type=node&node=5793620&i=0>
> > > wrote:
> >
> >> Dear Friends,
> >>  I am looking for a report which has list of titles(biblio) their number
> >> of copies(items) by item type and collection code.
> >>
> >> --
> >>  If you reply to this email, your message will be added to the
> >> discussion below:
> >> http://koha.1045719.n5.nabble.com/Report-Help-tp5793161.html
> >>  To unsubscribe from Koha-general, click here.
> >> NAML<
> http://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml
> >
> >>
> >
> >
> >
> > --
> >  If you reply to this email, your message will be added to the discussion
> > below:
> > http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793620.html
> >  To unsubscribe from Report Help, click here<
> http://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5793161&code=bWFub2prdW1hcm1pc3JhQGdtYWlsLmNvbXw1NzkzMTYxfC0xMDkzNzg1MDg0
> >
> > .
> > NAML<
> http://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml
> >
> >
>
>
>
>
> --
> View this message in context:
> http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793704.html
> Sent from the Koha-general mailing list archive at Nabble.com.
> ___
> 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

Re: [Koha] Report Help

[manoj382093]

Thank you very much Katelyn, it worked.

*Regards!*

*Manoj Kumar Misra*
M.Phil., M.L.I.Sc., B.L.I.Sc., M.Sc.(Phy), B.Sc. (P.C.M.)
Deputy Librarian
SRMGPC
Lucknow
Mob. No.: 09415422579
Alternate E-mail: manojmisrasrm...@gmail.com


On Fri, Apr 11, 2014 at 4:48 AM, Katelyn Browne [via Koha] <
ml-node+s1045719n5793778...@n5.nabble.com> wrote:

> Liz's report should do what you want if you change the GROUP BY to say
> "GROUP BY biblionumber, i.itype." (That will list, say, the 14 copies of
> Book A that are type x, then the 3 copies that are type y. If you'd rather
> have all the type x's list together, then all the type y's, just change
> the
> order: GROUP BY i.itype, biblionumber).
>
> --Katelyn.
>
> Katelyn Browne
> Middle/High School Librarian
> Capital City Public Charter School
> 100 Peabody Street NW
> Washington, DC 20011
> (202) 387-0309 x1745
> [hidden email] 
> http://www.ccpcs.org/library/
>
>
> On Fri, Apr 11, 2014 at 3:47 AM, manoj382093 <[hidden 
> email]>wrote:
>
>
> > Dear Chitralekha,
> > Take this example:
> > I have one book with 12 copies, 8copies with item type BTECHBKS and 4
> > copies with item type BCABKS. When i generate report it shows 12 copies
> for
> > itemtype BTECHBKS and none for BCABKS.
> >
> > Is there any way of reporting so that i can get the correct report or i
> > would have to remove 4 copies and save it as different bib record.
> >
> > *Regards!*
> >
> > *Manoj Kumar Misra*
> > M.Phil., M.L.I.Sc., B.L.I.Sc., M.Sc.(Phy), B.Sc. (P.C.M.)
> > Deputy Librarian
> > SRMGPC
> > Lucknow
> > Mob. No.: 09415422579
> > Alternate E-mail: [hidden 
> > email]
> >
> >
> > On Thu, Apr 10, 2014 at 2:06 AM, Chitralekha [via Koha] <
> > [hidden email] >
> wrote:
> >
> > > Please try this SQL report. This is sorted by count.
> > >
> > >
> > > SELECT CONCAT(' detail.pl?biblionumber=
> > ',biblio.biblionumber,'\">',biblio.biblionumber,'')
> > >
> > > AS biblionumbers,
> > >biblio.author, biblio.title,
> > > COUNT(items.itemnumber) AS ItemCount, ccode, itype
> > > FROM items
> > > LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
> > > GROUP BY items.biblionumber HAVING (COUNT(*)>=1)
> > > ORDER BY ItemCount DESC
> > >
> > >
> > > On Sat, Apr 5, 2014 at 2:01 PM, manoj382093 [via Koha] <[hidden
> email]<
> > http://user/SendEmail.jtp?type=node&node=5793620&i=0>
> > > > wrote:
> > >
> > >> Dear Friends,
> > >>  I am looking for a report which has list of titles(biblio) their
> number
> > >> of copies(items) by item type and collection code.
> > >>
> > >> --
> > >>  If you reply to this email, your message will be added to the
> > >> discussion below:
> > >> http://koha.1045719.n5.nabble.com/Report-Help-tp5793161.html
> > >>  To unsubscribe from Koha-general, click here.
> > >> NAML<
> >
> http://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml
> > >
> > >>
> > >
> > >
> > >
> > > --
> > >  If you reply to this email, your message will be added to the
> discussion
> > > below:
> > > http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793620.html
> > >  To unsubscribe from Report Help, click here<
> >
> >
> > > .
> > > NAML<
> >
> http://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml
> > >
> > >
> >
> >
> >
> >
> > --
> > View this message in context:
> > http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793704.html
> > Sent from the Koha-general mailing list archive at Nabble.com.
> > ___
> > Koha mailing list  http://koha-community.org
> > [hidden email] 
> > http://lists.katipo.co.nz/mailman/listinfo/koha
> >
> ___
> Koha mailing list  http://koha-community.org
> [hidden email] 
> http://lists.katipo.co.nz/mailman/listinfo/koha
>
>
> --
>  If you reply to this email, your message will be added to the discussion
> below:
> http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793778.html
>  To unsubscribe from Report Help, click 
> here

Re: [Koha] Report Help

[Chitralekha]

Please try this SQL report. This is sorted by count.


SELECT CONCAT('',biblio.biblionumber,'')

AS biblionumbers,
   biblio.author, biblio.title,
COUNT(items.itemnumber) AS ItemCount, ccode, itype
FROM items
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
GROUP BY items.biblionumber HAVING (COUNT(*)>=1)
ORDER BY ItemCount DESC


On Sat, Apr 5, 2014 at 2:01 PM, manoj382093 [via Koha] <
ml-node+s1045719n5793161...@n5.nabble.com> wrote:

> Dear Friends,
>  I am looking for a report which has list of titles(biblio) their number
> of copies(items) by item type and collection code.
>
> --
>  If you reply to this email, your message will be added to the discussion
> below:
> http://koha.1045719.n5.nabble.com/Report-Help-tp5793161.html
>  To unsubscribe from Koha-general, click 
> here
> .
> NAML
>




--
View this message in context: 
http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793620.html
Sent from the Koha-general mailing list archive at Nabble.com.
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Chitralekha]

Manojji,


Your one book has one biblio record say biblionumber 1234.
For this record in 942c tag (Koha [default] item type) you have entered /
selected item type as BTECHBKS.
If all 12 items are attached to this biblionumber the report will show 12
copies to BTECHBKS.

Changing 952y item type for 4 attached items to BCABKS is not getting
reflected in this report.

I am sorry even I am not sure how to make entry of one biblio-record having
different itemtypes.

I will explore and let you know as soon as I get solution.

Thanks and regards.

Chitralekha Mahesh Chitale
Librarian,
Lokmanya Tilak College of Engineering, Navi Mumbai.





On Fri, Apr 11, 2014 at 1:17 PM, manoj382093 [via Koha] <
ml-node+s1045719n5793704...@n5.nabble.com> wrote:

> Dear Chitralekha,
> Take this example:
> I have one book with 12 copies, 8copies with item type BTECHBKS and 4
> copies with item type BCABKS. When i generate report it shows 12 copies for
> itemtype BTECHBKS and none for BCABKS.
>
> Is there any way of reporting so that i can get the correct report or i
> would have to remove 4 copies and save it as different bib record.
>
> *Regards!*
>
> *Manoj Kumar Misra*
> M.Phil., M.L.I.Sc., B.L.I.Sc., M.Sc.(Phy), B.Sc. (P.C.M.)
> Deputy Librarian
> SRMGPC
> Lucknow
> Mob. No.: 09415422579
> Alternate E-mail: [hidden 
> email]
>
>
>




--
View this message in context: 
http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5794300.html
Sent from the Koha-general mailing list archive at Nabble.com.
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Robin Sheat]

Elaine Bradtke schreef op di 22-07-2014 om 17:35 [+0100]:
> One of my colleagues was asking if I could give statistics on what we
> have
> from the 17th, 18th and first half of the 19th century.  Preferably by
> year.
> 
When my Elasticsearch work is finished, this'll be just a matter of
finding an elasticsearch visualisation tool that lets you explore your
data, and telling it to do what you want.

The way I'd do it currently would be to script something that pulls the
date from the MARC of each entry and exports that. Then you could pop it
into libreoffice calc and make graphs or whatever you need.

-- 
Robin Sheat
Catalyst IT Ltd.
✆ +64 4 803 2204
GPG: 5FA7 4B49 1E4D CAA4 4C38  8505 77F5 B724 F871 3BDF

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Elaine Bradtke]

This is the basic report:
SELECT count(biblio.copyrightdate), biblio.copyrightdate,items.itype FROM
items LEFT JOIN biblioitems on
(items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on
(biblioitems.biblionumber=biblio.biblionumber) GROUP BY
biblio.copyrightdate ORDER BY biblio.copyrightdate asc

I also used an expanded version with more fields so I could see what the
numbers really mean.


   - It doesn't pick up everything.  A lot of our 18th and 19th century
   publications have partial or conjectural dates in square brackets. These
   seem to be ignored.
   - It gives a count of the items.  Not the biblios.  So this skews the
   count further (we have a lot of 18th century pamphlets bound together -
   many biblios, but only one item record for the bound volume).

Is there any way we can be more precise?
Would a query on the MARCXML for the  260 c produce a more accurate result?
Can we to force it to count biblios instead of items?
It may be we have to settle for doing the counting in the spreadsheet
rather than via Koha. But it would be nice to have some control over what
is counted.

I know next to nothing about SQL, any help would be greatly appreciated.

Thanks



On Wed, Jul 23, 2014 at 12:31 AM, Robin Sheat  wrote:

> Elaine Bradtke schreef op di 22-07-2014 om 17:35 [+0100]:
> > One of my colleagues was asking if I could give statistics on what we
> > have
> > from the 17th, 18th and first half of the 19th century.  Preferably by
> > year.
> >
> When my Elasticsearch work is finished, this'll be just a matter of
> finding an elasticsearch visualisation tool that lets you explore your
> data, and telling it to do what you want.
>
> The way I'd do it currently would be to script something that pulls the
> date from the MARC of each entry and exports that. Then you could pop it
> into libreoffice calc and make graphs or whatever you need.
>
> --
> Robin Sheat
> Catalyst IT Ltd.
> ✆ +64 4 803 2204
> GPG: 5FA7 4B49 1E4D CAA4 4C38  8505 77F5 B724 F871 3BDF
>
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>



-- 
Elaine Bradtke
Data Wrangler
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999
---
"Writing about music is like dancing about architecture"
--Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[ramirouvia .]

Elaine:

I think you should use 008 date:

SELECT
CASE SUBSTR(ExtractValue(m.marcxml,'//controlfield[@tag="008"]'),8,2)
  WHEN '19' THEN 'XX'
  WHEN '20' THEN 'XXI'
  WHEN '18' THEN 'XVIII'
  WHEN '17' THEN 'XVII'
  ElSE 'OLDER' END
   AS bibtype, count(DISTINCT biblionumber) AS bibs FROM biblioitems m
GROUP BY bibtype

I made this report based on two reports from the library by Nicole C.
Engard.

Ramiro



2014-07-23 15:57 GMT-03:00 Elaine Bradtke :

> This is the basic report:
> SELECT count(biblio.copyrightdate), biblio.copyrightdate,items.itype FROM
> items LEFT JOIN biblioitems on
> (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on
> (biblioitems.biblionumber=biblio.biblionumber) GROUP BY
> biblio.copyrightdate ORDER BY biblio.copyrightdate asc
>
> I also used an expanded version with more fields so I could see what the
> numbers really mean.
>
>
>- It doesn't pick up everything.  A lot of our 18th and 19th century
>publications have partial or conjectural dates in square brackets. These
>seem to be ignored.
>- It gives a count of the items.  Not the biblios.  So this skews the
>count further (we have a lot of 18th century pamphlets bound together -
>many biblios, but only one item record for the bound volume).
>
> Is there any way we can be more precise?
> Would a query on the MARCXML for the  260 c produce a more accurate result?
> Can we to force it to count biblios instead of items?
> It may be we have to settle for doing the counting in the spreadsheet
> rather than via Koha. But it would be nice to have some control over what
> is counted.
>
> I know next to nothing about SQL, any help would be greatly appreciated.
>
> Thanks
>
>
>
> On Wed, Jul 23, 2014 at 12:31 AM, Robin Sheat 
> wrote:
>
> > Elaine Bradtke schreef op di 22-07-2014 om 17:35 [+0100]:
> > > One of my colleagues was asking if I could give statistics on what we
> > > have
> > > from the 17th, 18th and first half of the 19th century.  Preferably by
> > > year.
> > >
> > When my Elasticsearch work is finished, this'll be just a matter of
> > finding an elasticsearch visualisation tool that lets you explore your
> > data, and telling it to do what you want.
> >
> > The way I'd do it currently would be to script something that pulls the
> > date from the MARC of each entry and exports that. Then you could pop it
> > into libreoffice calc and make graphs or whatever you need.
> >
> > --
> > Robin Sheat
> > Catalyst IT Ltd.
> > ✆ +64 4 803 2204
> > GPG: 5FA7 4B49 1E4D CAA4 4C38  8505 77F5 B724 F871 3BDF
> >
> > ___
> > Koha mailing list  http://koha-community.org
> > Koha@lists.katipo.co.nz
> > http://lists.katipo.co.nz/mailman/listinfo/koha
> >
>
>
>
> --
> Elaine Bradtke
> Data Wrangler
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
> ---
> "Writing about music is like dancing about architecture"
> --Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
> ___
> 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

Re: [Koha] Report help

[Elaine Bradtke]

Ramiro
That's very useful, thanks. I've expanded it a little to accommodate our
date range (yes we have some old items) and changed it to say "Unknown"
rather than older, because I suspect these are questionable dates or
missing data.  Quite a few dates are missing in the 008 field. One of the
difficulties in producing reliable statistics!

SELECT
CASE SUBSTR(ExtractValue(m.marcxml,'//controlfield[@tag="008"]'),8,2)
  WHEN '19' THEN 'XX'
  WHEN '20' THEN 'XXI'
  WHEN '18' THEN 'XIX'
  WHEN '17' THEN 'XVIII'
  WHEN '16' THEN 'XVII'
  WHEN '15' THEN 'XVI'
  ElSE 'Unknown' END
   AS bibtype, count(DISTINCT biblionumber) AS bibs FROM biblioitems m
GROUP BY bibtype


On Wed, Jul 23, 2014 at 8:35 PM, ramirouvia .  wrote:

> Elaine:
>
> I think you should use 008 date:
>
> SELECT
> CASE SUBSTR(ExtractValue(m.marcxml,'//controlfield[@tag="008"]'),8,2)
>   WHEN '19' THEN 'XX'
>   WHEN '20' THEN 'XXI'
>   WHEN '18' THEN 'XVIII'
>   WHEN '17' THEN 'XVII'
>   ElSE 'OLDER' END
>AS bibtype, count(DISTINCT biblionumber) AS bibs FROM biblioitems m
> GROUP BY bibtype
>
> I made this report based on two reports from the library by Nicole C.
> Engard.
>
> Ramiro
>
>
>
> 2014-07-23 15:57 GMT-03:00 Elaine Bradtke :
>
>> This is the basic report:
>> SELECT count(biblio.copyrightdate), biblio.copyrightdate,items.itype FROM
>> items LEFT JOIN biblioitems on
>> (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on
>> (biblioitems.biblionumber=biblio.biblionumber) GROUP BY
>> biblio.copyrightdate ORDER BY biblio.copyrightdate asc
>>
>> I also used an expanded version with more fields so I could see what the
>> numbers really mean.
>>
>>
>>- It doesn't pick up everything.  A lot of our 18th and 19th century
>>
>>publications have partial or conjectural dates in square brackets.
>> These
>>seem to be ignored.
>>- It gives a count of the items.  Not the biblios.  So this skews the
>>
>>count further (we have a lot of 18th century pamphlets bound together -
>>many biblios, but only one item record for the bound volume).
>>
>> Is there any way we can be more precise?
>> Would a query on the MARCXML for the  260 c produce a more accurate
>> result?
>> Can we to force it to count biblios instead of items?
>> It may be we have to settle for doing the counting in the spreadsheet
>> rather than via Koha. But it would be nice to have some control over what
>> is counted.
>>
>> I know next to nothing about SQL, any help would be greatly appreciated.
>>
>> Thanks
>>
>>
>>
>> On Wed, Jul 23, 2014 at 12:31 AM, Robin Sheat 
>> wrote:
>>
>> > Elaine Bradtke schreef op di 22-07-2014 om 17:35 [+0100]:
>> > > One of my colleagues was asking if I could give statistics on what we
>> > > have
>> > > from the 17th, 18th and first half of the 19th century.  Preferably by
>> > > year.
>> > >
>> > When my Elasticsearch work is finished, this'll be just a matter of
>> > finding an elasticsearch visualisation tool that lets you explore your
>> > data, and telling it to do what you want.
>> >
>> > The way I'd do it currently would be to script something that pulls the
>> > date from the MARC of each entry and exports that. Then you could pop it
>> > into libreoffice calc and make graphs or whatever you need.
>> >
>> > --
>> > Robin Sheat
>> > Catalyst IT Ltd.
>> > ✆ +64 4 803 2204
>> > GPG: 5FA7 4B49 1E4D CAA4 4C38  8505 77F5 B724 F871 3BDF
>> >
>> > ___
>> > Koha mailing list  http://koha-community.org
>> > Koha@lists.katipo.co.nz
>> > http://lists.katipo.co.nz/mailman/listinfo/koha
>> >
>>
>>
>>
>> --
>> Elaine Bradtke
>> Data Wrangler
>> VWML
>> English Folk Dance and Song Society | http://www.efdss.org
>> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
>> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance
>> and
>> Song Society in London, England. If you wish to phone me personally, send
>> an e-mail first. I work off site)
>> --
>> Registered Company No. 297142
>> Charity Registered in England and Wales No. 305999
>>
>> ---
>> "Writing about music is like dancing about architecture"
>> --Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
>> ___
>> Koha mailing list  http://koha-community.org
>> Koha@lists.katipo.co.nz
>> http://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
>


-- 
Elaine Bradtke
Data Wrangler
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Com

Re: [Koha] Report help

[BWS Johnson]

Salvete!

>SELECT
>CASE SUBSTR(ExtractValue(m.marcxml,'//controlfield[@tag="008"]'),8,2)
>      WHEN '19' THEN 'XX'
>      WHEN '20' THEN 'XXI'
>      WHEN '18' THEN 'XVIII'
>      WHEN '17' THEN 'XVII'
>      ElSE 'OLDER' END
>   AS bibtype, count(DISTINCT biblionumber) AS bibs FROM biblioitems m
>GROUP BY bibtype
>
>I made this report based on two reports from the library by Nicole C.
>Engard.
>



    Should prolly be

>  WHEN '19' THEN 'X1X'
>  WHEN '20' THEN 'XX'
>  WHEN '18' THEN 'XVIII'
>  WHEN '17' THEN 'XVII'



    yeah?

Cheers,
Brooke

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Elaine Bradtke]

 I rewrote the report to make it user friendly to my colleagues who aren't
familiar with Roman numerals. . . little tweaks like this I can do. It's
the syntax and commands that I don't know how to use properly that's the
real problem.

SELECT
CASE SUBSTR(ExtractValue(m.marcxml,'//controlfield[@tag="008"]'),8,2)
WHEN '15' THEN '16th'
WHEN '16' THEN '17th'
WHEN '17' THEN '18th'
WHEN '18' THEN '19th'
WHEN '19' THEN '20th'
WHEN '20' THEN '21st'
  ElSE 'Unknown' END
   AS Century, count(DISTINCT biblionumber) AS bibs FROM biblioitems m
GROUP BY century


On Tue, Jul 29, 2014 at 12:08 PM, BWS Johnson 
wrote:

> Salve!
>
> Way late response since I can't check my personal email at work.
>
>
> >Ahrgh
> >not enough sleep last night
> >1900s=20th century. .
> >
>
> Even if it's by Century, the initial ones had
>
>
>   WHEN '18' THEN 'XVIII'
>   WHEN '17' THEN 'XVII'
>
> So either way, it's flubbed, since that ought be XIX and XVIII
> respectively.
>
>
> Cheers,
> Brooke
>
>


-- 
Elaine Bradtke
Data Wrangler
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999
---
"Writing about music is like dancing about architecture"
--Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Heather Braum (NEKLS)]

Kerrie,

You need to add the parameter of b.branchcode=<> in
the WHERE part of your SQL statement, like I did below. The {choose branch}
part is actually arbitrary and can be whatever you want. Try this:

SELECT concat(b.surname,', ',b.firstname) AS name, count(s.borrowernumber)
AS checkouts
FROM statistics s
LEFT JOIN borrowers b
USING (borrowernumber)
WHERE b.branchcode=<> AND s.datetime BETWEEN <> AND <>
GROUP BY s.borrowernumber
ORDER BY count(s.borrowernumber) DESC
LIMIT 20

Hope it helps and works!

Heather Braum
NExpress Coordinator
Resource Sharing Librarian
Northeast Kansas Library System
hbr...@nekls.org

"The illiterate of the 21st century will not be those who cannot read
and write, but those who cannot learn, unlearn, and relearn." ~Alvin
Toffler, *Rethinking the Future*




On Mon, Oct 13, 2014 at 11:29 PM, Kerrie Stevens 
wrote:

> Hi Everyone,
> I've been using the report 'Patrons with most checkouts in date range'
> with success, but when I try to modify it to allow me to select which
> campus library the patrons are from so I can get a top 20 list for any of
> our campus libraries, I can't get it to work - can anyone help me with this?
> SELECT concat(b.surname,', ',b.firstname) AS name, count(s.borrowernumber)
> AS checkouts
> FROM statistics s
> LEFT JOIN borrowers b
> USING (borrowernumber)
> WHERE s.datetime BETWEEN <> AND
> <>
> GROUP BY s.borrowernumber
> ORDER BY count(s.borrowernumber) DESC
> LIMIT 20
> Thank you!
> Kerrie Stevens
> COLLEGE LIBRARIAN
> Harvest Bible College
> ___
> 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

Re: [Koha] Report help

[Steven Nickerson]

Hi Kerrie,
I think adding "AND s.branch =<>" will do what you're
looking for.  So the whole SQL statement would be:
SELECT concat(b.surname,', ',b.firstname) AS name, count(s.borrowernumber)
AS checkouts FROM statistics s LEFT JOIN borrowers b USING (borrowernumber)
WHERE s.datetime BETWEEN <> AND
<> AND s.branch =<> GROUP BY
s.borrowernumber ORDER BY count(s.borrowernumber) DESC LIMIT 20

That is, assuming each campus library is defined as a separate "branch" in
Koha, of course.   Hope this helps!
Steve

-Original Message-
From: Kerrie Stevens [mailto:kstev...@harvest.edu.au] 
Sent: Tuesday, October 14, 2014 12:29 AM
To: koha@lists.katipo.co.nz
Subject: [Koha] Report help

Hi Everyone,
I've been using the report 'Patrons with most checkouts in date range' with
success, but when I try to modify it to allow me to select which campus
library the patrons are from so I can get a top 20 list for any of our
campus libraries, I can't get it to work - can anyone help me with this?
SELECT concat(b.surname,', ',b.firstname) AS name, count(s.borrowernumber)
AS checkouts FROM statistics s LEFT JOIN borrowers b USING (borrowernumber)
WHERE s.datetime BETWEEN <> AND
<> GROUP BY s.borrowernumber ORDER BY
count(s.borrowernumber) DESC LIMIT 20 Thank you!
Kerrie Stevens
COLLEGE LIBRARIAN
Harvest Bible College


___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Nick Clemens]

I think the problem is that return from ExtractValue returns only one item
so the GROUP_CONCAT is only seeing one thing come in and doesn't add the
separator:

See this:
http://stackoverflow.com/questions/10808149/mysql-use-extractvaluexml-value-values-to-get-all-multiple-values-split-on

On Tue, Apr 14, 2015 at 11:05 AM, Nicole Engard  wrote:

> Hi all,
>
> I'm working on this report and I want there to be a BR between each
> distinct 650a - but for some reason it's not working. I worked on this
> in channel for a while and you can see the log here:
> http://irc.koha-community.org/koha/2015-04-14#i_1663170
>
> Here's the report:
>
>
> Select b.biblionumber, GROUP_CONCAT(DISTINCT ExtractValue(m.marcxml,
> '//datafield[@tag="650"]/subfield[@code="a"]') SEPARATOR ' BR ') AS
> 'Subject',i.itype AS 'IType'
> FROM biblio b
> LEFT JOIN biblioitems m using (biblionumber)
> left join items i using (biblioitemnumber)
> WHERE i.location in
>
> ('ARCHIVES','ARCHSIZE','HISTORYMED','HISTMEDOVZ','HISTMEDREF','RAREBKROOM','RAREOVRSIZ')
> and i.itype != 'JOURNAL' and ExtractValue(m.marcxml,
> '//datafield[@tag="650"]/subfield[@code="a"]') is not null and
> ExtractValue(m.marcxml, '//datafield[@tag="650"]/subfield[@code="a"]')
> != ''
> group by i.biblionumber
> ORDER BY b.biblionumber
>
>
>
> Thanks in advance!
> Nicole
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>



-- 
Nick Clemens
Quechee & Wilder Libraries
n...@quecheelibrary.org
http://www.QuecheeLibrary.org
Q (802) 295-1232 W (802) 295-6341
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Nick Clemens]

You can also use the position marker in the xpath to pick an arbitrary
number of subject headings:

GROUP_CONCAT(DISTINCT
ExtractValue(m.marcxml,'//datafield[@tag="650"][position()=1]/subfield[@code="a"]'),'
BR
',ExtractValue(m.marcxml,'//datafield[@tag="650"][position()=2]/subfield[@code="a"]'),'
BR
',ExtractValue(m.marcxml,'//datafield[@tag="650"][position()=3]/subfield[@code="a"]')
SEPARATOR ' BR ') AS 'Subject'

On Tue, Apr 14, 2015 at 1:17 PM, Nick Clemens 
wrote:

> I think the problem is that return from ExtractValue returns only one item
> so the GROUP_CONCAT is only seeing one thing come in and doesn't add the
> separator:
>
> See this:
>
> http://stackoverflow.com/questions/10808149/mysql-use-extractvaluexml-value-values-to-get-all-multiple-values-split-on
>
> On Tue, Apr 14, 2015 at 11:05 AM, Nicole Engard  wrote:
>
>> Hi all,
>>
>> I'm working on this report and I want there to be a BR between each
>> distinct 650a - but for some reason it's not working. I worked on this
>> in channel for a while and you can see the log here:
>> http://irc.koha-community.org/koha/2015-04-14#i_1663170
>>
>> Here's the report:
>>
>>
>> Select b.biblionumber, GROUP_CONCAT(DISTINCT ExtractValue(m.marcxml,
>> '//datafield[@tag="650"]/subfield[@code="a"]') SEPARATOR ' BR ') AS
>> 'Subject',i.itype AS 'IType'
>> FROM biblio b
>> LEFT JOIN biblioitems m using (biblionumber)
>> left join items i using (biblioitemnumber)
>> WHERE i.location in
>>
>> ('ARCHIVES','ARCHSIZE','HISTORYMED','HISTMEDOVZ','HISTMEDREF','RAREBKROOM','RAREOVRSIZ')
>> and i.itype != 'JOURNAL' and ExtractValue(m.marcxml,
>> '//datafield[@tag="650"]/subfield[@code="a"]') is not null and
>> ExtractValue(m.marcxml, '//datafield[@tag="650"]/subfield[@code="a"]')
>> != ''
>> group by i.biblionumber
>> ORDER BY b.biblionumber
>>
>>
>>
>> Thanks in advance!
>> Nicole
>> ___
>> Koha mailing list  http://koha-community.org
>> Koha@lists.katipo.co.nz
>> http://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
>
>
> --
> Nick Clemens
> Quechee & Wilder Libraries
> n...@quecheelibrary.org
> http://www.QuecheeLibrary.org
> Q (802) 295-1232 W (802) 295-6341
>



-- 
Nick Clemens
Quechee & Wilder Libraries
n...@quecheelibrary.org
http://www.QuecheeLibrary.org
Q (802) 295-1232 W (802) 295-6341
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Nicole Engard]

Don't I have to guess at what the max number of subjects would be
then? and then won't I have a bunch of BRs all over the place if there
is only one subject?

On Tue, Apr 14, 2015 at 2:02 PM, Nick Clemens  wrote:
> You can also use the position marker in the xpath to pick an arbitrary
> number of subject headings:
>
> GROUP_CONCAT(DISTINCT
> ExtractValue(m.marcxml,'//datafield[@tag="650"][position()=1]/subfield[@code="a"]'),'
> BR
> ',ExtractValue(m.marcxml,'//datafield[@tag="650"][position()=2]/subfield[@code="a"]'),'
> BR
> ',ExtractValue(m.marcxml,'//datafield[@tag="650"][position()=3]/subfield[@code="a"]')
> SEPARATOR ' BR ') AS 'Subject'
>
> On Tue, Apr 14, 2015 at 1:17 PM, Nick Clemens 
> wrote:
>
>> I think the problem is that return from ExtractValue returns only one item
>> so the GROUP_CONCAT is only seeing one thing come in and doesn't add the
>> separator:
>>
>> See this:
>>
>> http://stackoverflow.com/questions/10808149/mysql-use-extractvaluexml-value-values-to-get-all-multiple-values-split-on
>>
>> On Tue, Apr 14, 2015 at 11:05 AM, Nicole Engard  wrote:
>>
>>> Hi all,
>>>
>>> I'm working on this report and I want there to be a BR between each
>>> distinct 650a - but for some reason it's not working. I worked on this
>>> in channel for a while and you can see the log here:
>>> http://irc.koha-community.org/koha/2015-04-14#i_1663170
>>>
>>> Here's the report:
>>>
>>>
>>> Select b.biblionumber, GROUP_CONCAT(DISTINCT ExtractValue(m.marcxml,
>>> '//datafield[@tag="650"]/subfield[@code="a"]') SEPARATOR ' BR ') AS
>>> 'Subject',i.itype AS 'IType'
>>> FROM biblio b
>>> LEFT JOIN biblioitems m using (biblionumber)
>>> left join items i using (biblioitemnumber)
>>> WHERE i.location in
>>>
>>> ('ARCHIVES','ARCHSIZE','HISTORYMED','HISTMEDOVZ','HISTMEDREF','RAREBKROOM','RAREOVRSIZ')
>>> and i.itype != 'JOURNAL' and ExtractValue(m.marcxml,
>>> '//datafield[@tag="650"]/subfield[@code="a"]') is not null and
>>> ExtractValue(m.marcxml, '//datafield[@tag="650"]/subfield[@code="a"]')
>>> != ''
>>> group by i.biblionumber
>>> ORDER BY b.biblionumber
>>>
>>>
>>>
>>> Thanks in advance!
>>> Nicole
>>> ___
>>> Koha mailing list  http://koha-community.org
>>> Koha@lists.katipo.co.nz
>>> http://lists.katipo.co.nz/mailman/listinfo/koha
>>>
>>
>>
>>
>> --
>> Nick Clemens
>> Quechee & Wilder Libraries
>> n...@quecheelibrary.org
>> http://www.QuecheeLibrary.org
>> Q (802) 295-1232 W (802) 295-6341
>>
>
>
>
> --
> Nick Clemens
> Quechee & Wilder Libraries
> n...@quecheelibrary.org
> http://www.QuecheeLibrary.org
> Q (802) 295-1232 W (802) 295-6341
> ___
> 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

Re: [Koha] Report help

[Nick Clemens]

Yes to both.  It's not a perfect workaround by far, but thought it was
worth mentioning

You could something like CONCAT_WS(' BR ',IFNULL(ExtractValue(m.marcxml,'//
datafield[@tag="650"][position()=1]/subfield[@code="a"]',''),IFNULL
ExtractValue(m.marcxml,'//datafield[@tag="650"][
position()=2]/subfield[@code="a"]'),'')) to keep out extra BRs

I don't know of a way you can easily get around guessing on the number,
that seems to require creating a function like in the stack overflow link.
If someone does know how I would to know too ;-)

On Tue, Apr 14, 2015 at 4:09 PM, Nicole Engard  wrote:

> Don't I have to guess at what the max number of subjects would be
> then? and then won't I have a bunch of BRs all over the place if there
> is only one subject?
>
> On Tue, Apr 14, 2015 at 2:02 PM, Nick Clemens 
> wrote:
> > You can also use the position marker in the xpath to pick an arbitrary
> > number of subject headings:
> >
> > GROUP_CONCAT(DISTINCT
> >
> ExtractValue(m.marcxml,'//datafield[@tag="650"][position()=1]/subfield[@code="a"]'),'
> > BR
> >
> ',ExtractValue(m.marcxml,'//datafield[@tag="650"][position()=2]/subfield[@code="a"]'),'
> > BR
> >
> ',ExtractValue(m.marcxml,'//datafield[@tag="650"][position()=3]/subfield[@code="a"]')
> > SEPARATOR ' BR ') AS 'Subject'
> >
> > On Tue, Apr 14, 2015 at 1:17 PM, Nick Clemens 
> > wrote:
> >
> >> I think the problem is that return from ExtractValue returns only one
> item
> >> so the GROUP_CONCAT is only seeing one thing come in and doesn't add the
> >> separator:
> >>
> >> See this:
> >>
> >>
> http://stackoverflow.com/questions/10808149/mysql-use-extractvaluexml-value-values-to-get-all-multiple-values-split-on
> >>
> >> On Tue, Apr 14, 2015 at 11:05 AM, Nicole Engard 
> wrote:
> >>
> >>> Hi all,
> >>>
> >>> I'm working on this report and I want there to be a BR between each
> >>> distinct 650a - but for some reason it's not working. I worked on this
> >>> in channel for a while and you can see the log here:
> >>> http://irc.koha-community.org/koha/2015-04-14#i_1663170
> >>>
> >>> Here's the report:
> >>>
> >>>
> >>> Select b.biblionumber, GROUP_CONCAT(DISTINCT ExtractValue(m.marcxml,
> >>> '//datafield[@tag="650"]/subfield[@code="a"]') SEPARATOR ' BR ') AS
> >>> 'Subject',i.itype AS 'IType'
> >>> FROM biblio b
> >>> LEFT JOIN biblioitems m using (biblionumber)
> >>> left join items i using (biblioitemnumber)
> >>> WHERE i.location in
> >>>
> >>>
> ('ARCHIVES','ARCHSIZE','HISTORYMED','HISTMEDOVZ','HISTMEDREF','RAREBKROOM','RAREOVRSIZ')
> >>> and i.itype != 'JOURNAL' and ExtractValue(m.marcxml,
> >>> '//datafield[@tag="650"]/subfield[@code="a"]') is not null and
> >>> ExtractValue(m.marcxml, '//datafield[@tag="650"]/subfield[@code="a"]')
> >>> != ''
> >>> group by i.biblionumber
> >>> ORDER BY b.biblionumber
> >>>
> >>>
> >>>
> >>> Thanks in advance!
> >>> Nicole
> >>> ___
> >>> Koha mailing list  http://koha-community.org
> >>> Koha@lists.katipo.co.nz
> >>> http://lists.katipo.co.nz/mailman/listinfo/koha
> >>>
> >>
> >>
> >>
> >> --
> >> Nick Clemens
> >> Quechee & Wilder Libraries
> >> n...@quecheelibrary.org
> >> http://www.QuecheeLibrary.org
> >> Q (802) 295-1232 W (802) 295-6341
> >>
> >
> >
> >
> > --
> > Nick Clemens
> > Quechee & Wilder Libraries
> > n...@quecheelibrary.org
> > http://www.QuecheeLibrary.org
> > Q (802) 295-1232 W (802) 295-6341
> > ___
> > Koha mailing list  http://koha-community.org
> > Koha@lists.katipo.co.nz
> > http://lists.katipo.co.nz/mailman/listinfo/koha
>



-- 
Nick Clemens
Quechee & Wilder Libraries
n...@quecheelibrary.org
http://www.QuecheeLibrary.org
Q (802) 295-1232 W (802) 295-6341
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Nicole Engard]

Well this is awesome - thanks so much for giving me an option!

On Tue, Apr 14, 2015 at 3:39 PM, Nick Clemens  wrote:
> Yes to both.  It's not a perfect workaround by far, but thought it was worth
> mentioning
>
> You could something like CONCAT_WS(' BR
> ',IFNULL(ExtractValue(m.marcxml,'//datafield[@tag="650"][position()=1]/subfield[@code="a"]',''),IFNULLExtractValue(m.marcxml,'//datafield[@tag="650"][position()=2]/subfield[@code="a"]'),''))
> to keep out extra BRs
>
> I don't know of a way you can easily get around guessing on the number, that
> seems to require creating a function like in the stack overflow link.  If
> someone does know how I would to know too ;-)
>
> On Tue, Apr 14, 2015 at 4:09 PM, Nicole Engard  wrote:
>>
>> Don't I have to guess at what the max number of subjects would be
>> then? and then won't I have a bunch of BRs all over the place if there
>> is only one subject?
>>
>> On Tue, Apr 14, 2015 at 2:02 PM, Nick Clemens 
>> wrote:
>> > You can also use the position marker in the xpath to pick an arbitrary
>> > number of subject headings:
>> >
>> > GROUP_CONCAT(DISTINCT
>> >
>> > ExtractValue(m.marcxml,'//datafield[@tag="650"][position()=1]/subfield[@code="a"]'),'
>> > BR
>> >
>> > ',ExtractValue(m.marcxml,'//datafield[@tag="650"][position()=2]/subfield[@code="a"]'),'
>> > BR
>> >
>> > ',ExtractValue(m.marcxml,'//datafield[@tag="650"][position()=3]/subfield[@code="a"]')
>> > SEPARATOR ' BR ') AS 'Subject'
>> >
>> > On Tue, Apr 14, 2015 at 1:17 PM, Nick Clemens 
>> > wrote:
>> >
>> >> I think the problem is that return from ExtractValue returns only one
>> >> item
>> >> so the GROUP_CONCAT is only seeing one thing come in and doesn't add
>> >> the
>> >> separator:
>> >>
>> >> See this:
>> >>
>> >>
>> >> http://stackoverflow.com/questions/10808149/mysql-use-extractvaluexml-value-values-to-get-all-multiple-values-split-on
>> >>
>> >> On Tue, Apr 14, 2015 at 11:05 AM, Nicole Engard 
>> >> wrote:
>> >>
>> >>> Hi all,
>> >>>
>> >>> I'm working on this report and I want there to be a BR between each
>> >>> distinct 650a - but for some reason it's not working. I worked on this
>> >>> in channel for a while and you can see the log here:
>> >>> http://irc.koha-community.org/koha/2015-04-14#i_1663170
>> >>>
>> >>> Here's the report:
>> >>>
>> >>>
>> >>> Select b.biblionumber, GROUP_CONCAT(DISTINCT ExtractValue(m.marcxml,
>> >>> '//datafield[@tag="650"]/subfield[@code="a"]') SEPARATOR ' BR ') AS
>> >>> 'Subject',i.itype AS 'IType'
>> >>> FROM biblio b
>> >>> LEFT JOIN biblioitems m using (biblionumber)
>> >>> left join items i using (biblioitemnumber)
>> >>> WHERE i.location in
>> >>>
>> >>>
>> >>> ('ARCHIVES','ARCHSIZE','HISTORYMED','HISTMEDOVZ','HISTMEDREF','RAREBKROOM','RAREOVRSIZ')
>> >>> and i.itype != 'JOURNAL' and ExtractValue(m.marcxml,
>> >>> '//datafield[@tag="650"]/subfield[@code="a"]') is not null and
>> >>> ExtractValue(m.marcxml, '//datafield[@tag="650"]/subfield[@code="a"]')
>> >>> != ''
>> >>> group by i.biblionumber
>> >>> ORDER BY b.biblionumber
>> >>>
>> >>>
>> >>>
>> >>> Thanks in advance!
>> >>> Nicole
>> >>> ___
>> >>> Koha mailing list  http://koha-community.org
>> >>> Koha@lists.katipo.co.nz
>> >>> http://lists.katipo.co.nz/mailman/listinfo/koha
>> >>>
>> >>
>> >>
>> >>
>> >> --
>> >> Nick Clemens
>> >> Quechee & Wilder Libraries
>> >> n...@quecheelibrary.org
>> >> http://www.QuecheeLibrary.org
>> >> Q (802) 295-1232 W (802) 295-6341
>> >>
>> >
>> >
>> >
>> > --
>> > Nick Clemens
>> > Quechee & Wilder Libraries
>> > n...@quecheelibrary.org
>> > http://www.QuecheeLibrary.org
>> > Q (802) 295-1232 W (802) 295-6341
>> > ___
>> > Koha mailing list  http://koha-community.org
>> > Koha@lists.katipo.co.nz
>> > http://lists.katipo.co.nz/mailman/listinfo/koha
>
>
>
>
> --
> Nick Clemens
> Quechee & Wilder Libraries
> n...@quecheelibrary.org
> http://www.QuecheeLibrary.org
> Q (802) 295-1232 W (802) 295-6341
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[schnydszch]

Hi Nicole & Nick! I'm particularly interested with this report. What I did
before was this:
ExtractValue (marcxml,'//datafield[@tag="653"]/subfield[@code="a"][1]') as
Keywords, ExtractValue
(marcxml,'//datafield[@tag="653"]/subfield[@code="a"][2]') as Keywords,
ExtractValue 
(marcxml,'//datafield[@tag="653"]/subfield[@code="a"][3]') as Keywords
but the subjects/keywords are in different columns and I have arbitrarily
put in up to 40 columns for this keywords. whatever report we arrive will be
great and could be use by a greater number of Koha users. Cheers  ya'll! :)



--
View this message in context: 
http://koha.1045719.n5.nabble.com/Report-help-tp5835592p5835643.html
Sent from the Koha-general mailing list archive at Nabble.com.
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Church, Beverly]

Hi Nicole,

This query should work:

select title, IF( LOCATE('', biblioitems.marcxml, LOCATE('',
biblioitems.marcxml, LOCATE('
LOCATE('', biblioitems.marcxml, LOCATE('', biblioitems.marcxml, LOCATE('', biblioitems.marcxml,
LOCATE('', biblioitems.marcxml, LOCATE('',
biblioitems.marcxml, LOCATE(' 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

Re: [Koha] Report help

[Nicole Engard]

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  wrote:
> Hi Nicole,
>
> This query should work:
>
> select title, IF( LOCATE(' LOCATE('', biblioitems.marcxml, LOCATE(' tag="245"', biblioitems.marcxml)) = 0 OR LOCATE('',
> biblioitems.marcxml, LOCATE('
> LOCATE('', biblioitems.marcxml, LOCATE(' biblioitems.marcxml)), '', SUBSTRING( biblioitems.marcxml, LOCATE(' code="b">', biblioitems.marcxml, LOCATE(' biblioitems.marcxml)) + 19, LOCATE('', biblioitems.marcxml,
> LOCATE('', biblioitems.marcxml, LOCATE(' tag="245"', biblioitems.marcxml)) + 19) - (LOCATE('',
> biblioitems.marcxml, LOCATE(' 19))) AS 'Sub-title'  FROM biblioitems, biblio where
> biblioitems.biblionumber = biblio.biblionumber and LOCATE(' 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: bchu...@liblime.com
> skype: beverlychurch
>
>
> On Wed, May 25, 2011 at 12:10 PM, Nicole Engard  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

Re: [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  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  wrote:
>> Hi Nicole,
>>
>> This query should work:
>>
>> select title, IF( LOCATE('> LOCATE('', biblioitems.marcxml, LOCATE('> tag="245"', biblioitems.marcxml)) = 0 OR LOCATE('',
>> biblioitems.marcxml, LOCATE('
>> LOCATE('', biblioitems.marcxml, LOCATE('> biblioitems.marcxml)), '', SUBSTRING( biblioitems.marcxml, LOCATE('> code="b">', biblioitems.marcxml, LOCATE('> biblioitems.marcxml)) + 19, LOCATE('', biblioitems.marcxml,
>> LOCATE('', biblioitems.marcxml, LOCATE('> tag="245"', biblioitems.marcxml)) + 19) - (LOCATE('',
>> biblioitems.marcxml, LOCATE('> 19))) AS 'Sub-title'  FROM biblioitems, biblio where
>> biblioitems.biblionumber = biblio.biblionumber and LOCATE('> 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: bchu...@liblime.com
>> skype: beverlychurch
>>
>>
>> On Wed, May 25, 2011 at 12:10 PM, Nicole Engard  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

Re: [Koha] Report help

[Church, Beverly]

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


On Wed, May 25, 2011 at 3:08 PM, Nicole Engard  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_List
>
> Nicole
>
> On Wed, May 25, 2011 at 1:49 PM, Nicole Engard  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 
> wrote:
> >> Hi Nicole,
> >>
> >> This query should work:
> >>
> >> select title, IF( LOCATE(' 0 OR
> >> LOCATE('', biblioitems.marcxml, LOCATE(' >> tag="245"', biblioitems.marcxml)) = 0 OR LOCATE('',
> >> biblioitems.marcxml, LOCATE(' biblioitems.marcxml)) >
> >> LOCATE('', biblioitems.marcxml, LOCATE(' tag="245"',
> >> biblioitems.marcxml)), '', SUBSTRING( biblioitems.marcxml,
> LOCATE(' >> code="b">', biblioitems.marcxml, LOCATE(' >> biblioitems.marcxml)) + 19, LOCATE('', biblioitems.marcxml,
> >> LOCATE('', biblioitems.marcxml, LOCATE(' >> tag="245"', biblioitems.marcxml)) + 19) - (LOCATE('',
> >> biblioitems.marcxml, LOCATE(' biblioitems.marcxml)) +
> >> 19))) AS 'Sub-title'  FROM biblioitems, biblio where
> >> biblioitems.biblionumber = biblio.biblionumber and LOCATE(' >> 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: bchu...@liblime.com
> >> skype: beverlychurch
> >>
> >>
> >> On Wed, May 25, 2011 at 12:10 PM, Nicole Engard 
> 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

Re: [Koha] Report help

[Nicole Engard]

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  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: bchu...@liblime.com
> skype: beverlychurch
>
>
> On Wed, May 25, 2011 at 3:08 PM, Nicole Engard  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_List
>>
>> Nicole
>>
>> On Wed, May 25, 2011 at 1:49 PM, Nicole Engard  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 
>> > wrote:
>> >> Hi Nicole,
>> >>
>> >> This query should work:
>> >>
>> >> select title, IF( LOCATE('> >> 0 OR
>> >> LOCATE('', biblioitems.marcxml, LOCATE('> >> tag="245"', biblioitems.marcxml)) = 0 OR LOCATE('',
>> >> biblioitems.marcxml, LOCATE('> >> biblioitems.marcxml)) >
>> >> LOCATE('', biblioitems.marcxml, LOCATE('> >> tag="245"',
>> >> biblioitems.marcxml)), '', SUBSTRING( biblioitems.marcxml,
>> >> LOCATE('> >> code="b">', biblioitems.marcxml, LOCATE('> >> biblioitems.marcxml)) + 19, LOCATE('', biblioitems.marcxml,
>> >> LOCATE('', biblioitems.marcxml, LOCATE('> >> tag="245"', biblioitems.marcxml)) + 19) - (LOCATE('> >> code="b">',
>> >> biblioitems.marcxml, LOCATE('> >> biblioitems.marcxml)) +
>> >> 19))) AS 'Sub-title'  FROM biblioitems, biblio where
>> >> biblioitems.biblionumber = biblio.biblionumber and LOCATE('> >> 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: bchu...@liblime.com
>> >> skype: beverlychurch
>> >>
>> >>
>> >> On Wed, May 25, 2011 at 12:10 PM, Nicole Engard 
>> >> 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

Re: [Koha] Report help

[Church, Beverly]

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


On Wed, May 25, 2011 at 4:23 PM, Nicole Engard  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 
> 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: bchu...@liblime.com
> > skype: beverlychurch
> >
> >
> > On Wed, May 25, 2011 at 3:08 PM, Nicole Engard 
> 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_List
> >>
> >> Nicole
> >>
> >> On Wed, May 25, 2011 at 1:49 PM, Nicole Engard 
> 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 <
> bchu...@liblime.com>
> >> > wrote:
> >> >> Hi Nicole,
> >> >>
> >> >> This query should work:
> >> >>
> >> >> select title, IF( LOCATE(' =
> >> >> 0 OR
> >> >> LOCATE('', biblioitems.marcxml, LOCATE(' >> >> tag="245"', biblioitems.marcxml)) = 0 OR LOCATE(' code="b">',
> >> >> biblioitems.marcxml, LOCATE(' >> >> biblioitems.marcxml)) >
> >> >> LOCATE('', biblioitems.marcxml, LOCATE(' >> >> tag="245"',
> >> >> biblioitems.marcxml)), '', SUBSTRING( biblioitems.marcxml,
> >> >> LOCATE(' >> >> code="b">', biblioitems.marcxml, LOCATE(' >> >> biblioitems.marcxml)) + 19, LOCATE('',
> biblioitems.marcxml,
> >> >> LOCATE('', biblioitems.marcxml, LOCATE(' >> >> tag="245"', biblioitems.marcxml)) + 19) - (LOCATE(' >> >> code="b">',
> >> >> biblioitems.marcxml, LOCATE(' >> >> biblioitems.marcxml)) +
> >> >> 19))) AS 'Sub-title'  FROM biblioitems, biblio where
> >> >> biblioitems.biblionumber = biblio.biblionumber and LOCATE(' >> >> 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: bchu...@liblime.com
> >> >> skype: beverlychurch
> >> >>
> >> >>
> >> >> On Wed, May 25, 2011 at 12:10 PM, Nicole Engard 
> >> >> 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

Re: [Koha] Report help

[Ian Walls]

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 

> 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: bchu...@liblime.com
> skype: beverlychurch
>
>
> On Wed, May 25, 2011 at 4:23 PM, Nicole Engard  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 
>> 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: bchu...@liblime.com
>> > skype: beverlychurch
>> >
>> >
>> > On Wed, May 25, 2011 at 3:08 PM, Nicole Engard 
>> 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_List
>> >>
>> >> Nicole
>> >>
>> >> On Wed, May 25, 2011 at 1:49 PM, Nicole Engard 
>> 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 <
>> bchu...@liblime.com>
>> >> > wrote:
>> >> >> Hi Nicole,
>> >> >>
>> >> >> This query should work:
>> >> >>
>> >> >> select title, IF( LOCATE('> biblioitems.marcxml) =
>> >> >> 0 OR
>> >> >> LOCATE('', biblioitems.marcxml,
>> LOCATE('> >> >> tag="245"', biblioitems.marcxml)) = 0 OR LOCATE('> code="b">',
>> >> >> biblioitems.marcxml, LOCATE('> >> >> biblioitems.marcxml)) >
>> >> >> LOCATE('', biblioitems.marcxml, LOCATE('> >> >> tag="245"',
>> >> >> biblioitems.marcxml)), '', SUBSTRING( biblioitems.marcxml,
>> >> >> LOCATE('> >> >> code="b">', biblioitems.marcxml, LOCATE('> >> >> biblioitems.marcxml)) + 19, LOCATE('',
>> biblioitems.marcxml,
>> >> >> LOCATE('', biblioitems.marcxml,
>> LOCATE('> >> >> tag="245"', biblioitems.marcxml)) + 19) - (LOCATE('> >> >> code="b">',
>> >> >> biblioitems.marcxml, LOCATE('> >> >> biblioitems.marcxml)) +
>> >> >> 19))) AS 'Sub-title'  FROM biblioitems, biblio where
>> >> >> biblioitems.biblionumber = biblio.biblionumber and
>> LOCATE('> >> >> 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: bchu...@liblime.com
>> >> >> skype: beverlychurch
>> >> >>
>> >> >>
>> >> >> On Wed, May 25, 2011 at 12:10 PM, Nicole Engard 
>> >> >> 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.wa...@bywatersolutions.com
Twitter: @sekjal
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Chris Cormack]

2011/5/26 Church, Beverly :
> Is this run at the MySQL level or in the reports module?
>
>
In reports, (but of course reports is just a frontend to MySQL so if
that is old and doesn't support ExtractValue it won't work

Chris
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Daniel Grobani]

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.
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Chris Nighswonger]

 Hi Nicole,

On Fri, Jul 1, 2011 at 9:33 AM, Nicole Engard  wrote:

> Hi all,
>
> I wrote this report to get all subject headings in a Koha system that
> start with key phrases:
>
> SELECT lcsh
> FROM
> (SELECT biblionumber,
> ExtractValue(marcxml,'//datafield[@tag="610"]/subfield[@code>="a"]')
> AS lcsh FROM biblioitems)
> AS subjects
> WHERE lcsh
> LIKE "College%"
> OR lcsh LIKE "Seminary%"
> GROUP BY lcsh
>
>
> What's happening is if there is more than one 610 on a bib record it's
> showing them both on one line.  I tried using DISTINCT, but that's
> didn't do the trick, so I'm up for ideas.
>

I did some work with ExtractValue and marcxml a while back and IIRC this has
to do with the way ExtractValue returns the data from the xml. I was unable
to find a workaround at the time, but perhaps someone else has found one.

Kind Regards,
Chris
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Ian Walls]

Unfortunately, that's just the way that ExtractValue works.  From the
manual:

If multiple matches are found, the content of the first child text node of
each matching element is returned (in the order matched) as a single,
space-delimited string.

Since MARC doesn't give us any means of differentiating between the multiple
fields aside from their order, there isn't much that can be done here.  And
if the desired 610 field isn't the first in the series of them, the LIKE
clause won't match; you'd need % in front, as well.

MARC for the fail.. :(


-Ian

2011/7/1 Chris Nighswonger 

> Hi Nicole,
>
>
> On Fri, Jul 1, 2011 at 9:33 AM, Nicole Engard  wrote:
>
>> Hi all,
>>
>> I wrote this report to get all subject headings in a Koha system that
>> start with key phrases:
>>
>> SELECT lcsh
>> FROM
>> (SELECT biblionumber,
>> ExtractValue(marcxml,'//datafield[@tag="610"]/subfield[@code>="a"]')
>> AS lcsh FROM biblioitems)
>> AS subjects
>> WHERE lcsh
>> LIKE "College%"
>> OR lcsh LIKE "Seminary%"
>> GROUP BY lcsh
>>
>>
>> What's happening is if there is more than one 610 on a bib record it's
>> showing them both on one line.  I tried using DISTINCT, but that's
>> didn't do the trick, so I'm up for ideas.
>>
>
> I did some work with ExtractValue and marcxml a while back and IIRC this
> has to do with the way ExtractValue returns the data from the xml. I was
> unable to find a workaround at the time, but perhaps someone else has found
> one.
>
> Kind Regards,
> Chris
>
> ___
> 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.wa...@bywatersolutions.com
Twitter: @sekjal
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Chris Nighswonger]

 On Fri, Jul 1, 2011 at 9:52 AM, Ian Walls
wrote:

> Unfortunately, that's just the way that ExtractValue works.  From the
> manual:
>
> If multiple matches are found, the content of the first child text node of
> each matching element is returned (in the order matched) as a single,
> space-delimited string.
>

The sad thing is if it was comma or otherwise delimited we could do some
splitting foo. But spaces will inevitably appear in the date in the
specified fields.

Kind Regards,
Chris
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Chris Nighswonger]

On Fri, Jul 1, 2011 at 10:09 AM, Chris Nighswonger <
cnighswon...@foundations.edu> wrote:

>  On Fri, Jul 1, 2011 at 9:52 AM, Ian Walls  > wrote:
>
>> Unfortunately, that's just the way that ExtractValue works.  From the
>> manual:
>>
>> If multiple matches are found, the content of the first child text node of
>> each matching element is returned (in the order matched) as a single,
>> space-delimited string.
>>
>
> The sad thing is if it was comma or otherwise delimited we could do some
> splitting foo. But spaces will inevitably appear in the date in the
> specified fields.
>

Read 'data' for 'date'
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Nicole Engard]

On Fri, Jul 1, 2011 at 10:09 AM, Chris Nighswonger
 wrote:
> The sad thing is if it was comma or otherwise delimited we could do some
> splitting foo. But spaces will inevitably appear in the date in the
> specified fields.

Chris, in this library's case (cause they do have professional
catalogers) the subject headings all end in periods ... could we use
that instead of the comma/space?

Nicole
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Frédéric Demians]

 > I wrote this report to get all subject headings in a Koha system that
 > start with key phrases:

You can get your headings with a Perl script.

You can also export all your biblio records in ISO2709 and then combine
various standard Linux commands to extract and select your headings.

For example, on Linux Shell:

   mysql my_koha_db_name -e 'SELECT marc FROM biblioitems' | \
   yaz-marcdump | grep ^610 | sort | uniq

After grep ^610, you can add a text filter to extract just $a without
field name and other subfields.

-- 
Frédéric DEMIANS
http://www.tamil.fr/u/fdemians.html

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Chris Nighswonger]

On Fri, Jul 1, 2011 at 10:19 AM, Nicole Engard  wrote:

> On Fri, Jul 1, 2011 at 10:09 AM, Chris Nighswonger
>  wrote:
> > The sad thing is if it was comma or otherwise delimited we could do some
> > splitting foo. But spaces will inevitably appear in the date in the
> > specified fields.
>
> Chris, in this library's case (cause they do have professional
> catalogers) the subject headings all end in periods ... could we use
> that instead of the comma/space?
>
>
Assuming there were no periods embedded anywhere else in the data its
possible. The 'foo' I mentioned amounts to writing a stored procedure in
your db do handle the splitting since MySQL does not have a splitting
function. So it would require some coding apart from the reports tool in
Koha.

Kind Regards,
Chris
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Nicole Engard]

Thanks a bunch! This appears to work.  I'll post this to the wiki in
your name so that you get credit for the work and so that others can
benefit from this.

Nicole

2011/8/17 Msgr. Marion J. Makarewicz :
> SELECT b.title, b.author, b.copyrightdate, i.itemcallnumber,
>
> i.barcode, i.datelastborrowed, i.issues as totalcheckouts,
>
> i.dateaccessioned
>
> FROM items i
>
> LEFT JOIN issues
>
> USING (itemnumber)
>
> LEFT JOIN biblio b
>
> USING (biblionumber)
>
> WHERE i.itemnumber NOT IN (SELECT issues.itemnumber FROM issues) AND
> YEAR(NOW())-YEAR(i.datelastborrowed) > 5
>
> UNION SELECT b.title, b.author, b.copyrightdate, i.itemcallnumber,
>
> i.barcode, i.datelastborrowed, i.issues as totalcheckouts,
>
> i.dateaccessioned
>
> FROM items i
>
> LEFT JOIN issues
>
> USING (itemnumber)
>
> LEFT JOIN biblio b
>
> USING (biblionumber)
>
> WHERE i.itemnumber NOT IN (SELECT old_issues.itemnumber FROM old_issues) AND
> YEAR(NOW())-YEAR(i.datelastborrowed) > 5;
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Chris Cormack]

On 4 April 2012 14:38, Nicole Engard  wrote:
> I think this requires some regex magic :) So, if anyone can help me
> out that would be great:
>
> How do I create a report for all records that have both of the
> following items? A 942|c field that says Photographs and a leader
> fields that shows item type as book?
>
> I know that the 94$c is mapped to biblioitems.itemtype - so that's not
> the issue, it's the Leader bit I need help with.
>
>
What chars in the leader tell me if its a book?

Chris
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Nicole Engard]

>From here: http://manual.koha-community.org/3.6/en/XSLTiTypes.html

leader6 = a (and one of the leader7 values below)

leader7 = a

leader7 = c

leader7 = d

leader7 = m

leader6 = t

On Tue, Apr 3, 2012 at 7:43 PM, Chris Cormack  wrote:
> On 4 April 2012 14:38, Nicole Engard  wrote:
>> I think this requires some regex magic :) So, if anyone can help me
>> out that would be great:
>>
>> How do I create a report for all records that have both of the
>> following items? A 942|c field that says Photographs and a leader
>> fields that shows item type as book?
>>
>> I know that the 94$c is mapped to biblioitems.itemtype - so that's not
>> the issue, it's the Leader bit I need help with.
>>
>>
> What chars in the leader tell me if its a book?
>
> Chris
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Chris Cormack]

On 4 April 2012 15:19, Nicole Engard  wrote:
> From here: http://manual.koha-community.org/3.6/en/XSLTiTypes.html
>
>    leader6 = a (and one of the leader7 values below)
>
>        leader7 = a
>
>        leader7 = c
>
>        leader7 = d
>
>        leader7 = m
>
>    leader6 = t
>

Something like this then

SELECT * FROM biblioitems,
(SELECT biblioitemnumber,SUBSTR(marcxml,INSTR(marcxml,
"")+8+6,1) AS leader6,SUBSTR(marcxml,INSTR(marcxml,
"")+8+7,1) AS leader7 FROM biblioitems) AS leaders
WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND
(leaders.leader6 = 'a' AND (leader7 IN ('a','c','d','n')) OR leader6 =
't') AND itemtypes = 'your_itemtype goes here'

Chris
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Nicole Engard]

Nope :( No results, and we know there are some problem records. I only
made the following changes:

SELECT CONCAT('',biblionumber,'')
AS biblionumber FROM biblioitems,
   (SELECT biblioitemnumber,SUBSTR(marcxml,INSTR(marcxml, "")+8+6,1) AS
leader6,SUBSTR(marcxml,INSTR(marcxml, "")+8+7,1) AS leader7 FROM
biblioitems) AS leaders WHERE
biblioitems.biblioitemnumber=leaders.biblioitemnumber AND (leaders.leader6 =
'a' AND (leader7 IN ('a','c','d','n')) OR leader6 = 't') AND itemtype = 'PHOTO'


On Tue, Apr 3, 2012 at 8:43 PM, Chris Cormack  wrote:
> On 4 April 2012 15:19, Nicole Engard  wrote:
>> From here: http://manual.koha-community.org/3.6/en/XSLTiTypes.html
>>
>>    leader6 = a (and one of the leader7 values below)
>>
>>        leader7 = a
>>
>>        leader7 = c
>>
>>        leader7 = d
>>
>>        leader7 = m
>>
>>    leader6 = t
>>
>
> Something like this then
>
> SELECT * FROM biblioitems,
> (SELECT biblioitemnumber,SUBSTR(marcxml,INSTR(marcxml,
> "")+8+6,1) AS leader6,SUBSTR(marcxml,INSTR(marcxml,
> "")+8+7,1) AS leader7 FROM biblioitems) AS leaders
> WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND
> (leaders.leader6 = 'a' AND (leader7 IN ('a','c','d','n')) OR leader6 =
> 't') AND itemtypes = 'your_itemtype goes here'
>
> Chris
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Nicole Engard]

I found out that we're looking at too much in this.  I removed a bit
and now it all seems to work :)

Nicole

On Wed, Apr 4, 2012 at 9:44 AM, Nicole Engard  wrote:
> Nope :( No results, and we know there are some problem records. I only
> made the following changes:
>
> SELECT CONCAT(' href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',biblionumber,'')
> AS biblionumber FROM biblioitems,
>   (SELECT biblioitemnumber,SUBSTR(marcxml,INSTR(marcxml, "")+8+6,1) AS
> leader6,SUBSTR(marcxml,INSTR(marcxml, "")+8+7,1) AS leader7 FROM
> biblioitems) AS leaders WHERE
> biblioitems.biblioitemnumber=leaders.biblioitemnumber AND (leaders.leader6 =
> 'a' AND (leader7 IN ('a','c','d','n')) OR leader6 = 't') AND itemtype = 
> 'PHOTO'
>
>
> On Tue, Apr 3, 2012 at 8:43 PM, Chris Cormack  
> wrote:
>> On 4 April 2012 15:19, Nicole Engard  wrote:
>>> From here: http://manual.koha-community.org/3.6/en/XSLTiTypes.html
>>>
>>>    leader6 = a (and one of the leader7 values below)
>>>
>>>        leader7 = a
>>>
>>>        leader7 = c
>>>
>>>        leader7 = d
>>>
>>>        leader7 = m
>>>
>>>    leader6 = t
>>>
>>
>> Something like this then
>>
>> SELECT * FROM biblioitems,
>> (SELECT biblioitemnumber,SUBSTR(marcxml,INSTR(marcxml,
>> "")+8+6,1) AS leader6,SUBSTR(marcxml,INSTR(marcxml,
>> "")+8+7,1) AS leader7 FROM biblioitems) AS leaders
>> WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND
>> (leaders.leader6 = 'a' AND (leader7 IN ('a','c','d','n')) OR leader6 =
>> 't') AND itemtypes = 'your_itemtype goes here'
>>
>> Chris
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Barton Chittenden]

Elaine,

This query should do what you're looking for:

select biblionumber, from biblioitems where ExtractValue( marcxml,
'//datafield[@tag=942]/subfield[@code="c"]' ) = '';

Depending on the number of bibs, this could take quite a while to run -- I
would try it after hours.

Alternatively, if you have 942$c mapped in your biblio-marc mappings, you
could try querying the filed that it's mapped to, which would look a bit
like this:

select biblionumber from biblioitems where FOO is NULL;

where FOO is the column in question (FOO could conceivably be in items or
biblio... adjust the query above accordingly). This would run much faster
than the ExtractValue query.

--Barton
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Paul A]

At 09:00 AM 9/28/2015 -0400, Barton Chittenden wrote:

Elaine,

This query should do what you're looking for:

select biblionumber, from biblioitems where ExtractValue( marcxml,
'//datafield[@tag=942]/subfield[@code="c"]' ) = '';

Depending on the number of bibs, this could take quite a while to run -- I
would try it after hours.


Don't use a comma after biblionumber... And it's fairly fast -- I removed a 
942$c to test it, and got:


mysql> SELECT biblionumber FROM biblioitems WHERE 
ExtractValue(marcxml,'//datafield[@tag=942]/subfield[@code="c"]') = '';

+--+
| biblionumber |
+--+
|42419 |
+--+
1 row in set (0.26 sec)

Best -- Paul




Alternatively, if you have 942$c mapped in your biblio-marc mappings, you
could try querying the filed that it's mapped to, which would look a bit
like this:

select biblionumber from biblioitems where FOO is NULL;

where FOO is the column in question (FOO could conceivably be in items or
biblio... adjust the query above accordingly). This would run much faster
than the ExtractValue query.

--Barton
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha


---
Maritime heritage and history, preservation and conservation,
research and education through the written word and the arts.
 and 

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Paul Hoffman]

On Fri, Jul 13, 2018 at 10:06:56AM -0700, Elaine Bradtke wrote:
> This report finds Authors not linked to authorities.  But it only lists
> their names (some of them are quite prolific, and sometimes it's only one
> biblio that needs checking).
> How can I make the following give the biblio number as well as the name?
> 
> SELECT DISTINCT heading FROM (
>  SELECT ExtractValue(metadata,
> '//datafield[@tag="100"]/subfield[@code="a"]') AS heading
>  FROM biblio_metadata
>  WHERE
>   length(ExtractValue(metadata,
> '//datafield[@tag="100"]/subfield[@code="a"]')) != 0 AND
>   length(ExtractValue(metadata,
> '//datafield[@tag="100"]/subfield[@code="9"]')) = 0
> UNION
> [...]

I assume that you can just add biblionumber to the output everywhere 
that a heading appears, something like this (untested!):

SELECT DISTINCT biblionumber, heading FROM (
SELECT biblionumber, ExtractValue(metadata, 
'//datafield[@tag="100"]/subfield[@code="a"]') AS heading
FROM   biblio_metadata
WHERE  length(ExtractValue(metadata, 
'//datafield[@tag="100"]/subfield[@code="a"]')) != 0
ANDlength(ExtractValue(metadata, 
'//datafield[@tag="100"]/subfield[@code="9"]')) = 0
UNION
[...]

Paul.

-- 
Paul Hoffman 
Software Manager
Fenway Libraries Online
c/o Wentworth Institute of Technology
550 Huntington Ave.
Boston, MA 02115
(617) 442-2384 (FLO main number)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Elaine Bradtke]

Yes, that did the trick!  Thanks
This is the revised report:
SELECT DISTINCT biblionumber, heading FROM (
 SELECT biblionumber, ExtractValue(metadata,
'//datafield[@tag="100"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="100"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="100"]/subfield[@code="9"]')) = 0
UNION
 SELECT biblionumber, ExtractValue(metadata,
'//datafield[@tag="110"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="110"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="110"]/subfield[@code="9"]')) = 0
UNION
 SELECT biblionumber, ExtractValue(metadata,
'//datafield[@tag="111"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="111"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="111"]/subfield[@code="9"]')) = 0
UNION
 SELECT biblionumber, ExtractValue(metadata,
'//datafield[@tag="130"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="130"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="130"]/subfield[@code="9"]')) = 0
) AS heads
ORDER BY heading

Elaine Bradtke
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999
---
"Writing about music is like dancing about architecture"
--Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)

On Fri, Jul 13, 2018 at 11:53 AM, Paul Hoffman  wrote:

> On Fri, Jul 13, 2018 at 10:06:56AM -0700, Elaine Bradtke wrote:
> > This report finds Authors not linked to authorities.  But it only lists
> > their names (some of them are quite prolific, and sometimes it's only one
> > biblio that needs checking).
> > How can I make the following give the biblio number as well as the name?
> >
> > SELECT DISTINCT heading FROM (
> >  SELECT ExtractValue(metadata,
> > '//datafield[@tag="100"]/subfield[@code="a"]') AS heading
> >  FROM biblio_metadata
> >  WHERE
> >   length(ExtractValue(metadata,
> > '//datafield[@tag="100"]/subfield[@code="a"]')) != 0 AND
> >   length(ExtractValue(metadata,
> > '//datafield[@tag="100"]/subfield[@code="9"]')) = 0
> > UNION
> > [...]
>
> I assume that you can just add biblionumber to the output everywhere
> that a heading appears, something like this (untested!):
>
> SELECT DISTINCT biblionumber, heading FROM (
> SELECT biblionumber, ExtractValue(metadata, 
> '//datafield[@tag="100"]/subfield[@code="a"]')
> AS heading
> FROM   biblio_metadata
> WHERE  length(ExtractValue(metadata, 
> '//datafield[@tag="100"]/subfield[@code="a"]'))
> != 0
> ANDlength(ExtractValue(metadata, 
> '//datafield[@tag="100"]/subfield[@code="9"]'))
> = 0
> UNION
> [...]
>
> Paul.
>
> --
> Paul Hoffman 
> Software Manager
> Fenway Libraries Online
> c/o Wentworth Institute of Technology
> 550 Huntington Ave.
> Boston, MA 02115
> (617) 442-2384 (FLO main number)
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Jonathan Druart]

Hi Elaine,

The biblioitems.marcxml field has been moved to biblio_metadata.metadata.

Does this query work as you want:
SELECT CONCAT('',b.biblionumber,'')
 AS biblionumber
FROM biblio b
LEFT JOIN biblioitems bi ON b.biblionumber= bi.biblionumber
LEFT JOIN biblio_metadata bm ON b.biblionumber=bm.biblionumber
WHERE bi.itemtype = 'BK' AND SUBSTR(bm.metadata,INSTR(bm.metadata,
"")+8+6,1) = 'a';

Regards,
Jonathan

Le mar. 26 mai 2020 à 20:35, Elaine Bradtke  a écrit :
>
> What I'd like it to do - give a list of biblio numbers for records with a
> particular itemtype, and the information found in the Leader *06 - Type of
> record *field.
> I found the following in the reports library as something that could be
> modified to fit my needs.  But it doesn't work  I get the following
> message: Unknown column 'marcxml' in 'field list'
> Please check the log for further details.
> Also, I don't understand how to indicate Leader position 06
>
>  SELECT CONCAT(' ',biblionumber,'\">',biblionumber,'')
>
> AS biblionumber
>
> FROM biblioitems,
>
> (SELECT biblioitemnumber, SUBSTR(marcxml,INSTR(marcxml, "")+8+6,1)
>
> AS leader6 FROM biblioitems)
>
> AS leaders
>
> WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND
> leaders.leader6 = 'a'
>
> AND itemtype = <>
>
> Any help would be appreciated
>
> stay safe,
>
> Elaine Bradtke
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Elaine Bradtke]

Yes, I think I've got it now.  Thanks for your help.  The hard part was
figuring out where to find the itemtype in the bib record.
SELECT biblio.biblionumber,
SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS "Position06"
FROM biblio
LEFT JOIN biblio_metadata USING (biblionumber)
WHERE ExtractValue( metadata, '//datafield[@tag="942"]/subfield[@code="c"]'
) = <>
Elaine Bradtke
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999


On Wed, May 27, 2020 at 10:50 AM Elaine Bradtke  wrote:

> It doesn't throw up error messages, but what I really need is a list of
> biblio numbers, with the info in position 6 of the leader and the itemtype
> from the bib record, but also choose a specific itemtype (not books).  I'm
> going to try to mash up what you did with another report and see what
> happens.
> Elaine Bradtke
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
>
>
> On Wed, May 27, 2020 at 1:51 AM Jonathan Druart <
> jonathan.dru...@bugs.koha-community.org> wrote:
>
>> Hi Elaine,
>>
>> The biblioitems.marcxml field has been moved to biblio_metadata.metadata.
>>
>> Does this query work as you want:
>> SELECT CONCAT('> href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=
>> ',b.biblionumber,'\">',b.biblionumber,'')
>>  AS biblionumber
>> FROM biblio b
>> LEFT JOIN biblioitems bi ON b.biblionumber= bi.biblionumber
>> LEFT JOIN biblio_metadata bm ON b.biblionumber=bm.biblionumber
>> WHERE bi.itemtype = 'BK' AND SUBSTR(bm.metadata,INSTR(bm.metadata,
>> "")+8+6,1) = 'a';
>>
>> Regards,
>> Jonathan
>>
>> Le mar. 26 mai 2020 à 20:35, Elaine Bradtke  a écrit :
>> >
>> > What I'd like it to do - give a list of biblio numbers for records with
>> a
>> > particular itemtype, and the information found in the Leader *06 - Type
>> of
>> > record *field.
>> > I found the following in the reports library as something that could be
>> > modified to fit my needs.  But it doesn't work  I get the following
>> > message: Unknown column 'marcxml' in 'field list'
>> > Please check the log for further details.
>> > Also, I don't understand how to indicate Leader position 06
>> >
>> >  SELECT CONCAT('> detail.pl?biblionumber=
>> > ',biblionumber,'\">',biblionumber,'')
>> >
>> > AS biblionumber
>> >
>> > FROM biblioitems,
>> >
>> > (SELECT biblioitemnumber, SUBSTR(marcxml,INSTR(marcxml,
>> "")+8+6,1)
>> >
>> > AS leader6 FROM biblioitems)
>> >
>> > AS leaders
>> >
>> > WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND
>> > leaders.leader6 = 'a'
>> >
>> > AND itemtype = <>
>> >
>> > Any help would be appreciated
>> >
>> > stay safe,
>> >
>> > Elaine Bradtke
>> > VWML
>> > English Folk Dance and Song Society | http://www.efdss.org
>> > Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
>> > Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance
>> and
>> > Song Society in London, England. If you wish to phone me personally,
>> send
>> > an e-mail first. I work off site)
>> >
>> --
>> > Registered Company No. 297142
>> > Charity Registered in England and Wales No. 305999
>> > ___
>> >
>> > Koha mailing list  http://koha-community.org
>> > Koha@lists.katipo.co.nz
>> > Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Michael Sutherland]

Elaine,

Maybe this would be helpful for those codes -
https://www.loc.gov/marc/bibliographic/bdleader.html

And, to break it down for those that do not know and are interested, (at
the risk of condescending, which is not my intention, please, please, I
hope you do not take it that way)

in the expression - SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS
"Position06"

you are wanting to extract a piece (substring) from the entire leader
string - metadata,'//leader' - the 7 is the value that specifies the
initial position from which the characters can be extracted. The first
position of the expression starts with 1. But, in MARC the first position
is 0 (zero). The next value is a positive integer value that specifies the
ending limit and determines how many characters are going to be
extracted from the given expression in this case '1', which will give you
the single letter code in the leader 06, or the seventh position, however
you choose to look at it.

So, in collaboration with ByWater Solutions, I have co-written the
following report, which I use to report collection statistics by itemtype
to ACRL and ARL on our collection sans suppressed records from the OPAC. It
takes the combination of the leader 06 and 07 to determine itemtype and
count them.

SELECT
CASE SUBSTR(metadata,282,2)
  WHEN ' m' THEN 'Book'
  WHEN '2m' THEN 'Book'
  WHEN 'am' THEN 'Book'
  WHEN 'aa' THEN 'Book'
  WHEN 'ac' THEN 'Book'
  WHEN 'ad' THEN 'Book'
  WHEN 'ai' THEN 'Book'
  WHEN 'as' THEN 'Journal'
  WHEN 'cc' THEN 'Music Score'
  WHEN 'cm' THEN 'Music Score'
  WHEN 'dm' THEN 'Music Score'
  WHEN 'ei' THEN 'Map'
  WHEN 'em' THEN 'Map'
  WHEN 'es' THEN 'Map'
  WHEN 'ga' THEN 'Video recording and motion pictures'
  WHEN 'gs' THEN 'Video recording and motion pictures'
  WHEN 'gc' THEN 'Video recording and motion pictures'
  WHEN 'gi' THEN 'Video recording and motion pictures'
  WHEN 'gm' THEN 'Video recording and motion pictures'
  WHEN 'ic' THEN 'Non-music sound recording'
  WHEN 'im' THEN 'Non-music sound recording'
  WHEN 'is' THEN 'Non-music sound recording'
  WHEN 'jm' THEN 'Music sound recording'
  WHEN 'jc' THEN 'Music sound recording'
  WHEN 'ji' THEN 'Music sound recording'
  WHEN 'kc' THEN '2D image'
  WHEN 'km' THEN '2D image'
  WHEN 'ma' THEN 'Computer file'
  WHEN 'mi' THEN 'Computer file'
  WHEN 'mm' THEN 'Computer file'
  WHEN 'ms' THEN 'Computer file'
  WHEN 'oc' THEN 'Kit'
  WHEN 'om' THEN 'Kit'
  WHEN 'pc' THEN 'Mixed material'
  WHEN 'pm' THEN 'Mixed material'
  WHEN 'rc' THEN '3D Object'
  WHEN 'rm' THEN '3D Object'
  WHEN 'tc' THEN 'Manuscript'
  WHEN 'tm' THEN 'Manuscript'
  ELSE 'unknown' END AS Type,
COUNT(DISTINCT biblionumber) AS Titles,
COUNT(itemnumber) AS Volumes
FROM biblio_metadata
LEFT JOIN items USING (biblionumber)
WHERE
 ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="n"]')
!= 1
GROUP BY Type
WITH ROLLUP

which generates the following

TypeTitlesVolumes
2D image 65 92
3D Object 21 21
Book 1193934 1329489
Computer file 630 1555
Journal 38568 542424
Kit 41 66
Manuscript 5461 14120
Map 4754 5770
Mixed material 253 2517
Music Score 11948 15757
Music sound recording 9193 9766
Non-music sound recording 613 818
unknown 1018 214
Video recording and motion pictures 15246 17090
1281745 1939699
titles is the number of bib records and volumes are the number of items
attached to a bib record.

Best regards,
Michael
_
*Michael J. Sutherland*
University Libraries
Virginia Tech
sudrl...@vt.edu | 540.231.9669 <+15402319669>



On Wed, May 27, 2020 at 2:33 PM Elaine Bradtke  wrote:

> Yes, I think I've got it now.  Thanks for your help.  The hard part was
> figuring out where to find the itemtype in the bib record.
> SELECT biblio.biblionumber,
> SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS "Position06"
> FROM biblio
> LEFT JOIN biblio_metadata USING (biblionumber)
> WHERE ExtractValue( metadata, '//datafield[@tag="942"]/subfield[@code="c"]'
> ) = <>
> Elaine Bradtke
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
>
>
> On Wed, May 27, 2020 at 10:50 AM Elaine Bradtke  wrote:
>
> > It doesn't throw up error messages, but what I really need is a list of
> > biblio numbers, with the info in position 6 of the leader and the
> itemtype
> > from the bib record, but also choose a specific itemtype (not books).
> I'm
> > going to try to mash up what you did with another report

Re: [Koha] Report help needed

[Elaine Bradtke]

Not condescending at all, the things I don't know about reports would fill
a book.
Interesting report you have there by the way.
The purpose for mine is to double check the position 06 against the 942 $c
because there is a bug 19419 that seems to cause trouble with the 008 if
the Leader 06 is not set correctly for the format (still trying to verify
that's what's going on).  I wanted to find any records that didn't have the
appropriate Leader 06 for a given format as specified in the 942.
Elaine Bradtke
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999


On Wed, May 27, 2020 at 2:00 PM Michael Sutherland  wrote:

> Elaine,
>
> Maybe this would be helpful for those codes -
> https://www.loc.gov/marc/bibliographic/bdleader.html
>
> And, to break it down for those that do not know and are interested, (at
> the risk of condescending, which is not my intention, please, please, I
> hope you do not take it that way)
>
> in the expression - SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS
> "Position06"
>
> you are wanting to extract a piece (substring) from the entire leader
> string - metadata,'//leader' - the 7 is the value that specifies the
> initial position from which the characters can be extracted. The first
> position of the expression starts with 1. But, in MARC the first position
> is 0 (zero). The next value is a positive integer value that specifies the
> ending limit and determines how many characters are going to be
> extracted from the given expression in this case '1', which will give you
> the single letter code in the leader 06, or the seventh position, however
> you choose to look at it.
>
> So, in collaboration with ByWater Solutions, I have co-written the
> following report, which I use to report collection statistics by itemtype
> to ACRL and ARL on our collection sans suppressed records from the OPAC. It
> takes the combination of the leader 06 and 07 to determine itemtype and
> count them.
>
> SELECT
> CASE SUBSTR(metadata,282,2)
>   WHEN ' m' THEN 'Book'
>   WHEN '2m' THEN 'Book'
>   WHEN 'am' THEN 'Book'
>   WHEN 'aa' THEN 'Book'
>   WHEN 'ac' THEN 'Book'
>   WHEN 'ad' THEN 'Book'
>   WHEN 'ai' THEN 'Book'
>   WHEN 'as' THEN 'Journal'
>   WHEN 'cc' THEN 'Music Score'
>   WHEN 'cm' THEN 'Music Score'
>   WHEN 'dm' THEN 'Music Score'
>   WHEN 'ei' THEN 'Map'
>   WHEN 'em' THEN 'Map'
>   WHEN 'es' THEN 'Map'
>   WHEN 'ga' THEN 'Video recording and motion pictures'
>   WHEN 'gs' THEN 'Video recording and motion pictures'
>   WHEN 'gc' THEN 'Video recording and motion pictures'
>   WHEN 'gi' THEN 'Video recording and motion pictures'
>   WHEN 'gm' THEN 'Video recording and motion pictures'
>   WHEN 'ic' THEN 'Non-music sound recording'
>   WHEN 'im' THEN 'Non-music sound recording'
>   WHEN 'is' THEN 'Non-music sound recording'
>   WHEN 'jm' THEN 'Music sound recording'
>   WHEN 'jc' THEN 'Music sound recording'
>   WHEN 'ji' THEN 'Music sound recording'
>   WHEN 'kc' THEN '2D image'
>   WHEN 'km' THEN '2D image'
>   WHEN 'ma' THEN 'Computer file'
>   WHEN 'mi' THEN 'Computer file'
>   WHEN 'mm' THEN 'Computer file'
>   WHEN 'ms' THEN 'Computer file'
>   WHEN 'oc' THEN 'Kit'
>   WHEN 'om' THEN 'Kit'
>   WHEN 'pc' THEN 'Mixed material'
>   WHEN 'pm' THEN 'Mixed material'
>   WHEN 'rc' THEN '3D Object'
>   WHEN 'rm' THEN '3D Object'
>   WHEN 'tc' THEN 'Manuscript'
>   WHEN 'tm' THEN 'Manuscript'
>   ELSE 'unknown' END AS Type,
> COUNT(DISTINCT biblionumber) AS Titles,
> COUNT(itemnumber) AS Volumes
> FROM biblio_metadata
> LEFT JOIN items USING (biblionumber)
> WHERE
>  ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="n"]')
> != 1
> GROUP BY Type
> WITH ROLLUP
>
> which generates the following
>
> TypeTitlesVolumes
> 2D image 65 92
> 3D Object 21 21
> Book 1193934 1329489
> Computer file 630 1555
> Journal 38568 542424
> Kit 41 66
> Manuscript 5461 14120
> Map 4754 5770
> Mixed material 253 2517
> Music Score 11948 15757
> Music sound recording 9193 9766
> Non-music sound recording 613 818
> unknown 1018 214
> Video recording and motion pictures 15246 17090
> 1281745 1939699
> titles is the number of bib records and volumes are the number of items
> attached to a bib record.
>
> Best regards,
> Michael
> _
> *Michael J. Sutherland*
> University Libraries
> Virginia Tech
> sudrl...@vt.edu | 540.231.9669 <+15402319669>
>
>
>
> On Wed, May 27, 2020 at 2:33 PM Elaine Bradtke  wrot

Re: [Koha] Report help needed

[Michael Sutherland]

Thank you. I'm unfamiliar with that particular bug and will check it out
for our records.

Best,
Michael
_
*Michael J. Sutherland*
University Libraries
Virginia Tech
sudrl...@vt.edu | 540.231.9669 <+15402319669>



On Wed, May 27, 2020 at 7:05 PM Elaine Bradtke  wrote:

> Not condescending at all, the things I don't know about reports would fill
> a book.
> Interesting report you have there by the way.
> The purpose for mine is to double check the position 06 against the 942 $c
> because there is a bug 19419 that seems to cause trouble with the 008 if
> the Leader 06 is not set correctly for the format (still trying to verify
> that's what's going on).  I wanted to find any records that didn't have the
> appropriate Leader 06 for a given format as specified in the 942.
> Elaine Bradtke
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
>
>
> On Wed, May 27, 2020 at 2:00 PM Michael Sutherland 
> wrote:
>
> > Elaine,
> >
> > Maybe this would be helpful for those codes -
> > https://www.loc.gov/marc/bibliographic/bdleader.html
> >
> > And, to break it down for those that do not know and are interested, (at
> > the risk of condescending, which is not my intention, please, please, I
> > hope you do not take it that way)
> >
> > in the expression - SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS
> > "Position06"
> >
> > you are wanting to extract a piece (substring) from the entire leader
> > string - metadata,'//leader' - the 7 is the value that specifies the
> > initial position from which the characters can be extracted. The first
> > position of the expression starts with 1. But, in MARC the first position
> > is 0 (zero). The next value is a positive integer value that specifies
> the
> > ending limit and determines how many characters are going to be
> > extracted from the given expression in this case '1', which will give you
> > the single letter code in the leader 06, or the seventh position, however
> > you choose to look at it.
> >
> > So, in collaboration with ByWater Solutions, I have co-written the
> > following report, which I use to report collection statistics by itemtype
> > to ACRL and ARL on our collection sans suppressed records from the OPAC.
> It
> > takes the combination of the leader 06 and 07 to determine itemtype and
> > count them.
> >
> > SELECT
> > CASE SUBSTR(metadata,282,2)
> >   WHEN ' m' THEN 'Book'
> >   WHEN '2m' THEN 'Book'
> >   WHEN 'am' THEN 'Book'
> >   WHEN 'aa' THEN 'Book'
> >   WHEN 'ac' THEN 'Book'
> >   WHEN 'ad' THEN 'Book'
> >   WHEN 'ai' THEN 'Book'
> >   WHEN 'as' THEN 'Journal'
> >   WHEN 'cc' THEN 'Music Score'
> >   WHEN 'cm' THEN 'Music Score'
> >   WHEN 'dm' THEN 'Music Score'
> >   WHEN 'ei' THEN 'Map'
> >   WHEN 'em' THEN 'Map'
> >   WHEN 'es' THEN 'Map'
> >   WHEN 'ga' THEN 'Video recording and motion pictures'
> >   WHEN 'gs' THEN 'Video recording and motion pictures'
> >   WHEN 'gc' THEN 'Video recording and motion pictures'
> >   WHEN 'gi' THEN 'Video recording and motion pictures'
> >   WHEN 'gm' THEN 'Video recording and motion pictures'
> >   WHEN 'ic' THEN 'Non-music sound recording'
> >   WHEN 'im' THEN 'Non-music sound recording'
> >   WHEN 'is' THEN 'Non-music sound recording'
> >   WHEN 'jm' THEN 'Music sound recording'
> >   WHEN 'jc' THEN 'Music sound recording'
> >   WHEN 'ji' THEN 'Music sound recording'
> >   WHEN 'kc' THEN '2D image'
> >   WHEN 'km' THEN '2D image'
> >   WHEN 'ma' THEN 'Computer file'
> >   WHEN 'mi' THEN 'Computer file'
> >   WHEN 'mm' THEN 'Computer file'
> >   WHEN 'ms' THEN 'Computer file'
> >   WHEN 'oc' THEN 'Kit'
> >   WHEN 'om' THEN 'Kit'
> >   WHEN 'pc' THEN 'Mixed material'
> >   WHEN 'pm' THEN 'Mixed material'
> >   WHEN 'rc' THEN '3D Object'
> >   WHEN 'rm' THEN '3D Object'
> >   WHEN 'tc' THEN 'Manuscript'
> >   WHEN 'tm' THEN 'Manuscript'
> >   ELSE 'unknown' END AS Type,
> > COUNT(DISTINCT biblionumber) AS Titles,
> > COUNT(itemnumber) AS Volumes
> > FROM biblio_metadata
> > LEFT JOIN items USING (biblionumber)
> > WHERE
> >  ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="n"]')
> > != 1
> > GROUP BY Type
> > WITH ROLLUP
> >
> > which generates the following
> >
> > TypeTitlesVolumes
> > 2D image 65 92
> > 3D Object 21 21
> > Book 1193934 1329489
> > Computer file 630 1555
> > Journal 38568 542424
> > Kit 41 66
> > Manuscript 5461 14120
> > Map 4754 5770
> > Mixed materi

Re: [Koha] Report help needed

[Mark Alexander]

Excerpts from Michael Sutherland's message of 2020-05-27 16:59:50 -0400:
> CASE SUBSTR(metadata,282,2)

I tried this myself, and it does seem to work.  But I worry that
the 282 value might not be correct in the future, should the XML
format of the metadata change even slightly.  In particular,
the XML header has a field called xsi:schemaLocation that contains
a URL that might change someday.  I could very wrong about this,
though; perhaps the format really is stable.

This does the same job and seems less obscure (to me, anyway):

  substring(ExtractValue(metadata, '//leader'), 7, 2)

But perhaps it's not as fast?  I'm not a MySQL expert by any means.
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Michael Sutherland]

I do not know about the speed and I'm not an expert either.  The beauty of
the reports is that we can borrow them from others, save them and change
them as needed, right? It is the same as the former query
'ExtractValue(marcxml'
changing to ExtractValue(metadata'.  All of the reports in the reports
library  should
be and look to be updated for the new versions to help new and current
users.  I remember trying a number of reports in the library that did not
work for me.

Best,
M.
_
*Michael J. Sutherland*
University Libraries
Virginia Tech
sudrl...@vt.edu | 540.231.9669 <+15402319669>



On Wed, May 27, 2020 at 7:43 PM Mark Alexander  wrote:

> Excerpts from Michael Sutherland's message of 2020-05-27 16:59:50 -0400:
> > CASE SUBSTR(metadata,282,2)
>
> I tried this myself, and it does seem to work.  But I worry that
> the 282 value might not be correct in the future, should the XML
> format of the metadata change even slightly.  In particular,
> the XML header has a field called xsi:schemaLocation that contains
> a URL that might change someday.  I could very wrong about this,
> though; perhaps the format really is stable.
>
> This does the same job and seems less obscure (to me, anyway):
>
>   substring(ExtractValue(metadata, '//leader'), 7, 2)
>
> But perhaps it's not as fast?  I'm not a MySQL expert by any means.
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Katrin Fischer]

Hi Elaine,

I notice that you compare to >= so it will include other subfields
bigger than... . Can you try changing this to read just =?

Katrin

On 05.01.21 20:10, Elaine Bradtke wrote:

I'm trying to find records lacking 300 fields. The following keeps
failing.  Any help would be appreciated. Thanks!

SELECT CONCAT('',biblionumber,'')
AS bibnumber
FROM
(SELECT biblionumber,
ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="a"]') AS
suba,
ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="b"]') AS
subb,
ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="3"]') AS
sub3,
FROM biblio_metadata) AS subfields
WHERE suba = ""
AND subb = ""
AND sub3 = ""

Elaine Bradtke
VWML 
Our income is severely diminished in these exceptionally uncertain times.
Please help us to survive beyond this national crisis. Donate now
.
English Folk Dance and Song Society 
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Lisette Scheer]

Elaine, 
There is a comma after AS sub3 that is not needed. 
I was able to get the report to run on my system after taking out the ,
Lisette
-Original Message-
From: Koha [mailto:koha-boun...@lists.katipo.co.nz] On Behalf Of Elaine Bradtke
Sent: Tuesday, January 5, 2021 11:10 AM
To: koha 
Subject: [Koha] Report help needed

I'm trying to find records lacking 300 fields. The following keeps failing.  
Any help would be appreciated. Thanks!

SELECT CONCAT('',biblionumber,'')
AS bibnumber
FROM
(SELECT biblionumber,
ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="a"]') AS suba,
ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="b"]') AS subb,
ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="3"]') AS sub3, 
FROM biblio_metadata) AS subfields WHERE suba = ""
AND subb = ""
AND sub3 = ""

Elaine Bradtke
VWML 
Our income is severely diminished in these exceptionally uncertain times.
Please help us to survive beyond this national crisis. Donate now 
.
English Folk Dance and Song Society  Cecil Sharp House, 
2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send an 
e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999 
___

Koha mailing list  http://koha-community.org Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha


___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Elaine Bradtke]

Removing the comma fixed it!
It was built off another report with a lot more fields.  I'm a proud
graduate of the cut and paste school of report writing. ;-)
Elaine
VWML 



On Tue, Jan 5, 2021 at 11:38 AM Lisette Scheer 
wrote:

> Elaine,
> There is a comma after AS sub3 that is not needed.
> I was able to get the report to run on my system after taking out the ,
> Lisette
> -Original Message-
> From: Koha [mailto:koha-boun...@lists.katipo.co.nz] On Behalf Of Elaine
> Bradtke
> Sent: Tuesday, January 5, 2021 11:10 AM
> To: koha 
> Subject: [Koha] Report help needed
>
> I'm trying to find records lacking 300 fields. The following keeps
> failing.  Any help would be appreciated. Thanks!
>
> SELECT CONCAT(' ',biblionumber,'\">',biblionumber,'')
> AS bibnumber
> FROM
> (SELECT biblionumber,
> ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="a"]') AS
> suba,
> ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="b"]') AS
> subb,
> ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="3"]') AS
> sub3, FROM biblio_metadata) AS subfields WHERE suba = ""
> AND subb = ""
> AND sub3 = ""
>
> Elaine Bradtke
> VWML 
> Our income is severely diminished in these exceptionally uncertain times.
> Please help us to survive beyond this national crisis. Donate now <
> https://www.efdss.org/support-us>.
> English Folk Dance and Song Society  Cecil Sharp
> House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
> ___
>
> Koha mailing list  http://koha-community.org Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
>
>
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Alvaro Cornejo]

Hi Elaine,

I guess there is a space missing before FROM in ...main_headingFROM
`auth_header`...

And also a colon after GENRE/FORM)

Else you can try your SQL by parts and start by the basics. Something like

SELECT authid, datecreated,
authtypecode, concat(ExtractValue(`marcxml`,'//datafield[@tag="100"]/*'),
--), main_heading FROM `auth_header`WHERE datecreated SetASpecificDateHere

Regards

Alvaro
||
 Stay safe / Cuídate/  Reste sécurisé
*7* Switch off as you go / Apaga lo que no usas /  Débranchez au fur et à
mesure.
 *q *Recycle always / Recicla siempre / Recyclez toujours
 P Print only if absolutely necessary / Imprime solo si es necesario /
Imprimez seulement si nécessaire


Le mar. 10 mai 2022 à 12:43, Elaine Bradtke  a écrit :

> I imported the following report from Mana, but it doesn't work as is.  I
> tried changing `marcxml` to `metadata` but it still didn't work. The error
> message (You have an error in your SQL syntax; check the manual that
> corresponds to your MariaDB server version for the right syntax to use near
> '' at line 1) isn't specific enough for my limited abilities.  I'd be
> grateful for your help.
>
> Here's the original:
> SELECT authid, datecreated, authtypecode,
> concat(ExtractValue(`marcxml`,'//datafield[@tag="100"]/*'), --
> PERSO_NAMEExtractValue(`marcxml`,'//datafield[@tag="110"]/*'), --
> CORPO_NAMEExtractValue(`marcxml`,'//datafield[@tag="111"]/*'), --
> MEETI_NAMEExtractValue(`marcxml`,'//datafield[@tag="130"]/*'), --
> UNIF_TITLEExtractValue(`marcxml`,'//datafield[@tag="148"]/*'), --
> CHRON_TERMExtractValue(`marcxml`,'//datafield[@tag="150"]/*'), --
> TOPIC_TERMExtractValue(`marcxml`,'//datafield[@tag="151"]/*'), --
> GEOGR_NAMEExtractValue(`marcxml`,'//datafield[@tag="155"]/*')  --
> GENRE/FORM) main_headingFROM `auth_header`WHERE datecreated BETWEEN <<(from
> -mm-dd)>> AND <<(to -mm-dd)>>ORDER BY datecreated, authtypecode,
> main_heading
>
> Elaine Bradtke
> VWML 
> English Folk Dance and Song Society 
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Steven Nickerson]

Hi Elaine,
   I'm really not positive this is the issue, but I would double-check to make 
sure that all of your single and double quotes are standard ASCII quote marks.  
 It looks like several of them are "special" characters, perhaps automatically 
converted somewhere along the way in your copy/paste (thank you "smart" 
programs like MS Word).   Again, just a guess, and maybe those characters were 
correct when you executed the MySQL in the report and only got converted when 
entered into your email application.   They just look "suspect" to me.

-Original Message-
From: Elaine Bradtke [mailto:e...@efdss.org] 
Sent: Tuesday, May 20, 2014 12:58 PM
To: koha
Subject: [Koha] Report help needed

I'll admit, I'm the queen of cut and paste.  What I don't know about SQL would 
fill a book.

Using a report I already have on hand:
SELECT ExtractValue(b.marcxml,
'//datafield[@tag="245"]/subfield[@code="a"]'), ExtractValue(b.marcxml, 
'//datafield[@tag="260"]/subfield[@code="a"]'),ExtractValue(b.marcxml,
'//datafield[@tag="260"]/subfield[@code="c"]'), monthname(datecreated) AS 
month, year(datecreated) AS year, biblionumber AS biblionumber FROM biblio LEFT 
JOIN biblioitems b USING (biblionumber) WHERE datecreated BETWEEN <> AND <>

I tried to add some fields as per a request from one of our staff who would 
like a rather thorough list of items added to the catalogue within a time frame.

SELECT ExtractValue(b.marcxml,
'//datafield[@tag=“100”]/subfield[@code="a"]'), ExtractValue(b.marcxml, 
'//datafield[@tag="245"]/subfield[@code="a"]'), ExtractValue(b.marcxml, 
'//datafield[@tag="245"]/subfield[@code=“b”]’), ExtractValue(b.marcxml, 
'//datafield[@tag="245"]/subfield[@code=“c”]’), ExtractValue(b.marcxml, 
'//datafield[@tag="260"]/subfield[@code="a"]'), ExtractValue(b.marcxml, 
'//datafield[@tag=“773”]/subfield[@code=“t”]’), ExtractValue(b.marcxml, 
'//datafield[@tag=“773”]/subfield[@code=“g”]’), ExtractValue(b.marcxml, 
'//datafield[@tag="260"]/subfield[@code=“b”]’), ExtractValue(b.marcxml, 
'//datafield[@tag="260"]/subfield[@code="c"]'), ExtractValue(b.marcxml, 
'//datafield[@tag=“300”]/subfield[@code=“a”]’), ExtractValue(b.marcxml, 
'//datafield[@tag=“020”]/subfield[@code=“a”]’), ExtractValue(b.marcxml, 
'//datafield[@tag=“022”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS 
biblionumber FROM biblio LEFT JOIN biblioitems b USING (biblionumber) WHERE 
datecreated BETWEEN <> AND <>


The first report works.  The second doesn't.  I'm sure it's a syntax error, or 
something like that, but I'm blundering around in the dark.


Her request was for the following info: 100 field = Author | 245 = Title proper 
(including subtitles and statement of responsibility) | 773(?) = Periodical 
title |260 = place, publisher, and date | 300 = Extent | 020 = ISBN / 022 = ISSN


Can anyone help?

--
Elaine Bradtke
Data Wrangler
VWML
English Folk Dance and Song Society | http://www.efdss.org Cecil Sharp House, 2 
Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send an 
e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999
---
"Writing about music is like dancing about architecture"
--Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)


___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Bernardo Gonzalez Kriegel]

Hi Elaine,
yes, copy & paste :)

You paste an extra 'ExtractValue(b.marcxml,' before "monthname(datecreated)
AS month"

Try

SELECT
ExtractValue(b.marcxml, '//datafield[@tag="100"]/subfield[@code="a"]'),
ExtractValue(b.marcxml, '//datafield[@tag="245"]/subfield[@code="a"]'),
ExtractValue(b.marcxml, '//datafield[@tag="245"]/subfield[@code="b"]'),
ExtractValue(b.marcxml, '//datafield[@tag="245"]/subfield[@code="c"]'),
ExtractValue(b.marcxml, '//datafield[@tag="260"]/subfield[@code="a"]'),
ExtractValue(b.marcxml, '//datafield[@tag="773"]/subfield[@code="t"]'),
ExtractValue(b.marcxml, '//datafield[@tag="773"]/subfield[@code="g"]'),
ExtractValue(b.marcxml, '//datafield[@tag="260"]/subfield[@code="b"]'),
ExtractValue(b.marcxml, '//datafield[@tag="260"]/subfield[@code="c"]'),
ExtractValue(b.marcxml, '//datafield[@tag="300"]/subfield[@code="a"]'),
ExtractValue(b.marcxml, '//datafield[@tag="020"]/subfield[@code="a"]'),
ExtractValue(b.marcxml, '//datafield[@tag="022"]/subfield[@code="a"]'),
monthname(datecreated) AS month,
year(datecreated) AS year,
biblionumber AS biblionumber
FROM biblio
LEFT JOIN biblioitems b USING (biblionumber)
WHERE datecreated
BETWEEN <> AND <>


Bernardo

-- 
Bernardo Gonzalez Kriegel
bgkrie...@gmail.com


On Tue, May 20, 2014 at 1:57 PM, Elaine Bradtke  wrote:

> I'll admit, I'm the queen of cut and paste.  What I don't know about SQL
> would fill a book.
>
> Using a report I already have on hand:
> SELECT ExtractValue(b.marcxml,
> '//datafield[@tag="245"]/subfield[@code="a"]'), ExtractValue(b.marcxml,
> '//datafield[@tag="260"]/subfield[@code="a"]'),ExtractValue(b.marcxml,
> '//datafield[@tag="260"]/subfield[@code="c"]'), monthname(datecreated) AS
> month, year(datecreated) AS year, biblionumber AS biblionumber
> FROM biblio
> LEFT JOIN biblioitems b USING (biblionumber)
> WHERE datecreated BETWEEN <> AND <>
>
> I tried to add some fields as per a request from one of our staff who would
> like a rather thorough list of items added to the catalogue within a time
> frame.
>
> SELECT ExtractValue(b.marcxml,
> '//datafield[@tag=“100”]/subfield[@code="a"]'), ExtractValue(b.marcxml,
> '//datafield[@tag="245"]/subfield[@code="a"]'), ExtractValue(b.marcxml,
> '//datafield[@tag="245"]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
> '//datafield[@tag="245"]/subfield[@code=“c”]’), ExtractValue(b.marcxml,
> '//datafield[@tag="260"]/subfield[@code="a"]'), ExtractValue(b.marcxml,
> '//datafield[@tag=“773”]/subfield[@code=“t”]’), ExtractValue(b.marcxml,
> '//datafield[@tag=“773”]/subfield[@code=“g”]’), ExtractValue(b.marcxml,
> '//datafield[@tag="260"]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
> '//datafield[@tag="260"]/subfield[@code="c"]'), ExtractValue(b.marcxml,
> '//datafield[@tag=“300”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
> '//datafield[@tag=“020”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
> '//datafield[@tag=“022”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
> monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS
> biblionumber
> FROM biblio
> LEFT JOIN biblioitems b USING (biblionumber)
> WHERE datecreated BETWEEN <> AND <>
>
>
> The first report works.  The second doesn't.  I'm sure it's a syntax error,
> or something like that, but I'm blundering around in the dark.
>
>
> Her request was for the following info: 100 field = Author | 245 = Title
> proper (including subtitles and statement of responsibility) | 773(?) =
> Periodical title |260 = place, publisher, and date | 300 = Extent | 020 =
> ISBN / 022 = ISSN
>
>
> Can anyone help?
>
> --
> Elaine Bradtke
> Data Wrangler
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
> ---
> "Writing about music is like dancing about architecture"
> --Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
> ___
> 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

Re: [Koha] Report help needed

[Paul A]

It could be just the way that my email client is formatting characters, but 
you may have a problem with the " and '  (double and single quote 
marks.)  I read them below as “, ”, and ’


Maybe you used a text editor that allows "curlies" rather than "straights"?

Best -- Paul

At 05:57 PM 5/20/2014 +0100, Elaine Bradtke wrote:

I'll admit, I'm the queen of cut and paste.  What I don't know about SQL
would fill a book.

Using a report I already have on hand:
SELECT ExtractValue(b.marcxml,
'//datafield[@tag="245"]/subfield[@code="a"]'), ExtractValue(b.marcxml,
'//datafield[@tag="260"]/subfield[@code="a"]'),ExtractValue(b.marcxml,
'//datafield[@tag="260"]/subfield[@code="c"]'), monthname(datecreated) AS
month, year(datecreated) AS year, biblionumber AS biblionumber
FROM biblio
LEFT JOIN biblioitems b USING (biblionumber)
WHERE datecreated BETWEEN <> AND <>

I tried to add some fields as per a request from one of our staff who would
like a rather thorough list of items added to the catalogue within a time
frame.

SELECT ExtractValue(b.marcxml,
'//datafield[@tag=“100”]/subfield[@code="a"]'), ExtractValue(b.marcxml,
'//datafield[@tag="245"]/subfield[@code="a"]'), ExtractValue(b.marcxml,
'//datafield[@tag="245"]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
'//datafield[@tag="245"]/subfield[@code=“c”]’), ExtractValue(b.marcxml,
'//datafield[@tag="260"]/subfield[@code="a"]'), ExtractValue(b.marcxml,
'//datafield[@tag=“773”]/subfield[@code=“t”]’), 
ExtractValue(b.marcxml,
'//datafield[@tag=“773”]/subfield[@code=“g”]’), 
ExtractValue(b.marcxml,

'//datafield[@tag="260"]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
'//datafield[@tag="260"]/subfield[@code="c"]'), ExtractValue(b.marcxml,
'//datafield[@tag=“300”]/subfield[@code=“a”]’), 
ExtractValue(b.marcxml,
'//datafield[@tag=“020”]/subfield[@code=“a”]’), 
ExtractValue(b.marcxml,
'//datafield[@tag=“022”]/subfield[@code=“a”]’), 
ExtractValue(b.marcxml,

monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS
biblionumber
FROM biblio
LEFT JOIN biblioitems b USING (biblionumber)
WHERE datecreated BETWEEN <> AND <>


The first report works.  The second doesn't.  I'm sure it's a syntax error,
or something like that, but I'm blundering around in the dark.


Her request was for the following info: 100 field = Author | 245 = Title
proper (including subtitles and statement of responsibility) | 773(?) =
Periodical title |260 = place, publisher, and date | 300 = Extent | 020 =
ISBN / 022 = ISSN


Can anyone help?

--
Elaine Bradtke
Data Wrangler
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999
---
"Writing about music is like dancing about architecture"
--Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha


---
Maritime heritage and history, preservation and conservation,
research and education through the written word and the arts.
 and 

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Daniel Sanford]

It looks like the issue is
'//datafield[@tag=“022”]/
subfield[@code=“a”]’), ExtractValue(b.marcxml,
monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS

The last
ExtractValue(b.marcxml,
doesn't have a closing ) and you've stopped extracting XML so that is
causing one error use


//datafield[@tag=“022”]/
subfield[@code=“a”]’),
monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS

You've also got a mix of ’ and ' which are slightly different and causing
MySQL to misread the sections.

Likewise on the line
'//datafield[@tag="245"]/subfield[@code="a"]'), ExtractValue(b.marcxml,
'//datafield[@tag="245"]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
you'll note the a and b subfield codes have different quotation marks, the
one around a are right and the ones around b are not, a lot of the tags and
codes are using those and MySQL often errors out due to the that. The first
query you sent has all of those properly formatted.

Here's a cleaned up copy

 SELECT
ExtractValue(b.marcxml, '//datafield[@tag="100"]/subfield[@code="a"]'),
ExtractValue(b.marcxml, '//datafield[@tag="245"]/subfield[@code="a"]'),
ExtractValue(b.marcxml, '//datafield[@tag="245"]/subfield[@code="b"]'),
ExtractValue(b.marcxml, '//datafield[@tag="245"]/subfield[@code="c"]'),
ExtractValue(b.marcxml, '//datafield[@tag="260"]/subfield[@code="a"]'),
ExtractValue(b.marcxml, '//datafield[@tag="773"]/subfield[@code="t"]'),
ExtractValue(b.marcxml, '//datafield[@tag="773"]/subfield[@code="g"]'),
ExtractValue(b.marcxml, '//datafield[@tag="260"]/subfield[@code="b"]'),
ExtractValue(b.marcxml, '//datafield[@tag="260"]/subfield[@code="c"]'),
ExtractValue(b.marcxml, '//datafield[@tag="300"]/subfield[@code="a"]'),
ExtractValue(b.marcxml, '//datafield[@tag="020"]/subfield[@code="a"]'),
ExtractValue(b.marcxml, '//datafield[@tag="022"]/subfield[@code="a"]'),
monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS
biblionumber
FROM biblio
LEFT JOIN biblioitems b USING (biblionumber)
WHERE datecreated BETWEEN <> AND <>



Daniel Sanford
Assistant Technical Librarian for Digital Infrastructure
American Philosophical Society
105 South 5th Street
Philadelphia, PA 19106
Telephone: (215)-599-4313



On Tue, May 20, 2014 at 12:57 PM, Elaine Bradtke  wrote:

> I'll admit, I'm the queen of cut and paste.  What I don't know about SQL
> would fill a book.
>
> Using a report I already have on hand:
> SELECT ExtractValue(b.marcxml,
> '//datafield[@tag="245"]/subfield[@code="a"]'), ExtractValue(b.marcxml,
> '//datafield[@tag="260"]/subfield[@code="a"]'),ExtractValue(b.marcxml,
> '//datafield[@tag="260"]/subfield[@code="c"]'), monthname(datecreated) AS
> month, year(datecreated) AS year, biblionumber AS biblionumber
> FROM biblio
> LEFT JOIN biblioitems b USING (biblionumber)
> WHERE datecreated BETWEEN <> AND <>
>
> I tried to add some fields as per a request from one of our staff who would
> like a rather thorough list of items added to the catalogue within a time
> frame.
>
> SELECT ExtractValue(b.marcxml,
> '//datafield[@tag=“100”]/subfield[@code="a"]'), ExtractValue(b.marcxml,
> '//datafield[@tag="245"]/subfield[@code="a"]'), ExtractValue(b.marcxml,
> '//datafield[@tag="245"]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
> '//datafield[@tag="245"]/subfield[@code=“c”]’), ExtractValue(b.marcxml,
> '//datafield[@tag="260"]/subfield[@code="a"]'), ExtractValue(b.marcxml,
> '//datafield[@tag=“773”]/subfield[@code=“t”]’), ExtractValue(b.marcxml,
> '//datafield[@tag=“773”]/subfield[@code=“g”]’), ExtractValue(b.marcxml,
> '//datafield[@tag="260"]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
> '//datafield[@tag="260"]/subfield[@code="c"]'), ExtractValue(b.marcxml,
> '//datafield[@tag=“300”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
> '//datafield[@tag=“020”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
> '//datafield[@tag=“022”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
> monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS
> biblionumber
> FROM biblio
> LEFT JOIN biblioitems b USING (biblionumber)
> WHERE datecreated BETWEEN <> AND <>
>
>
> The first report works.  The second doesn't.  I'm sure it's a syntax error,
> or something like that, but I'm blundering around in the dark.
>
>
> Her request was for the following info: 100 field = Author | 245 = Title
> proper (including subtitles and statement of responsibility) | 773(?) =
> Periodical title |260 = place, publisher, and date | 300 = Extent | 020 =
> ISBN / 022 = ISSN
>
>
> Can anyone help?
>
> --
> Elaine Bradtke
> Data Wrangler
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered

Re: [Koha] Report help needed

[Elaine Bradtke]

Thanks, I'll try the various suggestions.  I actually did the copy and
paste in Koha. . . no text editor to blame.
But  there's the weirdness that goes on between a Mac and a PC (I use both
on the same machine with VMware), and might possibly have copied from one
to paste in the other. . .  Sometimes I have so many windows open I forget
where I am.

I'm sure the extra ExtractValue(b.marcxml didn't help!
Cheers,
E


On Wed, May 21, 2014 at 4:15 PM, Paul A wrote:

> It could be just the way that my email client is formatting characters,
> but you may have a problem with the " and '  (double and single quote
> marks.)  I read them below as “, †, and ’
>
> Maybe you used a text editor that allows "curlies" rather than "straights"?
>
> Best -- Paul
>
>
> At 05:57 PM 5/20/2014 +0100, Elaine Bradtke wrote:
>
>> I'll admit, I'm the queen of cut and paste.  What I don't know about SQL
>> would fill a book.
>>
>> Using a report I already have on hand:
>> SELECT ExtractValue(b.marcxml,
>> '//datafield[@tag="245"]/subfield[@code="a"]'), ExtractValue(b.marcxml,
>> '//datafield[@tag="260"]/subfield[@code="a"]'),ExtractValue(b.marcxml,
>> '//datafield[@tag="260"]/subfield[@code="c"]'), monthname(datecreated) AS
>> month, year(datecreated) AS year, biblionumber AS biblionumber
>> FROM biblio
>> LEFT JOIN biblioitems b USING (biblionumber)
>> WHERE datecreated BETWEEN <> AND <> (-mm-dd)>>
>>
>> I tried to add some fields as per a request from one of our staff who
>> would
>> like a rather thorough list of items added to the catalogue within a time
>> frame.
>>
>> SELECT ExtractValue(b.marcxml,
>> '//datafield[@tag=“100†]/subfield[@code="a"]'),
>> ExtractValue(b.marcxml,
>>
>> '//datafield[@tag="245"]/subfield[@code="a"]'), ExtractValue(b.marcxml,
>> '//datafield[@tag="245"]/subfield[@code=“b†]’),
>> ExtractValue(b.marcxml,
>> '//datafield[@tag="245"]/subfield[@code=“c†]’),
>> ExtractValue(b.marcxml,
>>
>> '//datafield[@tag="260"]/subfield[@code="a"]'), ExtractValue(b.marcxml,
>> '//datafield[@tag=“773†]/subfield[@code=“t†]’),
>> ExtractValue(b.marcxml,
>> '//datafield[@tag=“773†]/subfield[@code=“g†]’),
>> ExtractValue(b.marcxml,
>> '//datafield[@tag="260"]/subfield[@code=“b†]’),
>> ExtractValue(b.marcxml,
>>
>> '//datafield[@tag="260"]/subfield[@code="c"]'), ExtractValue(b.marcxml,
>> '//datafield[@tag=“300†]/subfield[@code=“a†]’),
>> ExtractValue(b.marcxml,
>> '//datafield[@tag=“020†]/subfield[@code=“a†]’),
>> ExtractValue(b.marcxml,
>> '//datafield[@tag=“022†]/subfield[@code=“a†]’),
>> ExtractValue(b.marcxml,
>>
>> monthname(datecreated) AS month, year(datecreated) AS year, biblionumber
>> AS
>> biblionumber
>> FROM biblio
>> LEFT JOIN biblioitems b USING (biblionumber)
>> WHERE datecreated BETWEEN <> AND <> (-mm-dd)>>
>>
>>
>> The first report works.  The second doesn't.  I'm sure it's a syntax
>> error,
>> or something like that, but I'm blundering around in the dark.
>>
>>
>> Her request was for the following info: 100 field = Author | 245 = Title
>> proper (including subtitles and statement of responsibility) | 773(?) =
>> Periodical title |260 = place, publisher, and date | 300 = Extent | 020 =
>> ISBN / 022 = ISSN
>>
>>
>> Can anyone help?
>>
>> --
>> Elaine Bradtke
>> Data Wrangler
>> VWML
>> English Folk Dance and Song Society | http://www.efdss.org
>> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
>> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance
>> and
>> Song Society in London, England. If you wish to phone me personally, send
>> an e-mail first. I work off site)
>> 
>> --
>> Registered Company No. 297142
>> Charity Registered in England and Wales No. 305999
>> 
>> ---
>> "Writing about music is like dancing about architecture"
>> --Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
>> ___
>> Koha mailing list  http://koha-community.org
>> Koha@lists.katipo.co.nz
>> http://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
> ---
> Maritime heritage and history, preservation and conservation,
> research and education through the written word and the arts.
>  and 
>
>
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>



-- 
Elaine Bradtke
Data Wrangler
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Regi

Re: [Koha] Report help needed

[Heather Braum (NEKLS)]

Elaine, try the below. It looks like you were missing some commas -- that
was what was causing the syntax errors; you can then name the columns like
I did below or however you want using as ___ single-word (as 780t) or as
"two or more words" (as 780w field_).

Make sure every column of data in the select statement has a comma after
it, except for the last on before the from statement.

Hope that helps!  -- it ran on my system.

SELECT
biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype,

ExtractValue(biblioitems.marcxml,
'//datafield[@tag="780"]/subfield[@code="t"]') as 780t,
ExtractValue(biblioitems.marcxml,
'//datafield[@tag="780"]/subfield[@code="w"]') as "780w field",
ExtractValue(biblioitems.marcxml,
'//datafield[@tag="866"]/subfield[@code="a"]') as 866a,
ExtractValue(biblioitems.marcxml,
'//datafield[@tag="866"]/subfield[@code="z"]') as 866z
FROM biblioitems LEFT JOIN biblio USING (biblionumber)
WHERE biblioitems.itemtype='CR'

Heather Braum
NExpress Coordinator
Resource Sharing Librarian
Northeast Kansas Library System
hbr...@nekls.org

"The illiterate of the 21st century will not be those who cannot read
and write, but those who cannot learn, unlearn, and relearn." ~Alvin
Toffler, *Rethinking the Future*




On Mon, Nov 17, 2014 at 5:38 PM, Elaine Bradtke  wrote:

> I have a request for a report that is beyond my paltry abilities and that
> of the report builder.
>
>
> I have this, and it works (thanks Liz!):
> SELECT
>
>  
> biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype
> FROM biblioitems LEFT JOIN biblio USING (biblionumber)   WHERE
> biblioitems.itemtype='CR'
>
> But I need to add in more columns that involve the MARC XML
>
> Specifically
> 780 t (previous title)
> 780 w (previous control no.)
> 866 a (holdings)
> 866 z (note)
> 867 e (source)
>
> When I try to add in:
>   Extract value ExtractValue(marcxml,
> '//datafield[@tag="780"]/subfield[@code="t"]')
> I manage to break the above query and it complains of syntax errors.
> Does it matter where this Extract phrase comes in?
> How do you handle multiple subfields (780 t and w for instance)
> For that matter, how do you handle multiple fields?  And how can I label
> the columns
>
> The following (and a few variations on the theme) did not work:
>
> SELECT
>
>  
> biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype
> ExtractValue(marcxml, '//datafield[@tag="780"]/subfield[@code="t"]')
> ExtractValue(marcxml, '//datafield[@tag="780"]/subfield[@code="w"]')
> ExtractValue(marcxml, '//datafield[@tag="866"]/subfield[@code="a"]')
> ExtractValue(marcxml, '//datafield[@tag="866"]/subfield[@code="z"]')
> FROM biblioitems LEFT JOIN biblio USING (biblionumber)   WHERE
> biblioitems.itemtype='CR'
>
>
> As always I am very, very grateful for whatever help you can give.
> Thanks
> --
> Elaine Bradtke
> Data Wrangler
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
> ---
> "Writing about music is like dancing about architecture"
> --Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
> ___
> 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

Re: [Koha] Report help needed

[Elaine Bradtke]

Thanks Heather, and double thanks for the hint about commas.  It only takes
one little missing piece of the puzzle for the whole thing to fail.
I could really use something like SQL queries for Dummies.
Elaine

On Mon, Nov 17, 2014 at 11:47 PM, Heather Braum (NEKLS) 
wrote:

> Elaine, try the below. It looks like you were missing some commas -- that
> was what was causing the syntax errors; you can then name the columns like
> I did below or however you want using as ___ single-word (as 780t) or as
> "two or more words" (as 780w field_).
>
> Make sure every column of data in the select statement has a comma after
> it, except for the last on before the from statement.
>
> Hope that helps!  -- it ran on my system.
>
> SELECT
> biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype,
>
> ExtractValue(biblioitems.marcxml,
> '//datafield[@tag="780"]/subfield[@code="t"]') as 780t,
> ExtractValue(biblioitems.marcxml,
> '//datafield[@tag="780"]/subfield[@code="w"]') as "780w field",
> ExtractValue(biblioitems.marcxml,
> '//datafield[@tag="866"]/subfield[@code="a"]') as 866a,
> ExtractValue(biblioitems.marcxml,
> '//datafield[@tag="866"]/subfield[@code="z"]') as 866z
> FROM biblioitems LEFT JOIN biblio USING (biblionumber)
> WHERE biblioitems.itemtype='CR'
>
> Heather Braum
> NExpress Coordinator
> Resource Sharing Librarian
> Northeast Kansas Library System
> hbr...@nekls.org
>
> "The illiterate of the 21st century will not be those who cannot read
> and write, but those who cannot learn, unlearn, and relearn." ~Alvin
> Toffler, *Rethinking the Future*
>
>
>
>
> On Mon, Nov 17, 2014 at 5:38 PM, Elaine Bradtke  wrote:
>
>> I have a request for a report that is beyond my paltry abilities and that
>> of the report builder.
>>
>>
>> I have this, and it works (thanks Liz!):
>> SELECT
>>
>>  
>> biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype
>> FROM biblioitems LEFT JOIN biblio USING (biblionumber)   WHERE
>> biblioitems.itemtype='CR'
>>
>> But I need to add in more columns that involve the MARC XML
>>
>> Specifically
>> 780 t (previous title)
>> 780 w (previous control no.)
>> 866 a (holdings)
>> 866 z (note)
>> 867 e (source)
>>
>> When I try to add in:
>>   Extract value ExtractValue(marcxml,
>> '//datafield[@tag="780"]/subfield[@code="t"]')
>> I manage to break the above query and it complains of syntax errors.
>> Does it matter where this Extract phrase comes in?
>> How do you handle multiple subfields (780 t and w for instance)
>> For that matter, how do you handle multiple fields?  And how can I label
>> the columns
>>
>> The following (and a few variations on the theme) did not work:
>>
>> SELECT
>>
>>  
>> biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype
>> ExtractValue(marcxml, '//datafield[@tag="780"]/subfield[@code="t"]')
>> ExtractValue(marcxml, '//datafield[@tag="780"]/subfield[@code="w"]')
>> ExtractValue(marcxml, '//datafield[@tag="866"]/subfield[@code="a"]')
>> ExtractValue(marcxml, '//datafield[@tag="866"]/subfield[@code="z"]')
>> FROM biblioitems LEFT JOIN biblio USING (biblionumber)   WHERE
>> biblioitems.itemtype='CR'
>>
>>
>> As always I am very, very grateful for whatever help you can give.
>> Thanks
>> --
>> Elaine Bradtke
>> Data Wrangler
>> VWML
>> English Folk Dance and Song Society | http://www.efdss.org
>> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
>> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance
>> and
>> Song Society in London, England. If you wish to phone me personally, send
>> an e-mail first. I work off site)
>> --
>> Registered Company No. 297142
>> Charity Registered in England and Wales No. 305999
>>
>> ---
>> "Writing about music is like dancing about architecture"
>> --Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
>> ___
>> Koha mailing list  http://koha-community.org
>> Koha@lists.katipo.co.nz
>> http://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
>


-- 
Elaine Bradtke
Data Wrangler
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999
---
"Writing about music is like dancing about architecture"
--Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
___
Koha mailing list  http://koha-communi

Re: [Koha] Report Help - With Rollup

[Nick Clemens]

It looks like there isn't a direct way to do that, see
here,
 but you can wrap the query up and join it to authorised values and make it
work with the caution that any circs without location defined will also end
up labeled "Grand Total' and should be fixed before running the report.

SELECT IFNULL(a.lib,'Grand Total') as Location, thingy.circs AS Circulations
FROM (select i.location as loction, count(s.datetime) as
circs from statistics s left join items i using (itemnumber)
left join borrowers p using (borrowernumber)
where s.type in ('issue','renew') and p.categorycode != 'LIBRARYUSE' and
s.branch=<> and s.datetime between
<> and <>
group by i.location with rollup ) AS thingy
LEFT JOIN authorised_values a on thingy.loction=a.authorised_value



On Thu, Mar 20, 2014 at 9:11 AM, Nicole Engard  wrote:

> Hi all,
>
> I need some help with a report that has With RollUp in it.  I have
> used this before and had no problem, but for this report it keeps
> showing the last value 2 times at the bottom instead of 'grand total'.
>
> So here's how I started:
>
> select IFNULL(a.lib,'*GRAND TOTAL*') as location, count(s.datetime) as
> circs from statistics s left join items i using (itemnumber) left join
> borrowers p using (borrowernumber) LEFT JOIN authorised_values a ON
> (i.location=a.authorised_value) where s.type in ('issue','renew') and
> p.categorycode != 'LIBRARYUSE' and s.branch=< branch|branches>> and a.category='LOC' and s.datetime between
> <> and < (-mm-dd)|date>> group by i.location with rollup
>
> This runs and is right, but at the end it repeats the last shelving
> location instead of showing 'Grand Total'.  Next I tried:
>
> select if(i.location is null, '*GRAND TOTAL*', a.lib) as location,
> count(s.datetime) as circs
> from statistics s
> left join items i using (itemnumber)
> left join borrowers p using (borrowernumber)
> LEFT JOIN authorised_values a ON (i.location=a.authorised_value)
> where s.type in ('issue','renew') and p.categorycode != 'LIBRARYUSE'
> and s.branch='BAILEYCOVE'
> and a.category='LOC' and s.datetime between '2014-03-19'
> and '2014-03-20'
> group by i.location with rollup
>
>
> This repeated the same location over and over.  So then I tried this:
>
> select ifnull(i.location, 'GRAND TOTAL') as code, a.lib as location,
> count(s.datetime) as circs
> from statistics s
> left join items i using (itemnumber)
> left join borrowers p using (borrowernumber)
> LEFT JOIN authorised_values a ON (i.location=a.authorised_value)
> where s.type in ('issue','renew') and p.categorycode != 'LIBRARYUSE'
> and s.branch=<>
> and a.category='LOC' and s.datetime between < renewed between (-mm-dd)|date>>
> and <>
> group by i.location with rollup
>
> Which shows grand total, but still repeats the darn location name again.
>
> And this which shows only one location over and over:
>
> select case a.location when null 'grand total' else a.lib as location,
> count(s.datetime) as circs
> from statistics s
> left join items i using (itemnumber)
> left join borrowers p using (borrowernumber)
> LEFT JOIN authorised_values a ON (i.location=a.authorised_value)
> where s.type in ('issue','renew') and p.categorycode != 'LIBRARYUSE'
> and s.branch=<>
> and a.category='LOC' and s.datetime between < renewed between (-mm-dd)|date>>
> and <>
> group by i.location with rollup
>
>
> I'm up for any suggestion that gets me to have the final line just
> have the total and not a false location name.
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>



-- 
Nick Clemens
Quechee & Wilder Libraries
n...@quecheelibrary.org
http://www.QuecheeLibrary.org
Q (802) 295-1232 W (802) 295-6341
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help - With Rollup

[Nicole Engard]

Thank you so much Nick!!

Nicole
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help - With Rollup

[Nicole Engard]

Okay, any idea why the Juveasy might be showing 2 times?

http://screencast.com/t/6dbByW4eZ

I added the location code in case that was it - they're identical as
are their numbers.

On Thu, Mar 20, 2014 at 1:50 PM, Nicole Engard  wrote:
> Thank you so much Nick!!
>
> Nicole
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help - With Rollup

[Nick Clemens]

Here's a thought, it might be an error in the authorized values., and I
think it is a bug

I recreated it on our system by adding an authorized_value in a different
category with the same value as one in the LOC:
i.e.
I added AFIC to our Vendor category, with description Error!  The system
doesn't throw an error, and I can't see that value in the listing, but it
is then 'Adult Fiction' is duplicated in my report

Is the issue clear?




On Thu, Mar 20, 2014 at 7:04 PM, Nicole Engard  wrote:

> Okay, any idea why the Juveasy might be showing 2 times?
>
> http://screencast.com/t/6dbByW4eZ
>
> I added the location code in case that was it - they're identical as
> are their numbers.
>
> On Thu, Mar 20, 2014 at 1:50 PM, Nicole Engard  wrote:
> > Thank you so much Nick!!
> >
> > Nicole
>



-- 
Nick Clemens
Quechee & Wilder Libraries
n...@quecheelibrary.org
http://www.QuecheeLibrary.org
Q (802) 295-1232 W (802) 295-6341
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help - With Rollup

[Nicole Engard]

Nick,

I figured it out - we needed to limit to LOC authorized values:

SELECT IFNULL(a.lib,'*GRAND TOTAL*') as Location, thingy.circs AS
Circulations FROM (select i.location as loction, count(s.datetime) as
circs from statistics s left join items i using (itemnumber) left join
borrowers p using (borrowernumber) where s.type in ('issue','renew')
and p.categorycode != 'LIBRARYUSE' and s.branch=<> and s.datetime between
<> and <> group by i.location with rollup ) AS thingy LEFT
JOIN authorised_values a on thingy.loction=a.authorised_value
where a.category='LOC'

On Tue, Mar 25, 2014 at 1:08 PM, Nick Clemens  wrote:
> Here's a thought, it might be an error in the authorized values., and I
> think it is a bug
>
> I recreated it on our system by adding an authorized_value in a different
> category with the same value as one in the LOC:
> i.e.
> I added AFIC to our Vendor category, with description Error!  The system
> doesn't throw an error, and I can't see that value in the listing, but it is
> then 'Adult Fiction' is duplicated in my report
>
> Is the issue clear?
>
>
>
>
> On Thu, Mar 20, 2014 at 7:04 PM, Nicole Engard  wrote:
>>
>> Okay, any idea why the Juveasy might be showing 2 times?
>>
>> http://screencast.com/t/6dbByW4eZ
>>
>> I added the location code in case that was it - they're identical as
>> are their numbers.
>>
>> On Thu, Mar 20, 2014 at 1:50 PM, Nicole Engard  wrote:
>> > Thank you so much Nick!!
>> >
>> > Nicole
>
>
>
>
> --
> Nick Clemens
> Quechee & Wilder Libraries
> n...@quecheelibrary.org
> http://www.QuecheeLibrary.org
> Q (802) 295-1232 W (802) 295-6341
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help - With Rollup

[Nick Clemens]

Ah, I see now, not a bug, I thought the duplicate authorised_values weren't
showing up, they were just filing odd because I was using test values and
not sorting.


On Tue, Mar 25, 2014 at 4:03 PM, Nicole Engard  wrote:

> Nick,
>
> I figured it out - we needed to limit to LOC authorized values:
>
> SELECT IFNULL(a.lib,'*GRAND TOTAL*') as Location, thingy.circs AS
> Circulations FROM (select i.location as loction, count(s.datetime) as
> circs from statistics s left join items i using (itemnumber) left join
> borrowers p using (borrowernumber) where s.type in ('issue','renew')
> and p.categorycode != 'LIBRARYUSE' and s.branch=< branch|branches>> and s.datetime between
> <> and < (-mm-dd)|date>> group by i.location with rollup ) AS thingy LEFT
> JOIN authorised_values a on thingy.loction=a.authorised_value
> where a.category='LOC'
>
> On Tue, Mar 25, 2014 at 1:08 PM, Nick Clemens 
> wrote:
> > Here's a thought, it might be an error in the authorized values., and I
> > think it is a bug
> >
> > I recreated it on our system by adding an authorized_value in a different
> > category with the same value as one in the LOC:
> > i.e.
> > I added AFIC to our Vendor category, with description Error!  The system
> > doesn't throw an error, and I can't see that value in the listing, but
> it is
> > then 'Adult Fiction' is duplicated in my report
> >
> > Is the issue clear?
> >
> >
> >
> >
> > On Thu, Mar 20, 2014 at 7:04 PM, Nicole Engard 
> wrote:
> >>
> >> Okay, any idea why the Juveasy might be showing 2 times?
> >>
> >> http://screencast.com/t/6dbByW4eZ
> >>
> >> I added the location code in case that was it - they're identical as
> >> are their numbers.
> >>
> >> On Thu, Mar 20, 2014 at 1:50 PM, Nicole Engard 
> wrote:
> >> > Thank you so much Nick!!
> >> >
> >> > Nicole
> >
> >
> >
> >
> > --
> > Nick Clemens
> > Quechee & Wilder Libraries
> > n...@quecheelibrary.org
> > http://www.QuecheeLibrary.org
> > Q (802) 295-1232 W (802) 295-6341
>



-- 
Nick Clemens
Quechee & Wilder Libraries
n...@quecheelibrary.org
http://www.QuecheeLibrary.org
Q (802) 295-1232 W (802) 295-6341
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help - With Rollup

[Nicole Engard]

Okay - now here's the new issue - the grand total does not show:

SELECT IFNULL(a.lib,'*GRAND TOTAL*') as Location, thingy.circs AS
Circulations FROM (select i.location as loction, count(s.datetime) as
circs from statistics s left join items i using (itemnumber) left join
borrowers p using (borrowernumber) where s.type in ('issue','renew')
and p.categorycode != 'LIBRARYUSE' and s.branch=<> and s.datetime between
<> and <> group by i.location with rollup ) AS thingy LEFT
JOIN authorised_values a on thingy.loction=a.authorised_value
where a.category='LOC'

On Tue, Mar 25, 2014 at 4:16 PM, Nick Clemens  wrote:
> Ah, I see now, not a bug, I thought the duplicate authorised_values weren't
> showing up, they were just filing odd because I was using test values and
> not sorting.
>
>
> On Tue, Mar 25, 2014 at 4:03 PM, Nicole Engard  wrote:
>>
>> Nick,
>>
>> I figured it out - we needed to limit to LOC authorized values:
>>
>> SELECT IFNULL(a.lib,'*GRAND TOTAL*') as Location, thingy.circs AS
>> Circulations FROM (select i.location as loction, count(s.datetime) as
>> circs from statistics s left join items i using (itemnumber) left join
>> borrowers p using (borrowernumber) where s.type in ('issue','renew')
>> and p.categorycode != 'LIBRARYUSE' and s.branch=<> branch|branches>> and s.datetime between
>> <> and <> (-mm-dd)|date>> group by i.location with rollup ) AS thingy LEFT
>> JOIN authorised_values a on thingy.loction=a.authorised_value
>> where a.category='LOC'
>>
>> On Tue, Mar 25, 2014 at 1:08 PM, Nick Clemens 
>> wrote:
>> > Here's a thought, it might be an error in the authorized values., and I
>> > think it is a bug
>> >
>> > I recreated it on our system by adding an authorized_value in a
>> > different
>> > category with the same value as one in the LOC:
>> > i.e.
>> > I added AFIC to our Vendor category, with description Error!  The system
>> > doesn't throw an error, and I can't see that value in the listing, but
>> > it is
>> > then 'Adult Fiction' is duplicated in my report
>> >
>> > Is the issue clear?
>> >
>> >
>> >
>> >
>> > On Thu, Mar 20, 2014 at 7:04 PM, Nicole Engard 
>> > wrote:
>> >>
>> >> Okay, any idea why the Juveasy might be showing 2 times?
>> >>
>> >> http://screencast.com/t/6dbByW4eZ
>> >>
>> >> I added the location code in case that was it - they're identical as
>> >> are their numbers.
>> >>
>> >> On Thu, Mar 20, 2014 at 1:50 PM, Nicole Engard 
>> >> wrote:
>> >> > Thank you so much Nick!!
>> >> >
>> >> > Nicole
>> >
>> >
>> >
>> >
>> > --
>> > Nick Clemens
>> > Quechee & Wilder Libraries
>> > n...@quecheelibrary.org
>> > http://www.QuecheeLibrary.org
>> > Q (802) 295-1232 W (802) 295-6341
>
>
>
>
> --
> Nick Clemens
> Quechee & Wilder Libraries
> n...@quecheelibrary.org
> http://www.QuecheeLibrary.org
> Q (802) 295-1232 W (802) 295-6341
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help- reading history

[vishal patil]

Hii,

Visit koha report library for any sql report:

http://wiki.koha-community.org/wiki/SQL_Reports_Library

On Mon, Sep 22, 2014 at 12:38 PM, clint.deckard <
clint.deck...@frontiers.co.nz> wrote:

> A school I deal with would like a report that lists the reading history of
> the top 10 borrowers so that they can award a prize to each. As I am not a
> sql expert I would appreciate some assistance.
> Much appreciated,
> Clint.
>
>  --
> Clint Deckard
> Takapau
> Central Hawkes Bay, New Zealand.
>
>
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>



-- 


*Warm Regards,Vishal Patil*Software Engineer - Nucsoft Ltd.
phone: 022 32080161 | mob: 08767505528
www.osslabs.biz
www.nucsoft.com
vishal.pa...@osslabs.biz
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help- reading history

[clint.deckard]

Dear Vishal Patil,
thank you for the assistance.
The school did use the 'Patrons checking out the most' report and then 
copied and paste the reading history into a spreadsheet to produce the 
information they needed, it's just that they were hoping for a solution 
that required a little less intervention.
I should have provided more information in my initial request for help. 
My apologies.


I appreciate you taking the time to help.
Best wishes,
Clint.

vishal patil wrote:

Hii,

Visit koha report library for any sql report:

http://wiki.koha-community.org/wiki/SQL_Reports_Library

On Mon, Sep 22, 2014 at 12:38 PM, clint.deckard 
mailto:clint.deck...@frontiers.co.nz>> 
wrote:


A school I deal with would like a report that lists the reading
history of the top 10 borrowers so that they can award a prize to
each. As I am not a sql expert I would appreciate some assistance.
Much appreciated,
Clint.

 --
Clint Deckard
Takapau
Central Hawkes Bay, New Zealand.


_
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz 
http://lists.katipo.co.nz/__mailman/listinfo/koha





--
*Warm Regards,
Vishal Patil
*Software Engineer - Nucsoft Ltd.
phone: 022 32080161 | mob: 08767505528
www.osslabs.biz 
www.nucsoft.com 
vishal.pa...@osslabs.biz 



___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help- reading history

[Katelyn Browne]

Clint,

This is SUPER rough and cobbled-together, but it's the best I could do in a
few minutes. If I have time later, I'll try to clean it up a bit (or others
are welcome to do so). I have NOT tested it very extensively!

It doesn't show multiple checkouts of the same item by the same patron, but
otherwise it should do basically what you're asking.

SELECT topten.name, biblio.title, biblio.author
FROM statistics
LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
LEFT JOIN biblio ON (items.biblionumber = biblio.biblionumber)
LEFT JOIN (SELECT CONCAT(p.surname, ", ", p.firstname) AS name,
p.borrowernumber
FROM statistics s
LEFT JOIN borrowers p ON (s.borrowernumber=p.borrowernumber)
WHERE DATE(s.datetime) BETWEEN <>
AND <>
  AND s.itemnumber IS NOT NULL
GROUP BY p.borrowernumber
ORDER BY COUNT(s.borrowernumber) DESC
LIMIT 10) topten ON (topten.borrowernumber = statistics.borrowernumber)
WHERE topten.name IS NOT NULL AND statistics.datetime BETWEEN <> AND <>
AND statistics.type = "issue"
ORDER BY topten.name ASC

Katelyn Browne
Middle/High School Librarian
Capital City Public Charter School
100 Peabody Street NW
Washington, DC 20011
(202) 387-0309 x1745
kbro...@ccpcs.org
http://www.ccpcs.org/library/

On Mon, Sep 22, 2014 at 3:58 PM, clint.deckard <
clint.deck...@frontiers.co.nz> wrote:

> Dear Vishal Patil,
> thank you for the assistance.
> The school did use the 'Patrons checking out the most' report and then
> copied and paste the reading history into a spreadsheet to produce the
> information they needed, it's just that they were hoping for a solution
> that required a little less intervention.
> I should have provided more information in my initial request for help. My
> apologies.
>
> I appreciate you taking the time to help.
> Best wishes,
> Clint.
>
> vishal patil wrote:
>
>> Hii,
>>
>> Visit koha report library for any sql report:
>>
>> http://wiki.koha-community.org/wiki/SQL_Reports_Library
>>
>> On Mon, Sep 22, 2014 at 12:38 PM, clint.deckard <
>> clint.deck...@frontiers.co.nz >
>> wrote:
>>
>> A school I deal with would like a report that lists the reading
>> history of the top 10 borrowers so that they can award a prize to
>> each. As I am not a sql expert I would appreciate some assistance.
>> Much appreciated,
>> Clint.
>>
>>  --
>> Clint Deckard
>> Takapau
>> Central Hawkes Bay, New Zealand.
>>
>>
>> _
>> Koha mailing list  http://koha-community.org
>> Koha@lists.katipo.co.nz 
>> http://lists.katipo.co.nz/__mailman/listinfo/koha
>> 
>>
>>
>>
>>
>> --
>> *Warm Regards,
>> Vishal Patil
>> *Software Engineer - Nucsoft Ltd.
>> phone: 022 32080161 | mob: 08767505528
>> www.osslabs.biz 
>> www.nucsoft.com 
>> vishal.pa...@osslabs.biz 
>>
>>
> ___
> 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

Re: [Koha] Report Help - Extracting XML

[Daniel Grobani]

Nicole,

This should give you the 520a:

extractValue(biblioitems.marcxml,
"collection/record/datafield[@tag='520'][1]/subfield[@code='a']")

Of course, you'll have to do a join on biblioitems.


-
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-Extracting-XML-tp4640604p4640701.html
Sent from the Koha - Discuss mailing list archive at Nabble.com.
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help - Extracting XML

[Nicole Engard]

Daniel,

That part I knew :) it was getting it in to the query (the joining) I
was having trouble with :)

Nicole

On Wed, Jul 27, 2011 at 8:00 PM, Daniel Grobani
 wrote:
> Nicole,
>
> This should give you the 520a:
>
> extractValue(biblioitems.marcxml,
> "collection/record/datafield[@tag='520'][1]/subfield[@code='a']")
>
> Of course, you'll have to do a join on biblioitems.
>
>
> -
> 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-Extracting-XML-tp4640604p4640701.html
> Sent from the Koha - Discuss mailing list archive at Nabble.com.
> ___
> 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

Re: [Koha] Report Help - Extracting XML

[Grobani, Daniel]

How about LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = 
items.biblioitemnumber)?

Daniel

-Original Message-
From: Nicole Engard [mailto:neng...@gmail.com] 
Sent: Thursday, July 28, 2011 4:36 AM
To: Grobani, Daniel
Cc: koha@lists.katipo.co.nz
Subject: Re: [Koha] Report Help - Extracting XML

Daniel,

That part I knew :) it was getting it in to the query (the joining) I was 
having trouble with :)

Nicole

On Wed, Jul 27, 2011 at 8:00 PM, Daniel Grobani  
wrote:
> Nicole,
>
> This should give you the 520a:
>
> extractValue(biblioitems.marcxml,
> "collection/record/datafield[@tag='520'][1]/subfield[@code='a']")
>
> Of course, you'll have to do a join on biblioitems.
>
>
> -
> 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-Extracting-XML-tp4640604
> p4640701.html Sent from the Koha - Discuss mailing list archive at 
> Nabble.com.
> ___
> 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

Re: [Koha] Report Help - Extracting XML

[Nicole Engard]

If you don't mind, put your two pieces in to my report in the right
places - my problem is that I'm not sure the Join goes where I would
normally put it.

Nicole

On Thu, Jul 28, 2011 at 12:45 PM, Grobani, Daniel
 wrote:
> How about LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = 
> items.biblioitemnumber)?
>
> Daniel
>
> -Original Message-
> From: Nicole Engard [mailto:neng...@gmail.com]
> Sent: Thursday, July 28, 2011 4:36 AM
> To: Grobani, Daniel
> Cc: koha@lists.katipo.co.nz
> Subject: Re: [Koha] Report Help - Extracting XML
>
> Daniel,
>
> That part I knew :) it was getting it in to the query (the joining) I was 
> having trouble with :)
>
> Nicole
>
> On Wed, Jul 27, 2011 at 8:00 PM, Daniel Grobani  
> wrote:
>> Nicole,
>>
>> This should give you the 520a:
>>
>> extractValue(biblioitems.marcxml,
>> "collection/record/datafield[@tag='520'][1]/subfield[@code='a']")
>>
>> Of course, you'll have to do a join on biblioitems.
>>
>>
>> -
>> 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-Extracting-XML-tp4640604
>> p4640701.html Sent from the Koha - Discuss mailing list archive at
>> Nabble.com.
>> ___
>> 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

Re: [Koha] Report Help - Extracting XML

[Daniel Grobani]

Try this (I also did some light cleanup):

SELECT  COUNT(statistics.datetime) AS circs,
CONCAT(' \"/cgi- ',biblio.title,' ') AS Title,
CONCAT(' \"http://mycatalogv/cgi-
http://catalog.losgatosca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'
') AS URL,
biblio.author,
items.location,

extractValue(biblioitems.marcxml,"collection/record/datafield[@tag='520'][1]/subfield[@code='a']")
FROMstatistics
JOINitems ON (items.itemnumber=statistics.itemnumber)
LEFT JOIN   biblio ON (biblio.biblionumber=items.biblionumber)
LEFT JOIN   biblioitems ON (biblioitems.biblioitemnumber =
items.biblioitemnumber)
WHERE   DATE(statistics.datetime) BETWEEN '-mm-dd' AND '-mm-dd'
AND statistics.itemnumber IS NOT NULL
AND location = 'Shelving Location Code'
GROUP BYbiblio.biblionumber
ORDER BYcircs DESC
LIMIT   10


-
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-Extracting-XML-tp4640604p4644634.html
Sent from the Koha - Discuss mailing list archive at Nabble.com.
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help - Extracting XML

[Nicole Engard]

Okay - this:


select count(statistics.datetime) as circs, CONCAT('',biblio.title,'')
AS Title, CONCAT('http://catalog.mysiteca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'\">http://catalog.mysiteca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'')
as URL, biblio.author, items.location,
extractValue(biblioitems.marcxml,"collection/record/datafield[@tag='520'][1]/subfield[@code='a']")
FROMstatistics
JOINitems ON (items.itemnumber=statistics.itemnumber)
LEFT JOIN   biblio ON (biblio.biblionumber=items.biblionumber)
LEFT JOIN   biblioitems ON (biblioitems.biblioitemnumber =
items.biblioitemnumber)
WHERE   DATE(statistics.datetime) BETWEEN '2011-01-01' AND '2011-07-01'
   AND statistics.itemnumber IS NOT NULL
   AND location = 'Shelving Location Code'
GROUP BYbiblio.biblionumber
ORDER BYcircs DESC
LIMIT   10

Gets this:

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near '       statistics
JOIN            items ON (items.itemnumber=statistic' at line 4


Also, a tip - those <<>> sections I had in there are runtime
parameters for Koha (a neat trick that is documented on the wiki and
in the manual - in case you removed them cause you didn't know what
they were).


On Thu, Jul 28, 2011 at 7:41 PM, Daniel Grobani
 wrote:
> Try this (I also did some light cleanup):
>
> SELECT          COUNT(statistics.datetime) AS circs,
>                CONCAT(' \"/cgi- ',biblio.title,' ') AS Title,
>                CONCAT(' \"http://mycatalogv/cgi-
> http://catalog.losgatosca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'
> ') AS URL,
>                biblio.author,
>                items.location,
>
> extractValue(biblioitems.marcxml,"collection/record/datafield[@tag='520'][1]/subfield[@code='a']")
> FROM            statistics
> JOIN            items ON (items.itemnumber=statistics.itemnumber)
> LEFT JOIN       biblio ON (biblio.biblionumber=items.biblionumber)
> LEFT JOIN       biblioitems ON (biblioitems.biblioitemnumber =
> items.biblioitemnumber)
> WHERE           DATE(statistics.datetime) BETWEEN '-mm-dd' AND 
> '-mm-dd'
>                        AND statistics.itemnumber IS NOT NULL
>                        AND location = 'Shelving Location Code'
> GROUP BY        biblio.biblionumber
> ORDER BY        circs DESC
> LIMIT           10
>
>
> -
> 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-Extracting-XML-tp4640604p4644634.html
> Sent from the Koha - Discuss mailing list archive at Nabble.com.
> ___
> 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

Re: [Koha] Report Help - Extracting XML

[Grobani, Daniel]

Hmm, it works fine for me. It looks like it might have choked on the first 
tab--try replacing all the tabs I introduced to spaces.

Thanks for your reminder about the <<>> and sorry I removed them. I remember 
reading about that now, but because that works differently in our, um, flavor, 
it didn't stick.

Daniel

-Original Message-
From: Nicole Engard [mailto:neng...@gmail.com] 
Sent: Thursday, July 28, 2011 5:02 PM
To: Grobani, Daniel
Cc: koha@lists.katipo.co.nz
Subject: Re: [Koha] Report Help - Extracting XML

Okay - this:


select count(statistics.datetime) as circs, CONCAT('',biblio.title,'')
AS Title, CONCAT('http://catalog.mysiteca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'\">http://catalog.mysiteca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'')
as URL, biblio.author, items.location,
extractValue(biblioitems.marcxml,"collection/record/datafield[@tag='520'][1]/subfield[@code='a']")
FROMstatistics
JOINitems ON (items.itemnumber=statistics.itemnumber)
LEFT JOIN   biblio ON (biblio.biblionumber=items.biblionumber)
LEFT JOIN   biblioitems ON (biblioitems.biblioitemnumber =
items.biblioitemnumber)
WHERE   DATE(statistics.datetime) BETWEEN '2011-01-01' AND '2011-07-01'
   AND statistics.itemnumber IS NOT NULL
   AND location = 'Shelving Location Code'
GROUP BYbiblio.biblionumber
ORDER BYcircs DESC
LIMIT   10

Gets this:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near '     
  statistics JOIN            items ON (items.itemnumber=statistic' at line 4


Also, a tip - those <<>> sections I had in there are runtime parameters for 
Koha (a neat trick that is documented on the wiki and in the manual - in case 
you removed them cause you didn't know what they were).


On Thu, Jul 28, 2011 at 7:41 PM, Daniel Grobani  
wrote:
> Try this (I also did some light cleanup):
>
> SELECT          COUNT(statistics.datetime) AS circs,
>                CONCAT(' \"/cgi- ',biblio.title,' ') AS Title,
>                CONCAT(' \"http://mycatalogv/cgi- 
> http://catalog.losgatosca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'
> ') AS URL,
>                biblio.author,
>                items.location,
>
> extractValue(biblioitems.marcxml,"collection/record/datafield[@tag='52
> 0'][1]/subfield[@code='a']")
> FROM            statistics
> JOIN            items ON (items.itemnumber=statistics.itemnumber)
> LEFT JOIN       biblio ON (biblio.biblionumber=items.biblionumber)
> LEFT JOIN       biblioitems ON (biblioitems.biblioitemnumber =
> items.biblioitemnumber)
> WHERE           DATE(statistics.datetime) BETWEEN '-mm-dd' AND 
> '-mm-dd'
>                        AND statistics.itemnumber IS NOT NULL
>                        AND location = 'Shelving Location Code'
> GROUP BY        biblio.biblionumber
> ORDER BY        circs DESC
> LIMIT           10
>
>
> -
> 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-Extracting-XML-tp4640604
> p4644634.html Sent from the Koha - Discuss mailing list archive at 
> Nabble.com.
> ___
> 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

Re: [Koha] Report Help - Extracting XML

[Nicole Engard]

:) No problem! I actually had to remove them to test on the command line anyway.

You are right! It does work :) thanks - will share on the wiki with a
dual credit :)

Nicole

On Thu, Jul 28, 2011 at 8:21 PM, Grobani, Daniel
 wrote:
> Hmm, it works fine for me. It looks like it might have choked on the first 
> tab--try replacing all the tabs I introduced to spaces.
>
> Thanks for your reminder about the <<>> and sorry I removed them. I remember 
> reading about that now, but because that works differently in our, um, 
> flavor, it didn't stick.
>
> Daniel
>
> -Original Message-
> From: Nicole Engard [mailto:neng...@gmail.com]
> Sent: Thursday, July 28, 2011 5:02 PM
> To: Grobani, Daniel
> Cc: koha@lists.katipo.co.nz
> Subject: Re: [Koha] Report Help - Extracting XML
>
> Okay - this:
>
>
> select count(statistics.datetime) as circs, CONCAT(' href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.title,'')
> AS Title, CONCAT(' href=\"http://catalog.mysiteca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'\">http://catalog.mysiteca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'')
> as URL, biblio.author, items.location,
> extractValue(biblioitems.marcxml,"collection/record/datafield[@tag='520'][1]/subfield[@code='a']")
> FROM            statistics
> JOIN            items ON (items.itemnumber=statistics.itemnumber)
> LEFT JOIN       biblio ON (biblio.biblionumber=items.biblionumber)
> LEFT JOIN       biblioitems ON (biblioitems.biblioitemnumber =
> items.biblioitemnumber)
> WHERE           DATE(statistics.datetime) BETWEEN '2011-01-01' AND 
> '2011-07-01'
>                       AND statistics.itemnumber IS NOT NULL
>                       AND location = 'Shelving Location Code'
> GROUP BY        biblio.biblionumber
> ORDER BY        circs DESC
> LIMIT           10
>
> Gets this:
>
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
> that corresponds to your MySQL server version for the right syntax to use 
> near '       statistics JOIN            items ON (items.itemnumber=statistic' 
> at line 4
>
>
> Also, a tip - those <<>> sections I had in there are runtime parameters for 
> Koha (a neat trick that is documented on the wiki and in the manual - in case 
> you removed them cause you didn't know what they were).
>
>
> On Thu, Jul 28, 2011 at 7:41 PM, Daniel Grobani  
> wrote:
>> Try this (I also did some light cleanup):
>>
>> SELECT          COUNT(statistics.datetime) AS circs,
>>                CONCAT(' \"/cgi- ',biblio.title,' ') AS Title,
>>                CONCAT(' \"http://mycatalogv/cgi-
>> http://catalog.losgatosca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'
>> ') AS URL,
>>                biblio.author,
>>                items.location,
>>
>> extractValue(biblioitems.marcxml,"collection/record/datafield[@tag='52
>> 0'][1]/subfield[@code='a']")
>> FROM            statistics
>> JOIN            items ON (items.itemnumber=statistics.itemnumber)
>> LEFT JOIN       biblio ON (biblio.biblionumber=items.biblionumber)
>> LEFT JOIN       biblioitems ON (biblioitems.biblioitemnumber =
>> items.biblioitemnumber)
>> WHERE           DATE(statistics.datetime) BETWEEN '-mm-dd' AND 
>> '-mm-dd'
>>                        AND statistics.itemnumber IS NOT NULL
>>                        AND location = 'Shelving Location Code'
>> GROUP BY        biblio.biblionumber
>> ORDER BY        circs DESC
>> LIMIT           10
>>
>>
>> -
>> 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-Extracting-XML-tp4640604
>> p4644634.html Sent from the Koha - Discuss mailing list archive at
>> Nabble.com.
>> ___
>> 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

Re: [Koha] Report help needed - lost items

[Mark Alexander]

Excerpts from Elaine Bradtke's message of 2019-08-23 11:32:44 -0700:
> I've got the following adapted from one in the reports library.  But I need
> to limit it by item type, or not return everything that doesn't have an
> item record attached. We've got lots of components in our catalogue that
> don't have items and it's returning all of them:
> 
> SELECT
> CONCAT(
> ' bib.biblionumber,
> '\">',
> bib.title,
> ''
> ) AS Title,
> bib.title,
> bib.biblionumber,
> lostitems.barcode,
> lostitems.ccode,
> lostitems.onloan AS 'Checkout date'
> FROM
> biblio bib
> LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
> items.itemlost = 0)
> LEFT JOIN items AS lostitems ON (bib.biblionumber =
> lostitems.biblionumber AND lostitems.itemlost != 0)
> GROUP BY bib.biblionumber
> HAVING count(items.itemnumber) = 0

While I'm not an SQL expert by any means, I think I was able to get
this to work on my catalog by removing the HAVING clause and
putting "WHERE items.itemnumber IS NULL" before the GROUP clause:

SELECT
CONCAT(
'',
bib.title,
''
) AS Title,
bib.title,
bib.biblionumber,
lostitems.barcode,
lostitems.ccode,
lostitems.onloan AS 'Checkout date'
FROM
biblio bib
LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
items.itemlost = 0)
LEFT JOIN items AS lostitems ON (bib.biblionumber =
lostitems.biblionumber AND lostitems.itemlost != 0)
WHERE items.itemnumber IS NULL 
GROUP BY bib.biblionumber
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed - lost items

[Caroline Cyr-La-Rose]

Hello Elaine,

from what I understand, you want bibliographic information on lost items?

I'd need to know exactly the purpose of the report to help you more as 
there are things in there I don't understand (like why select barcode, 
when afterwards you group by biblionumber, or why the title is there twice).


First, instead of selecting from the biblio table, I would go the other 
way and select from the items table. That way, you are sure of getting 
only things that have an item.


If you want to limit by itemtype, you can add WHERE itype = > in your query to have the ability to choose your 
itemtype upon executing your report.


I cleaned your query up a little bit, but like I mentioned before, as I 
don't know the purpose, it might not be what you need.


SELECT
CONCAT(
'',
title,
''
) AS Title,
biblionumber,
barcode,
ccode,
onloan AS 'Checkout date'
FROM
items
JOIN biblio USING (biblionumber)
WHERE itemlost != 0
  AND itype = <>

Let us know if this works.
Caroline


On 19-08-23 15 h 00, Mark Alexander wrote:

Excerpts from Elaine Bradtke's message of 2019-08-23 11:32:44 -0700:

I've got the following adapted from one in the reports library.  But I need
to limit it by item type, or not return everything that doesn't have an
item record attached. We've got lots of components in our catalogue that
don't have items and it's returning all of them:

SELECT
 CONCAT(
 '',
 bib.title,
 ''
 ) AS Title,
 bib.title,
 bib.biblionumber,
 lostitems.barcode,
 lostitems.ccode,
 lostitems.onloan AS 'Checkout date'
FROM
 biblio bib
 LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
items.itemlost = 0)
 LEFT JOIN items AS lostitems ON (bib.biblionumber =
lostitems.biblionumber AND lostitems.itemlost != 0)
GROUP BY bib.biblionumber
HAVING count(items.itemnumber) = 0

While I'm not an SQL expert by any means, I think I was able to get
this to work on my catalog by removing the HAVING clause and
putting "WHERE items.itemnumber IS NULL" before the GROUP clause:

SELECT
 CONCAT(
 '',
 bib.title,
 ''
 ) AS Title,
 bib.title,
 bib.biblionumber,
 lostitems.barcode,
 lostitems.ccode,
 lostitems.onloan AS 'Checkout date'
FROM
 biblio bib
 LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
items.itemlost = 0)
 LEFT JOIN items AS lostitems ON (bib.biblionumber =
lostitems.biblionumber AND lostitems.itemlost != 0)
WHERE items.itemnumber IS NULL
GROUP BY bib.biblionumber
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha


--
Caroline Cyr La Rose, M.S.I.
Bibliothécaire | Responsable de produit

Tél. : 1-833-465-4276, poste 221
caroline.cyr-la-r...@inlibro.com 

INLiBRO | Spécialistes en technologies documentaires | www.inLibro.com 


___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha