[Koha] Report help needed

[Elaine Bradtke]

Thanks Heather, and double thanks for the hint about commas.  It only takes
one little missing piece of the puzzle for the whole thing to fail.
I could really use something like SQL queries for Dummies.
Elaine

On Mon, Nov 17, 2014 at 11:47 PM, Heather Braum (NEKLS) <hbraum at nekls.org>
wrote:

> Elaine, try the below. It looks like you were missing some commas -- that
> was what was causing the syntax errors; you can then name the columns like
> I did below or however you want using as ___ single-word (as 780t) or as
> "two or more words" (as 780w field_).
>
> Make sure every column of data in the select statement has a comma after
> it, except for the last on before the from statement.
>
> Hope that helps!  -- it ran on my system.
>
> SELECT
> biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype,
>
> ExtractValue(biblioitems.marcxml,
> '//datafield[@tag="780"]/subfield[@code="t"]') as 780t,
> ExtractValue(biblioitems.marcxml,
> '//datafield[@tag="780"]/subfield[@code="w"]') as "780w field",
> ExtractValue(biblioitems.marcxml,
> '//datafield[@tag="866"]/subfield[@code="a"]') as 866a,
> ExtractValue(biblioitems.marcxml,
> '//datafield[@tag="866"]/subfield[@code="z"]') as 866z
> FROM biblioitems LEFT JOIN biblio USING (biblionumber)
> WHERE biblioitems.itemtype='CR'
>
> Heather Braum
> NExpress Coordinator
> Resource Sharing Librarian
> Northeast Kansas Library System
> hbraum at nekls.org
>
> "The illiterate of the 21st century will not be those who cannot read
> and write, but those who cannot learn, unlearn, and relearn." ~Alvin
> Toffler, *Rethinking the Future*
>
>
>
>
> On Mon, Nov 17, 2014 at 5:38 PM, Elaine Bradtke <eb at efdss.org> wrote:
>
>> I have a request for a report that is beyond my paltry abilities and that
>> of the report builder.
>>
>>
>> I have this, and it works (thanks Liz!):
>> SELECT
>>
>>  biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype
>> FROM biblioitems LEFT JOIN biblio USING (biblionumber)   WHERE
>> biblioitems.itemtype='CR'
>>
>> But I need to add in more columns that involve the MARC XML
>>
>> Specifically
>> 780 t (previous title)
>> 780 w (previous control no.)
>> 866 a (holdings)
>> 866 z (note)
>> 867 e (source)
>>
>> When I try to add in:
>>   Extract value ExtractValue(marcxml,
>> '//datafield[@tag="780"]/subfield[@code="t"]')
>> I manage to break the above query and it complains of syntax errors.
>> Does it matter where this Extract phrase comes in?
>> How do you handle multiple subfields (780 t and w for instance)
>> For that matter, how do you handle multiple fields?  And how can I label
>> the columns
>>
>> The following (and a few variations on the theme) did not work:
>>
>> SELECT
>>
>>  biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype
>> ExtractValue(marcxml, '//datafield[@tag="780"]/subfield[@code="t"]')
>> ExtractValue(marcxml, '//datafield[@tag="780"]/subfield[@code="w"]')
>> ExtractValue(marcxml, '//datafield[@tag="866"]/subfield[@code="a"]')
>> ExtractValue(marcxml, '//datafield[@tag="866"]/subfield[@code="z"]')
>> FROM biblioitems LEFT JOIN biblio USING (biblionumber)   WHERE
>> biblioitems.itemtype='CR'
>>
>>
>> As always I am very, very grateful for whatever help you can give.
>> Thanks
>> --
>> Elaine Bradtke
>> Data Wrangler
>> VWML
>> English Folk Dance and Song Society | http://www.efdss.org
>> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
>> Tel    +44 (0) 20 7485 2206 (This number is for the English Folk Dance
>> and
>> Song Society in London, England. If you wish to phone me personally, send
>> an e-mail first. I work off site)
>> --------------------------------------------------------------------------
>> Registered Company No. 297142
>> Charity Registered in England and Wales No. 305999
>>
>> ---------------------------------------------------------------------------
>> "Writing about music is like dancing about architecture"
>> --Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
>> _______________________________________________
>> Koha mailing list  http://koha-community.org
>> Koha at lists.katipo.co.nz
>> http://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
>


-- 
Elaine Bradtke
Data Wrangler
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel    +44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--------------------------------------------------------------------------
Registered Company No. 297142
Charity Registered in England and Wales No. 305999
---------------------------------------------------------------------------
"Writing about music is like dancing about architecture"
--Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)