[Koha] Koha SQL report with MARC field

[Caroline Cyr La Rose]

Hi,

The metadata was moved to it's own table some time ago. You will need to 
add this line somewhere in the SELECT

ExtractValue(biblio_metadata.metadata, '//datafield[@tag="675"]/subfield[@code="3"]') AS "Informacija"

And this line between the last LEFT JOIN and the WHERE

LEFT JOIN biblio_metadata USING (biblionumber)

Like this (I added some carriage returns for legibility)

SELECT
items.dateaccessioned AS Pridėjimas,
items.barcode AS Barkodas,
biblio.author AS Autorius,
biblio.title AS Pavadinimas,
biblioitems.publishercode AS Leidykla,
items.copynumber AS Inventorinis,
biblioitems.isbn AS ISBN,
items.itemnotes AS Uzrasai,
items.itype AS Išteklis,
items.homebranch AS Lokacija,
ExtractValue(biblio_metadata.metadata, '//datafield[@tag="675"]/subfield[@code="3"]') AS "Informacija"
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
LEFT JOIN biblio_metadata USING (biblionumber)
WHERE
items.dateaccessioned BETWEEN <<Between Date (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
ORDER BY items.barcode DESC

(Note: I haven't tested it...)

Good luck!

Caroline

On 2022-07-01 06:49, Tadas G wrote:
> Hello, My Koha 21.05.11 version.
> I use this report to view the inventory. It works well. The question would
> be how would I go about adding a MARC field from Record e.g 675$3
> Maybe someone has an idea how to add it?
>
> SELECT items.dateaccessioned AS Pridėjimas,items.barcode AS
> Barkodas,biblio.author AS Autorius,biblio.title AS
> Pavadinimas,biblioitems.publishercode AS Leidykla,items.copynumber AS
> Inventorinis,biblioitems.isbn AS ISBN,items.itemnotes AS
> Uzrasai,items.itype AS Išteklis,items.homebranch AS Lokacija FROM items
> LEFT JOIN biblioitems ON
> (items.biblioitemnumber=biblioitems.biblioitemnumber)
> LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) WHERE
> items.dateaccessioned BETWEEN
> <<Between Date (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>>
> ORDER BY items.barcode DESC
>
> I think you need to add lines:
> CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,
> '\">', b.title, '</a>' ) AS Title,
>      ExtractValue(bi.metadata,
> '//datafield[@tag="675"]/subfield[@code="3"]') AS "Informacija",
>
> But I don't know how to add them to work
>
>
> Thanks for the help
> _______________________________________________
>
> Koha mailing listhttp://koha-community.org
> Koha at lists.katipo.co.nz
> Unsubscribe:https://lists.katipo.co.nz/mailman/listinfo/koha
-- 
Caroline Cyr La Rose, M.S.I.
Bibliothécaire | Responsable de produit

Tél. : 1-833-465-4276, poste 221
Caroline.Cyr-La-Rose at inLibro.com <mailto:caroline.cyr-la-rose at inLibro.com>

INLiBRO | Spécialistes en technologies documentaires | www.inLibro.com 
<http://www.inLibro.com>