[Koha] Report filtered by item type fails

[Elaine Bradtke]

What happens if you try SELECT itemtype, biblionumber FROM biblioitems
WHERE itemtype = 'CR' - are your results any different?

I get a list of biblionumbers with itemtype CR - the correct number of
them. (848)

But when I run this:

SELECT
 biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,itemtype
FROM items LEFT JOIN biblioitems on
(items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on
(biblioitems.biblionumber=biblio.biblionumber)   WHERE itemtype='CR'

I only get six - all of these records have attached items. Probably the
only six CR records with attached items.
I get the same six  records when I use biblioitems.itemtype instead:

SELECT
 biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype
FROM items LEFT JOIN biblioitems on
(items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on
(biblioitems.biblionumber=biblio.biblionumber)   WHERE
biblioitems.itemtype='CR'


On Thu, Nov 13, 2014 at 2:44 AM, Liz Rea <liz at catalyst.net.nz> wrote:

> Hi Elaine,
>
> What happens if you try SELECT itemtype, biblionumber FROM biblioitems
> WHERE itemtype = 'CR' - are your results any different?
>
> Cheers,
> Liz
> On 13/11/14 14:43, Elaine Bradtke wrote:
> > Koha 3.16
> > According to my statistical report, we have 848 biblios that are
> >  continuing resources (BIBITEMTYPE CR)
> > But when I try to build a report that limits the output to these records
> I
> > get only a handful of records (these uniquely have items attached, 99% of
> > our CR records do not, so this information usually only appears in the
> > 942c).
> >
> > If I run the following report it doesn't list any entries with CR.
> > SELECT biblionumber, ExtractValue(marcxml,
> > '//datafield[@tag="942"]/subfield[@code="c"]') AS comp FROM
> > biblioitems
> >
> > But I can search and retrieve individual records, and they do indeed have
> > CR in the 942c.
> >
> > When I run an advanced search and limit it to this item type, the search
> > results are exactly what I expect to see.
> >
> > I can't figure out how it will count them in one instance, but not list
> > them in another;  find them in the advanced search, but not when I query
> > the database with a report. . .
> >
> > We've just moved our server over to a new virtual machine and a different
> > version of Linux. . . Is the above a symptom of indexing problems or some
> > other technical glitch?
> >
> >
>
> --
> --
> Liz Rea
> Catalyst.Net Limited
> Level 6, Catalyst House,
> 150 Willis Street, Wellington.
> P.O Box 11053, Manners Street,
> Wellington 6142
>
> GPG: B149 A443 6B01 7386 C2C7 F481 B6c2 A49D 3726 38B7
>
> _______________________________________________
> 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)