[Koha] Report help needed

[Elaine Bradtke]

Thanks Bernardo, you have come to my rescue again!  Someday I will take the
time to actually learn how to do this properly. It worked perfectly.
Elaine
On Tue, Jun 18, 2013 at 1:17 AM, Bernardo Gonzalez Kriegel <
bgkriegel at gmail.com> wrote:

> Elaine,
> you can't have DISTINCT heading and biblionumber at the same time.
>
> A small modification will give you what you are looking
>
> SELECT biblionumber, ExtractValue(marcxml,
>
> '//datafield[@tag="650"]/subfield[@code="a"]') AS heading
>  FROM biblioitems
>  WHERE
>   length(ExtractValue(marcxml,
> '//datafield[@tag="650"]/subfield[@code="a"]')) != 0 AND
>   length(ExtractValue(marcxml,
> '//datafield[@tag="650"]/subfield[@code="9"]')) = 0
> ORDER BY heading
>
> Regards,
> Bernardo
>
> --
> Bernardo Gonzalez Kriegel
> bgkriegel at gmail.com
>
>
> On Mon, Jun 17, 2013 at 7:56 PM, Elaine Bradtke <eb at efdss.org> wrote:
>
>> I'm tracking down some unauthorized subject headings and have run into a
>> problem with a term  that is far too common in our catalogue to be useful
>> in any kind of search.
>>
>>  I'm trying to devise a report that will look for 650 fields with a
>> subfield a, but without a subfield 9  -  But the second half of what I
>> need
>> - a report of not just the rogue term, but also the biblio number of the
>> record that it is in. I have tried several things but cannot get it to
>> work.
>>
>> The functional part of my report is here:
>>
>> SELECT DISTINCT heading FROM (
>>  SELECT ExtractValue(marcxml,
>> '//datafield[@tag="650"]/subfield[@code="a"]') AS heading
>>  FROM biblioitems
>>  WHERE
>>   length(ExtractValue(marcxml,
>> '//datafield[@tag="650"]/subfield[@code="a"]')) != 0 AND
>>   length(ExtractValue(marcxml,
>> '//datafield[@tag="650"]/subfield[@code="9"]')) = 0
>> ) AS heads
>> ORDER BY heading
>>
>>
>> This gives a list of headings in 650 fields that are not linked to
>> authorities
>> Can anyone help me add in the query that will return the biblionumber too?
>> Thanks in advance
>> --
>> 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)