[Koha] Report help needed

[Jonathan Druart]

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 at 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 at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha