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 <eb@efdss.org> 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 < jonathan.druart@bugs.koha-community.org> 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 <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