[Koha] Regarding SQL query for Analytical entries

Suresh Kumar Tejomurtula tejoskumar at gmail.com
Tue Jan 22 01:49:50 NZDT 2019


Hi,

we have analytical entries created for conference papers purchased in our
library. We generated a report using the below query.
But, unable to get the report the way I need. I wanted a separator between
the subject entries that are repeated. But the separator is not getting
added and all the subjects for a particular biblio record are coming
without separator. We are using koha 16.05

SELECT biblioitems.biblionumber,
ExtractValue(marcxml, '//datafield[@tag="088"]/subfield[@code="a"]') AS
"Reportno",
ExtractValue(marcxml, '//datafield[@tag="245"]/subfield[@code="a"]') AS
"title",
ExtractValue(marcxml, '//datafield[@tag="773"]/subfield[@code="a"]') AS
"main_entry",
ExtractValue(marcxml, '//datafield[@tag="773"]/subfield[@code="t"]') AS
"main_entry_title",
ExtractValue(marcxml, '//datafield[@tag="773"]/subfield[@code="o"]') AS
"barcode",
GROUP_CONCAT(ExtractValue(marcxml,
'//datafield[@tag="650"]/subfield[@code="a"]') SEPARATOR '||') AS "Subject"
FROM biblioitems
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
where biblioitems.itemtype='CON'
Group by ExtractValue(marcxml,
'//datafield[@tag="773"]/subfield[@code="t"]')

Could you please let me know where iam going wrong.

-- 
Regards
T. Suresh Kumar


More information about the Koha mailing list