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"]' ) = <<Item Type|itemtypes>> 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 <[email protected]> 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 < > [email protected]> wrote: > >> 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 <[email protected]> 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 >> > [email protected] >> > Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha >> > _______________________________________________ Koha mailing list http://koha-community.org [email protected] Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
