[Koha] Report help needed
Elaine Bradtke
eb at efdss.org
Thu May 28 11:04:44 NZST 2020
Not condescending at all, the things I don't know about reports would fill
a book.
Interesting report you have there by the way.
The purpose for mine is to double check the position 06 against the 942 $c
because there is a bug 19419 that seems to cause trouble with the 008 if
the Leader 06 is not set correctly for the format (still trying to verify
that's what's going on). I wanted to find any records that didn't have the
appropriate Leader 06 for a given format as specified in the 942.
Elaine Bradtke
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
On Wed, May 27, 2020 at 2:00 PM Michael Sutherland <sudrland at vt.edu> wrote:
> Elaine,
>
> Maybe this would be helpful for those codes -
> https://www.loc.gov/marc/bibliographic/bdleader.html
>
> And, to break it down for those that do not know and are interested, (at
> the risk of condescending, which is not my intention, please, please, I
> hope you do not take it that way)
>
> in the expression - SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS
> "Position06"
>
> you are wanting to extract a piece (substring) from the entire leader
> string - metadata,'//leader' - the 7 is the value that specifies the
> initial position from which the characters can be extracted. The first
> position of the expression starts with 1. But, in MARC the first position
> is 0 (zero). The next value is a positive integer value that specifies the
> ending limit and determines how many characters are going to be
> extracted from the given expression in this case '1', which will give you
> the single letter code in the leader 06, or the seventh position, however
> you choose to look at it.
>
> So, in collaboration with ByWater Solutions, I have co-written the
> following report, which I use to report collection statistics by itemtype
> to ACRL and ARL on our collection sans suppressed records from the OPAC. It
> takes the combination of the leader 06 and 07 to determine itemtype and
> count them.
>
> SELECT
> CASE SUBSTR(metadata,282,2)
> WHEN ' m' THEN 'Book'
> WHEN '2m' THEN 'Book'
> WHEN 'am' THEN 'Book'
> WHEN 'aa' THEN 'Book'
> WHEN 'ac' THEN 'Book'
> WHEN 'ad' THEN 'Book'
> WHEN 'ai' THEN 'Book'
> WHEN 'as' THEN 'Journal'
> WHEN 'cc' THEN 'Music Score'
> WHEN 'cm' THEN 'Music Score'
> WHEN 'dm' THEN 'Music Score'
> WHEN 'ei' THEN 'Map'
> WHEN 'em' THEN 'Map'
> WHEN 'es' THEN 'Map'
> WHEN 'ga' THEN 'Video recording and motion pictures'
> WHEN 'gs' THEN 'Video recording and motion pictures'
> WHEN 'gc' THEN 'Video recording and motion pictures'
> WHEN 'gi' THEN 'Video recording and motion pictures'
> WHEN 'gm' THEN 'Video recording and motion pictures'
> WHEN 'ic' THEN 'Non-music sound recording'
> WHEN 'im' THEN 'Non-music sound recording'
> WHEN 'is' THEN 'Non-music sound recording'
> WHEN 'jm' THEN 'Music sound recording'
> WHEN 'jc' THEN 'Music sound recording'
> WHEN 'ji' THEN 'Music sound recording'
> WHEN 'kc' THEN '2D image'
> WHEN 'km' THEN '2D image'
> WHEN 'ma' THEN 'Computer file'
> WHEN 'mi' THEN 'Computer file'
> WHEN 'mm' THEN 'Computer file'
> WHEN 'ms' THEN 'Computer file'
> WHEN 'oc' THEN 'Kit'
> WHEN 'om' THEN 'Kit'
> WHEN 'pc' THEN 'Mixed material'
> WHEN 'pm' THEN 'Mixed material'
> WHEN 'rc' THEN '3D Object'
> WHEN 'rm' THEN '3D Object'
> WHEN 'tc' THEN 'Manuscript'
> WHEN 'tm' THEN 'Manuscript'
> ELSE 'unknown' END AS Type,
> COUNT(DISTINCT biblionumber) AS Titles,
> COUNT(itemnumber) AS Volumes
> FROM biblio_metadata
> LEFT JOIN items USING (biblionumber)
> WHERE
> ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="n"]')
> != 1
> GROUP BY Type
> WITH ROLLUP
>
> which generates the following
>
> TypeTitlesVolumes
> 2D image 65 92
> 3D Object 21 21
> Book 1193934 1329489
> Computer file 630 1555
> Journal 38568 542424
> Kit 41 66
> Manuscript 5461 14120
> Map 4754 5770
> Mixed material 253 2517
> Music Score 11948 15757
> Music sound recording 9193 9766
> Non-music sound recording 613 818
> unknown 1018 214
> Video recording and motion pictures 15246 17090
> 1281745 1939699
> titles is the number of bib records and volumes are the number of items
> attached to a bib record.
>
> Best regards,
> Michael
> _________________________________________
> *Michael J. Sutherland*
> University Libraries
> Virginia Tech
> sudrland at vt.edu | 540.231.9669 <+15402319669>
>
>
>
> On Wed, May 27, 2020 at 2:33 PM Elaine Bradtke <eb at efdss.org> wrote:
>
>> Yes, I think I've got it now. Thanks for your help. The hard part was
>> figuring out where to find the itemtype in the bib record.
>> SELECT biblio.biblionumber,
>> SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS "Position06"
>> FROM biblio
>> LEFT JOIN biblio_metadata USING (biblionumber)
>> WHERE ExtractValue( metadata,
>> '//datafield[@tag="942"]/subfield[@code="c"]'
>> ) = <<Item Type|itemtypes>>
>> Elaine Bradtke
>> 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
>>
>>
>> On Wed, May 27, 2020 at 10:50 AM Elaine Bradtke <eb at efdss.org> wrote:
>>
>> > It doesn't throw up error messages, but what I really need is a list of
>> > biblio numbers, with the info in position 6 of the leader and the
>> itemtype
>> > from the bib record, but also choose a specific itemtype (not books).
>> I'm
>> > going to try to mash up what you did with another report and see what
>> > happens.
>> > Elaine Bradtke
>> > 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
>> >
>> >
>> > On Wed, May 27, 2020 at 1:51 AM Jonathan Druart <
>> > jonathan.druart at bugs.koha-community.org> wrote:
>> >
>> >> Hi Elaine,
>> >>
>> >> The biblioitems.marcxml field has been moved to
>> biblio_metadata.metadata.
>> >>
>> >> Does this query work as you want:
>> >> SELECT CONCAT('<a
>> >> href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=
>> >> ',b.biblionumber,'\">',b.biblionumber,'</a>')
>> >> AS biblionumber
>> >> FROM biblio b
>> >> LEFT JOIN biblioitems bi ON b.biblionumber= bi.biblionumber
>> >> LEFT JOIN biblio_metadata bm ON b.biblionumber=bm.biblionumber
>> >> WHERE bi.itemtype = 'BK' AND SUBSTR(bm.metadata,INSTR(bm.metadata,
>> >> "<leader>")+8+6,1) = 'a';
>> >>
>> >> Regards,
>> >> Jonathan
>> >>
>> >> Le mar. 26 mai 2020 à 20:35, Elaine Bradtke <eb at efdss.org> a écrit :
>> >> >
>> >> > What I'd like it to do - give a list of biblio numbers for records
>> with
>> >> a
>> >> > particular itemtype, and the information found in the Leader *06 -
>> Type
>> >> of
>> >> > record *field.
>> >> > I found the following in the reports library as something that could
>> be
>> >> > modified to fit my needs. But it doesn't work I get the following
>> >> > message: Unknown column 'marcxml' in 'field list'
>> >> > Please check the log for further details.
>> >> > Also, I don't understand how to indicate Leader position 06
>> >> >
>> >> > SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/
>> >> detail.pl?biblionumber=
>> >> > ',biblionumber,'\">',biblionumber,'</a>')
>> >> >
>> >> > AS biblionumber
>> >> >
>> >> > FROM biblioitems,
>> >> >
>> >> > (SELECT biblioitemnumber, SUBSTR(marcxml,INSTR(marcxml,
>> >> "<leader>")+8+6,1)
>> >> >
>> >> > AS leader6 FROM biblioitems)
>> >> >
>> >> > AS leaders
>> >> >
>> >> > WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND
>> >> > leaders.leader6 = 'a'
>> >> >
>> >> > AND itemtype = <<Item Type|itemtypes>>
>> >> >
>> >> > Any help would be appreciated
>> >> >
>> >> > stay safe,
>> >> >
>> >> > Elaine Bradtke
>> >> > 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
>> >> > _______________________________________________
>> >> >
>> >> > Koha mailing list http://koha-community.org
>> >> > Koha at lists.katipo.co.nz
>> >> > Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>> >>
>> >
>> _______________________________________________
>>
>> Koha mailing list http://koha-community.org
>> Koha at lists.katipo.co.nz
>> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
More information about the Koha
mailing list