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('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber= ',biblionumber,'\">',biblionumber,'</a>') AS biblionumber FROM biblioitems, (SELECT biblioitemnumber, SUBSTR(marcxml,INSTR(marcxml, "<leader>")+8+6,1) AS leader6 FROM biblioitems) AS leaders WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND leaders.leader6 = 'a' AND itemtype = <<Item Type|itemtypes>> 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
Hi, Elaine! I am the newbiest of SQL report newbies, but I tend to "speak MARC21," and found a different report on the Koha Wiki that I was able to tweak and it seems to work in our catalog to retrieve all bibs with a Leader position 06 value of "a:" SELECT biblionumber, title, author, ExtractValue(metadata,'//leader') AS "Leader Field", SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS "Position05" FROM biblio LEFT JOIN biblio_metadata USING (biblionumber) WHERE SUBSTRING(ExtractValue(metadata,'//leader'),7,1) = 'a' If you change that final little 'a' to, e.g., 'e' you get all the cartographic resources. The "7" somehow gets you position 6, and "6" would get you position 5 in the Leader...somehow. Cheerio! h2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Ms. Heather Hernandez (she, her, hers) Technical Services Librarian San Francisco Maritime National Historical Park Research Center 2 Marina Blvd., Bldg. E, 3rd floor, San Francisco, CA 94123-1284 415-561-7032, heather_hernandez@nps.gov<https://webmail.lmi.net/src/compose.php?send_to=heather_hernandez%40nps.gov> Library catalog: http://keys.bywatersolutions.com/
Hi Elaine, The biblioitems.marcxml field has been moved to biblio_metadata.metadata. Does this query work as you want: SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'\">',b.biblionumber,'</a>') 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, "<leader>")+8+6,1) = 'a'; Regards, Jonathan Le mar. 26 mai 2020 à 20:35, Elaine Bradtke <eb@efdss.org> 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('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber= ',biblionumber,'\">',biblionumber,'</a>')
AS biblionumber
FROM biblioitems,
(SELECT biblioitemnumber, SUBSTR(marcxml,INSTR(marcxml, "<leader>")+8+6,1)
AS leader6 FROM biblioitems)
AS leaders
WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND leaders.leader6 = 'a'
AND itemtype = <<Item Type|itemtypes>>
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
participants (3)
-
Elaine Bradtke -
Hernandez, Heather H -
Jonathan Druart