Report help needed

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Report help needed

Elaine Bradtke-3
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
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Report help needed

Hernandez, Heather H
Hi, Elaine!

I am the newbiest of SQL report newbies, but I tend to "speak MARC21," and found a different report on the Koha Wiki that I was able to tweak and it seems to work in our catalog to retrieve all bibs with a Leader position 06 value of "a:"

SELECT biblionumber, title, author, ExtractValue(metadata,'//leader') AS "Leader Field", SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS "Position05"
FROM biblio
LEFT JOIN biblio_metadata USING (biblionumber)
WHERE SUBSTRING(ExtractValue(metadata,'//leader'),7,1) = 'a'

If you change that final little 'a' to, e.g., 'e' you get all the cartographic resources.  The "7" somehow gets you position 6, and "6" would get you position 5 in the Leader...somehow.

Cheerio!
h2

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Ms. Heather Hernandez (she, her, hers)
Technical Services Librarian
San Francisco Maritime National Historical Park Research Center
2 Marina Blvd., Bldg. E, 3rd floor, San Francisco, CA  94123-1284
415-561-7032, [hidden email]<https://webmail.lmi.net/src/compose.php?send_to=heather_hernandez%40nps.gov>
Library catalog: http://keys.bywatersolutions.com/
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: Report help needed

Jonathan Druart
In reply to this post by Elaine Bradtke-3
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 <[hidden email]> 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
> [hidden email]
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: Report help needed

Elaine Bradtke-3
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 <[hidden email]> 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 <
> [hidden email]> 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 <[hidden email]> 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
>> > [hidden email]
>> > Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: Report help needed

Michael Sutherland
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
[hidden email] | 540.231.9669 <+15402319669>



On Wed, May 27, 2020 at 2:33 PM Elaine Bradtke <[hidden email]> 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 <[hidden email]> 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 <
> > [hidden email]> 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 <[hidden email]> 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
> >> > [hidden email]
> >> > Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
> >>
> >
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> [hidden email]
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Report help needed

Hernandez, Heather H
Hi, Michael--

That's fantastic!!  Thank you so much for sharing it!

Best,
h2

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Ms. Heather Hernandez (she, her, hers)
Technical Services Librarian
San Francisco Maritime National Historical Park Research Center
2 Marina Blvd., Bldg. E, 3rd floor, San Francisco, CA  94123-1284
415-561-7032, [hidden email]<https://webmail.lmi.net/src/compose.php?send_to=heather_hernandez%40nps.gov>
Library catalog: http://keys.bywatersolutions.com/
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: Report help needed

Elaine Bradtke-3
In reply to this post by Michael Sutherland
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 <[hidden email]> 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
> [hidden email] | 540.231.9669 <+15402319669>
>
>
>
> On Wed, May 27, 2020 at 2:33 PM Elaine Bradtke <[hidden email]> 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 <[hidden email]> 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 <
>> > [hidden email]> 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 <[hidden email]> 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
>> >> > [hidden email]
>> >> > Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>> >>
>> >
>> _______________________________________________
>>
>> Koha mailing list  http://koha-community.org
>> [hidden email]
>> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: Report help needed

Michael Sutherland
Thank you. I'm unfamiliar with that particular bug and will check it out
for our records.

Best,
Michael
_________________________________________
*Michael J. Sutherland*
University Libraries
Virginia Tech
[hidden email] | 540.231.9669 <+15402319669>



On Wed, May 27, 2020 at 7:05 PM Elaine Bradtke <[hidden email]> wrote:

> 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 <[hidden email]>
> 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
> > [hidden email] | 540.231.9669 <+15402319669>
> >
> >
> >
> > On Wed, May 27, 2020 at 2:33 PM Elaine Bradtke <[hidden email]> 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 <[hidden email]> 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 <
> >> > [hidden email]> 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 <[hidden email]> 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
> >> >> > [hidden email]
> >> >> > Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
> >> >>
> >> >
> >> _______________________________________________
> >>
> >> Koha mailing list  http://koha-community.org
> >> [hidden email]
> >> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
> >>
> >
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> [hidden email]
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: Report help needed

Mark Alexander
In reply to this post by Michael Sutherland
Excerpts from Michael Sutherland's message of 2020-05-27 16:59:50 -0400:
> CASE SUBSTR(metadata,282,2)

I tried this myself, and it does seem to work.  But I worry that
the 282 value might not be correct in the future, should the XML
format of the metadata change even slightly.  In particular,
the XML header has a field called xsi:schemaLocation that contains
a URL that might change someday.  I could very wrong about this,
though; perhaps the format really is stable.

This does the same job and seems less obscure (to me, anyway):

  substring(ExtractValue(metadata, '//leader'), 7, 2)

But perhaps it's not as fast?  I'm not a MySQL expert by any means.
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: Report help needed

Michael Sutherland
I do not know about the speed and I'm not an expert either.  The beauty of
the reports is that we can borrow them from others, save them and change
them as needed, right? It is the same as the former query
'ExtractValue(marcxml'
changing to ExtractValue(metadata'.  All of the reports in the reports
library <https://wiki.koha-community.org/wiki/SQL_Reports_Library> should
be and look to be updated for the new versions to help new and current
users.  I remember trying a number of reports in the library that did not
work for me.

Best,
M.
_________________________________________
*Michael J. Sutherland*
University Libraries
Virginia Tech
[hidden email] | 540.231.9669 <+15402319669>



On Wed, May 27, 2020 at 7:43 PM Mark Alexander <[hidden email]> wrote:

> Excerpts from Michael Sutherland's message of 2020-05-27 16:59:50 -0400:
> > CASE SUBSTR(metadata,282,2)
>
> I tried this myself, and it does seem to work.  But I worry that
> the 282 value might not be correct in the future, should the XML
> format of the metadata change even slightly.  In particular,
> the XML header has a field called xsi:schemaLocation that contains
> a URL that might change someday.  I could very wrong about this,
> though; perhaps the format really is stable.
>
> This does the same job and seems less obscure (to me, anyway):
>
>   substring(ExtractValue(metadata, '//leader'), 7, 2)
>
> But perhaps it's not as fast?  I'm not a MySQL expert by any means.
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> [hidden email]
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha