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@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@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@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@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
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@vt.edu | 540.231.9669 <+15402319669> On Wed, May 27, 2020 at 2:33 PM Elaine Bradtke <eb@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@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@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@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@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
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, heather_hernandez@nps.gov<https://webmail.lmi.net/src/compose.php?send_to=heather_hernandez%40nps.gov> Library catalog: http://keys.bywatersolutions.com/
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@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@vt.edu | 540.231.9669 <+15402319669>
On Wed, May 27, 2020 at 2:33 PM Elaine Bradtke <eb@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@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@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@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@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
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 sudrland@vt.edu | 540.231.9669 <+15402319669> On Wed, May 27, 2020 at 7:05 PM Elaine Bradtke <eb@efdss.org> 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 <sudrland@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@vt.edu | 540.231.9669 <+15402319669>
On Wed, May 27, 2020 at 2:33 PM Elaine Bradtke <eb@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@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@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@efdss.org> a écrit :
What I'd like it to do - give a list of biblio numbers for records
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
with a 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@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
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.
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 sudrland@vt.edu | 540.231.9669 <+15402319669> On Wed, May 27, 2020 at 7:43 PM Mark Alexander <marka@pobox.com> 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 Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
participants (4)
-
Elaine Bradtke -
Hernandez, Heather H -
Mark Alexander -
Michael Sutherland