[Koha] mysql query xml extract value, runtime parameters and lookup of authorized values

[schnydszch]

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.