Hi all, I have the following mysql query below to get biblio details based on target audience/course 521$a which has authorized/pre-defined values TARGET. Some records has more than one Target audience hence the ExtractValue( b.marcxml, '//datafield[@tag="521"]/subfield[@code="a"][2]') and so on. The thing is the authorized values made up by the librarians are kind of alien and I would like to show in the report the Descriptions (intranet and OPAC) - these are captured in the authorised_values.lib and and authorised_values.lib_opac columns. Has anybody done the same query? I can't seem to find the right terms maybe someone can show me. TIA and cheers!
SELECT barcode AS ACCESSEDNO, dateaccessioned AS DATERECEIVED, itemcallnumber AS CALLNO, ExtractValue( b.marcxml, '//datafield[@tag="245"]/subfield[@code="c"]') AS AUTHOR, ExtractValue( b.marcxml, '//datafield[@tag="260"]/subfield[@code="c"]') AS YEAR, ExtractValue( b.marcxml, '//datafield[@tag="245"]/subfield[@code="a"]') AS TITLE, ExtractValue( b.marcxml, '//datafield[@tag="245"]/subfield[@code="b"]') AS SUBTITLE, ExtractValue( b.marcxml, '//datafield[@tag="250"]/subfield[@code="a"]') AS ED, ExtractValue( b.marcxml, '//datafield[@tag="260"]/subfield[@code="a"]') AS PLACEOFPUB, ExtractValue( b.marcxml, '//datafield[@tag="260"]/subfield[@code="b"]') AS PUBLISHER, ExtractValue( b.marcxml, '//datafield[@tag="300"]/subfield[@code="a"]') AS PAGES, ExtractValue( b.marcxml, '//datafield[@tag="020"]/subfield[@code="a"]') AS ISBN, ExtractValue( b.marcxml, '//datafield[@tag="521"]/subfield[@code="a"][1]') AS COURSE, ExtractValue( b.marcxml, '//datafield[@tag="521"]/subfield[@code="a"][2]') AS COURSE, ExtractValue( b.marcxml, '//datafield[@tag="521"]/subfield[@code="a"][3]') AS COURSE, ExtractValue( b.marcxml, '//datafield[@tag="521"]/subfield[@code="a"][4]') AS COURSE, ExtractValue( b.marcxml, '//datafield[@tag="521"]/subfield[@code="a"][5]') AS COURSE, ExtractValue( b.marcxml, '//datafield[@tag="521"]/subfield[@code="b"]') AS YEARLEVEL, location AS LOCATION FROM items as a LEFT JOIN biblioitems as b ON ( a.biblioitemnumber = b.biblioitemnumber ) WHERE ExtractValue( b.marcxml, '//datafield[@tag="521"]/subfield[@code="a"]')=<<COURSE|TARGET>> -- View this message in context: http://koha.1045719.n5.nabble.com/mysql-query-xml-extract-value-runtime-parameters-and-lookup-of-authorized-values-tp5906539.html Sent from the Koha-general mailing list archive at Nabble.com. _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha