[Koha] How to include 952$x in a report

[Stefano Bargioni]

Paul, seems you are using two queries (";" delimits queries).
If I'm not wrong, you can use

SELECT items.biblionumber,items.barcode,items.dateaccessioned,
ExtractValue(biblioitems.marcxml,'//datafield[@tag="952"]/subfield[@code="x"]') AS ITEM
FROM biblioitems
LEFT JOIN items ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
ORDER BY items.biblionumber ASC

It works on my Koha 3.2.7 (Mysql 5.1.49).
Also see @code="x", instead of @code>="x". Using ">=x" will extract subfields x,y,z...
HTH. Stefano

> At 10:24 AM 2/21/2012 +1300, Chris Cormack wrote:
>> On 21 February 2012 10:17, Paul <paul.a at aandc.org> wrote:
>>> Help requested please from an expert in MySQL reports. ? The code below 
>> works
>>> perfectly, but I want to add 952$x (a text string for the condition of our
>>> holdings as in "good/fair" or "fine tpb") to the first line.
>>> 
>>> If I add items.more_subfields_xml (which includes 952$x) I get a 
>> plethora of
>>> formatted XML that is unsuitable for copying straight into a spreadsheet.
> [snip first trial code]
> 
>> http://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC
> 
> Thanks Chris, I had tried something along those lines, and have now tried 
> again. Started with deleting the:
> 
>     WHERE biblionumber=14),
> 
> per mysql manual "The statement selects all rows if there is no WHERE 
> clause", but still get error "can't use undefined value as an ARRAY 
> reference" at guided_reports.pl line 618. After multiple trial/error 
> changes to punctuation, etc, here's what I have, still non-functional:
> 
> SELECT items.biblionumber,items.barcode,items.dateaccessioned
> FROM items;
> 
> SELECT ExtractValue(marcxml,'//datafield[@tag="952"]/subfield[@code>="x"]') 
> AS ITEM
> FROM biblioitems
> 
> LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
> LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
> 
> ORDER BY items.biblionumber asc
> 
> Removing the "SELECT ExtractValue ..." and preceding semi-colon works fine, 
> I'm just totally flummoxed on getting the 952$x part to function.  Is 
> another LEFT JOIN required?
> 
> Could you possibly suggest the correct syntax?  Many thanks.
> 
> Paul