Hello all, I need some report assistance. I want to add the summary (520a) to this report: 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://mycatalogv/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'\">http://catalog.losgatosca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'</a>') as URL, biblio.author, items.location from statistics join items on (items.itemnumber=statistics.itemnumber) left join biblio on (biblio.biblionumber=items.biblionumber) where DATE(statistics.datetime) Between <<Date between (yyyy-mm-dd)>> and <<and (yyyy-mm-dd)>> and statistics.itemnumber is not NULL and location = <<Shelving Location Code>> group by biblio.biblionumber order by circs DESC Limit 10 Can someone make some suggestions?
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-tp4640604p46407... Sent from the Koha - Discuss mailing list archive at Nabble.com.
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 <dgrobani@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-tp4640604p46407... 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
How about LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber)? Daniel -----Original Message----- From: Nicole Engard [mailto:nengard@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 <dgrobani@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
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 <DGrobani@samuelmerritt.edu> wrote:
How about LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber)?
Daniel
-----Original Message----- From: Nicole Engard [mailto:nengard@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 <dgrobani@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
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 'yyyy-mm-dd' AND 'yyyy-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-tp4640604p46446... Sent from the Koha - Discuss mailing list archive at Nabble.com.
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 <dgrobani@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 'yyyy-mm-dd' AND 'yyyy-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-tp4640604p46446... 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
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:nengard@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 <dgrobani@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 'yyyy-mm-dd' AND 'yyyy-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
:) 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 <DGrobani@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:nengard@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 <dgrobani@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 'yyyy-mm-dd' AND 'yyyy-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
participants (3)
-
Daniel Grobani -
Grobani, Daniel
-
Nicole Engard