[Koha] CCL query query
Hi, In the course of trying to devise a CCL query that will display books acquired by a branch in the last 30 days, I've found that compound queries don't seem to work the way I expect them to. I don't know if this is due to my misunderstanding or because I'm on LLEK. For example, I think the following should return all biblios having at least one item whose acqdate is 20011-04-13 and whose homebranch is SF: http://samuelmerritt.kohalibrary.com/cgi-bin/koha/opac-search.pl?q=ccl%3Dacqdate%3D2011-04-13+and+branch%3DSF. This returns one biblio with five items, but the only item with acqdate=2011-04-13 has branch=OAK, and the only item with branch=SF has acqdate=2009-06-17. Have I specified the syntax incorrectly? Many thanks, Daniel Grobani Library Technology Specialist John A. Graziano Memorial Library Samuel Merritt University dgrob...@samuelmerritt.edu ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Re: [Koha] CCL query query
Galen, Ack! That's what I feared. Thanks for confirming it, and for your clear explanation. It looks like I'll have to go back to generating a report instead. Daniel -Original Message- From: Galen Charlton [mailto:g...@esilibrary.com] Sent: Thursday, May 05, 2011 12:35 PM To: Grobani, Daniel Cc: koha@lists.katipo.co.nz Subject: Re: [Koha] CCL query query Hi Daniel, On May 5, 2011, at 3:03 PM, Grobani, Daniel wrote: > In the course of trying to devise a CCL query that will display books > acquired by a branch in the last 30 days, I've found that compound queries > don't seem to work the way I expect them to. I don't know if this is due to > my misunderstanding or because I'm on LLEK. > > For example, I think the following should return all biblios having at least > one item whose acqdate is 20011-04-13 and whose homebranch is SF: > http://samuelmerritt.kohalibrary.com/cgi-bin/koha/opac-search.pl?q=ccl%3Dacqdate%3D2011-04-13+and+branch%3DSF. > > This returns one biblio with five items, but the only item with > acqdate=2011-04-13 has branch=OAK, and the only item with branch=SF has > acqdate=2009-06-17. > > Have I specified the syntax incorrectly? No, the syntax is correct. What you're running into is the fact that the search engine is indexing the entire bib record, including item records, and the search itself is searching bibs, not items. Consequently, since the bib record in question meets both conditions of the query (i.e., it has a 952 containing a branch subfield whose value is SF and it has a 952 containing the specified acq date), the entire bib is retrieved. The bibliographic search engine is not currently set up to verify that queries on embedded item fields are all referring to the same item record. Regards, Galen -- Galen Charlton VP, Data Services Equinox Software, Inc. / Your Library's Guide to Open Source email: g...@esilibrary.com direct: +1 352-215-7548 skype: gmcharlt web:http://www.esilibrary.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
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
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
