Report filtered by item type fails
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? -- 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)
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
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@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@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)
Give this a go: SELECT biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype FROM biblioitems LEFT JOIN items USING (biblionumber) LEFT JOIN biblio USING (biblionumber) WHERE biblioitems.itemtype='CR' I think your problem is that you are linking your tables using biblioitemnumber instead of biblionumber. I hope this helps, let us know if it doesn't! Cheers, Liz On 13/11/14 19:25, Elaine Bradtke wrote:
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@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@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
-- -- 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
participants (2)
-
Elaine Bradtke -
Liz Rea