Re: [Koha] Report Help - Extracting XML
:) 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
dgrob...@samuelmerritt.edu 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('a
href=\/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\',biblio.title,'/a')
AS Title, CONCAT('a
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,'/a')
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 dgrob...@samuelmerritt.edu
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
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 dgrob...@samuelmerritt.edu 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
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 dgrob...@samuelmerritt.edu 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 dgrob...@samuelmerritt.edu 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
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
Okay - this:
select count(statistics.datetime) as circs, CONCAT('a
href=\/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\',biblio.title,'/a')
AS Title, CONCAT('a
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,'/a')
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
dgrob...@samuelmerritt.edu 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
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('a
href=\/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\',biblio.title,'/a')
AS Title, CONCAT('a
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,'/a')
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 dgrob...@samuelmerritt.edu
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, 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
