[Koha] Report help needed

[Elaine Bradtke]

Forwarding this to the list.  Joel's suggestion fixed the problems.
Elaine
VWML <https://vwml.org>



-- Forwarded message -
From: Coehoorn, Joel 
Date: Tue, May 10, 2022 at 11:20 AM
Subject: Re: [Koha] Report help needed
To: Elaine Bradtke 


It could just be my email client, but I see line endings in the wrong
place. It works on my system (in that at least I see data) if I don't
change any code other than fixing formatting and line endings:

SELECT authid, datecreated, authtypecode,
concat(ExtractValue(`marcxml`,'//datafield[@tag="100"]/*'), --
PERSO_NAME
ExtractValue(`marcxml`,'//datafield[@tag="110"]/*'), -- CORPO_NAME
ExtractValue(`marcxml`,'//datafield[@tag="111"]/*'), -- MEETI_NAME
ExtractValue(`marcxml`,'//datafield[@tag="130"]/*'), -- UNIF_TITLE
ExtractValue(`marcxml`,'//datafield[@tag="148"]/*'), -- CHRON_TERM
ExtractValue(`marcxml`,'//datafield[@tag="150"]/*'), -- TOPIC_TERM
ExtractValue(`marcxml`,'//datafield[@tag="151"]/*'), -- GEOGR_NAME
ExtractValue(`marcxml`,'//datafield[@tag="155"]/*')  -- GENRE/FORM
) main_heading
FROM `auth_header`
WHERE datecreated BETWEEN <<(from -mm-dd)>> AND <<(to -mm-dd)>>
ORDER BY datecreated, authtypecode, main_heading


Note that MySql is picky about double-dash (--) comments, and requires
whitespace after the comment characters.

Joel Coehoorn
Director of Information Technology
York College of Nebraska


On Tue, May 10, 2022 at 12:42 PM Elaine Bradtke  wrote:

> I imported the following report from Mana, but it doesn't work as is.  I
> tried changing `marcxml` to `metadata` but it still didn't work. The error
> message (You have an error in your SQL syntax; check the manual that
> corresponds to your MariaDB server version for the right syntax to use near
> '' at line 1) isn't specific enough for my limited abilities.  I'd be
> grateful for your help.
>
> Here's the original:
> SELECT authid, datecreated, authtypecode,
> concat(ExtractValue(`marcxml`,'//datafield[@tag="100"]/*'), --
> PERSO_NAMEExtractValue(`marcxml`,'//datafield[@tag="110"]/*'), --
> CORPO_NAMEExtractValue(`marcxml`,'//datafield[@tag="111"]/*'), --
> MEETI_NAMEExtractValue(`marcxml`,'//datafield[@tag="130"]/*'), --
> UNIF_TITLEExtractValue(`marcxml`,'//datafield[@tag="148"]/*'), --
> CHRON_TERMExtractValue(`marcxml`,'//datafield[@tag="150"]/*'), --
> TOPIC_TERMExtractValue(`marcxml`,'//datafield[@tag="151"]/*'), --
> GEOGR_NAMEExtractValue(`marcxml`,'//datafield[@tag="155"]/*')  --
> GENRE/FORM) main_headingFROM `auth_header`WHERE datecreated BETWEEN <<(from
> -mm-dd)>> AND <<(to -mm-dd)>>ORDER BY datecreated, authtypecode,
> main_heading
>
> Elaine Bradtke
> VWML <https://www.vwml.org>
> English Folk Dance and Song Society <https://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

Re: [Koha] Report help needed

[Alvaro Cornejo]

Hi Elaine,

I guess there is a space missing before FROM in ...main_headingFROM
`auth_header`...

And also a colon after GENRE/FORM)

Else you can try your SQL by parts and start by the basics. Something like

SELECT authid, datecreated,
authtypecode, concat(ExtractValue(`marcxml`,'//datafield[@tag="100"]/*'),
--), main_heading FROM `auth_header`WHERE datecreated SetASpecificDateHere

Regards

Alvaro
||
 Stay safe / Cuídate/  Reste sécurisé
*7* Switch off as you go / Apaga lo que no usas /  Débranchez au fur et à
mesure.
 *q *Recycle always / Recicla siempre / Recyclez toujours
 P Print only if absolutely necessary / Imprime solo si es necesario /
Imprimez seulement si nécessaire


Le mar. 10 mai 2022 à 12:43, Elaine Bradtke  a écrit :

> I imported the following report from Mana, but it doesn't work as is.  I
> tried changing `marcxml` to `metadata` but it still didn't work. The error
> message (You have an error in your SQL syntax; check the manual that
> corresponds to your MariaDB server version for the right syntax to use near
> '' at line 1) isn't specific enough for my limited abilities.  I'd be
> grateful for your help.
>
> Here's the original:
> SELECT authid, datecreated, authtypecode,
> concat(ExtractValue(`marcxml`,'//datafield[@tag="100"]/*'), --
> PERSO_NAMEExtractValue(`marcxml`,'//datafield[@tag="110"]/*'), --
> CORPO_NAMEExtractValue(`marcxml`,'//datafield[@tag="111"]/*'), --
> MEETI_NAMEExtractValue(`marcxml`,'//datafield[@tag="130"]/*'), --
> UNIF_TITLEExtractValue(`marcxml`,'//datafield[@tag="148"]/*'), --
> CHRON_TERMExtractValue(`marcxml`,'//datafield[@tag="150"]/*'), --
> TOPIC_TERMExtractValue(`marcxml`,'//datafield[@tag="151"]/*'), --
> GEOGR_NAMEExtractValue(`marcxml`,'//datafield[@tag="155"]/*')  --
> GENRE/FORM) main_headingFROM `auth_header`WHERE datecreated BETWEEN <<(from
> -mm-dd)>> AND <<(to -mm-dd)>>ORDER BY datecreated, authtypecode,
> main_heading
>
> Elaine Bradtke
> VWML 
> English Folk Dance and Song Society 
> 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

[Koha] Report help needed

[Elaine Bradtke]

I imported the following report from Mana, but it doesn't work as is.  I
tried changing `marcxml` to `metadata` but it still didn't work. The error
message (You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use near
'' at line 1) isn't specific enough for my limited abilities.  I'd be
grateful for your help.

Here's the original:
SELECT authid, datecreated, authtypecode,
concat(ExtractValue(`marcxml`,'//datafield[@tag="100"]/*'), --
PERSO_NAMEExtractValue(`marcxml`,'//datafield[@tag="110"]/*'), --
CORPO_NAMEExtractValue(`marcxml`,'//datafield[@tag="111"]/*'), --
MEETI_NAMEExtractValue(`marcxml`,'//datafield[@tag="130"]/*'), --
UNIF_TITLEExtractValue(`marcxml`,'//datafield[@tag="148"]/*'), --
CHRON_TERMExtractValue(`marcxml`,'//datafield[@tag="150"]/*'), --
TOPIC_TERMExtractValue(`marcxml`,'//datafield[@tag="151"]/*'), --
GEOGR_NAMEExtractValue(`marcxml`,'//datafield[@tag="155"]/*')  --
GENRE/FORM) main_headingFROM `auth_header`WHERE datecreated BETWEEN <<(from
-mm-dd)>> AND <<(to -mm-dd)>>ORDER BY datecreated, authtypecode,
main_heading

Elaine Bradtke
VWML 
English Folk Dance and Song Society 
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

Re: [Koha] Report help needed

[Elaine Bradtke]

Removing the comma fixed it!
It was built off another report with a lot more fields.  I'm a proud
graduate of the cut and paste school of report writing. ;-)
Elaine
VWML <https://vwml.org>



On Tue, Jan 5, 2021 at 11:38 AM Lisette Scheer 
wrote:

> Elaine,
> There is a comma after AS sub3 that is not needed.
> I was able to get the report to run on my system after taking out the ,
> Lisette
> -Original Message-
> From: Koha [mailto:koha-boun...@lists.katipo.co.nz] On Behalf Of Elaine
> Bradtke
> Sent: Tuesday, January 5, 2021 11:10 AM
> To: koha 
> Subject: [Koha] Report help needed
>
> I'm trying to find records lacking 300 fields. The following keeps
> failing.  Any help would be appreciated. Thanks!
>
> SELECT CONCAT(' ',biblionumber,'\">',biblionumber,'')
> AS bibnumber
> FROM
> (SELECT biblionumber,
> ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="a"]') AS
> suba,
> ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="b"]') AS
> subb,
> ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="3"]') AS
> sub3, FROM biblio_metadata) AS subfields WHERE suba = ""
> AND subb = ""
> AND sub3 = ""
>
> Elaine Bradtke
> VWML <https://www.vwml.org>
> Our income is severely diminished in these exceptionally uncertain times.
> Please help us to survive beyond this national crisis. Donate now <
> https://www.efdss.org/support-us>.
> English Folk Dance and Song Society <https://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

Re: [Koha] Report help needed

[Lisette Scheer]

Elaine, 
There is a comma after AS sub3 that is not needed. 
I was able to get the report to run on my system after taking out the ,
Lisette
-Original Message-
From: Koha [mailto:koha-boun...@lists.katipo.co.nz] On Behalf Of Elaine Bradtke
Sent: Tuesday, January 5, 2021 11:10 AM
To: koha 
Subject: [Koha] Report help needed

I'm trying to find records lacking 300 fields. The following keeps failing.  
Any help would be appreciated. Thanks!

SELECT CONCAT('',biblionumber,'')
AS bibnumber
FROM
(SELECT biblionumber,
ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="a"]') AS suba,
ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="b"]') AS subb,
ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="3"]') AS sub3, 
FROM biblio_metadata) AS subfields WHERE suba = ""
AND subb = ""
AND sub3 = ""

Elaine Bradtke
VWML <https://www.vwml.org>
Our income is severely diminished in these exceptionally uncertain times.
Please help us to survive beyond this national crisis. Donate now 
<https://www.efdss.org/support-us>.
English Folk Dance and Song Society <https://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

Re: [Koha] Report help needed

[Katrin Fischer]

Hi Elaine,

I notice that you compare to >= so it will include other subfields
bigger than... . Can you try changing this to read just =?

Katrin

On 05.01.21 20:10, Elaine Bradtke wrote:

I'm trying to find records lacking 300 fields. The following keeps
failing.  Any help would be appreciated. Thanks!

SELECT CONCAT('',biblionumber,'')
AS bibnumber
FROM
(SELECT biblionumber,
ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="a"]') AS
suba,
ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="b"]') AS
subb,
ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="3"]') AS
sub3,
FROM biblio_metadata) AS subfields
WHERE suba = ""
AND subb = ""
AND sub3 = ""

Elaine Bradtke
VWML 
Our income is severely diminished in these exceptionally uncertain times.
Please help us to survive beyond this national crisis. Donate now
.
English Folk Dance and Song Society 
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

[Koha] Report help needed

[Elaine Bradtke]

I'm trying to find records lacking 300 fields. The following keeps
failing.  Any help would be appreciated. Thanks!

SELECT CONCAT('',biblionumber,'')
AS bibnumber
FROM
(SELECT biblionumber,
ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="a"]') AS
suba,
ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="b"]') AS
subb,
ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="3"]') AS
sub3,
FROM biblio_metadata) AS subfields
WHERE suba = ""
AND subb = ""
AND sub3 = ""

Elaine Bradtke
VWML 
Our income is severely diminished in these exceptionally uncertain times.
Please help us to survive beyond this national crisis. Donate now
.
English Folk Dance and Song Society 
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

Re: [Koha] 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  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
sudrl...@vt.edu | 540.231.9669 <+15402319669>



On Wed, May 27, 2020 at 7:43 PM Mark Alexander  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
>
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Mark Alexander]

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

Re: [Koha] 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
sudrl...@vt.edu | 540.231.9669 <+15402319669>



On Wed, May 27, 2020 at 7:05 PM Elaine Bradtke  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 
> 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 

Re: [Koha] Report help needed

[Elaine Bradtke]

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  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
> sudrl...@vt.edu | 540.231.9669 <+15402319669>
>
>
>
> On Wed, May 27, 2020 at 2:33 PM Elaine Bradtke  

Re: [Koha] 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
sudrl...@vt.edu | 540.231.9669 <+15402319669>



On Wed, May 27, 2020 at 2:33 PM Elaine Bradtke  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"]'
> ) = <>
> 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  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 

Re: [Koha] Report help needed

[Elaine Bradtke]

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"]'
) = <>
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  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.dru...@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('> href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=
>> ',b.biblionumber,'\">',b.biblionumber,'')
>>  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,
>> "")+8+6,1) = 'a';
>>
>> Regards,
>> Jonathan
>>
>> Le mar. 26 mai 2020 à 20:35, Elaine Bradtke  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('> detail.pl?biblionumber=
>> > ',biblionumber,'\">',biblionumber,'')
>> >
>> > AS biblionumber
>> >
>> > FROM biblioitems,
>> >
>> > (SELECT biblioitemnumber, SUBSTR(marcxml,INSTR(marcxml,
>> "")+8+6,1)
>> >
>> > AS leader6 FROM biblioitems)
>> >
>> > AS leaders
>> >
>> > WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND
>> > leaders.leader6 = 'a'
>> >
>> > AND itemtype = <>
>> >
>> > 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

Re: [Koha] Report help needed

[Jonathan Druart]

Hi Elaine,

The biblioitems.marcxml field has been moved to biblio_metadata.metadata.

Does this query work as you want:
SELECT CONCAT('',b.biblionumber,'')
 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,
"")+8+6,1) = 'a';

Regards,
Jonathan

Le mar. 26 mai 2020 à 20:35, Elaine Bradtke  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(' ',biblionumber,'\">',biblionumber,'')
>
> AS biblionumber
>
> FROM biblioitems,
>
> (SELECT biblioitemnumber, SUBSTR(marcxml,INSTR(marcxml, "")+8+6,1)
>
> AS leader6 FROM biblioitems)
>
> AS leaders
>
> WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND
> leaders.leader6 = 'a'
>
> AND itemtype = <>
>
> 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

[Koha] Report help needed

[Elaine Bradtke]

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('',biblionumber,'')

AS biblionumber

FROM biblioitems,

(SELECT biblioitemnumber, SUBSTR(marcxml,INSTR(marcxml, "")+8+6,1)

AS leader6 FROM biblioitems)

AS leaders

WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND
leaders.leader6 = 'a'

AND itemtype = <>

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

Re: [Koha] Report help needed - lost items

[Cab Vinton]

Very late to the party, but maybe this could provide a clue.

The following throws an error, but I believe the basic idea of finding
records where the lowest itemlost value isn't zero is on the right
track:

WHERE itype = <>
GROUP BY biblionumber
HAVING MIN(itemlost) > 0

I suspect SQL is sensitive to the order of these statements, but there
are probably other issues as well.

Hopefully someone else w/ better report-writing chops than me can take
it from there. Or if you found something that works, Elaine, please
share w/ the list :-)

All best,

Cab Vinton, Director
Plaistow Public Library
Plaistow, NH


On Sat, Aug 24, 2019 at 8:30 PM Elaine Bradtke  wrote:
>
> Mark, that still didn't do the trick, but thanks!
> Caroline, this is much closer to what I need. But still not quite right.
> It's messy because I've altered a report that I found in the report library
> rather inelegantly.
>
> What I really need is a list of titles and biblio numbers of biblios where
> the last item is lost, or missing, or withdrawn (I can change that part of
> the query myself).  I have to limit it by format because we have articles
> and components in the catalogue that don't have item records attached.
> What I'm getting now lis a list of biblios with items that are missing but
> it includes biblios that still have one or more items that are on the shelf
> (but at least one item that is missing). Or in other words, I'm looking for
> biblios where the all the items are missing/lost/withdrawn.
>
> Many thanks for your help!
>
> 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 Fri, Aug 23, 2019 at 12:30 PM Caroline Cyr-La-Rose <
> caroline.cyr-la-r...@inlibro.com> wrote:
>
> > Hello Elaine,
> >
> > from what I understand, you want bibliographic information on lost items?
> >
> > I'd need to know exactly the purpose of the report to help you more as
> > there are things in there I don't understand (like why select barcode,
> > when afterwards you group by biblionumber, or why the title is there
> > twice).
> >
> > First, instead of selecting from the biblio table, I would go the other
> > way and select from the items table. That way, you are sure of getting
> > only things that have an item.
> >
> > If you want to limit by itemtype, you can add WHERE itype = < > type|itemtypes>> in your query to have the ability to choose your
> > itemtype upon executing your report.
> >
> > I cleaned your query up a little bit, but like I mentioned before, as I
> > don't know the purpose, it might not be what you need.
> >
> > SELECT
> >  CONCAT(
> >  ' >  biblionumber,
> >  '\">',
> >  title,
> >  ''
> >  ) AS Title,
> >  biblionumber,
> >  barcode,
> >  ccode,
> >  onloan AS 'Checkout date'
> > FROM
> >  items
> >  JOIN biblio USING (biblionumber)
> > WHERE itemlost != 0
> >AND itype = <>
> >
> > Let us know if this works.
> > Caroline
> >
> >
> > On 19-08-23 15 h 00, Mark Alexander wrote:
> > > Excerpts from Elaine Bradtke's message of 2019-08-23 11:32:44 -0700:
> > >> I've got the following adapted from one in the reports library.  But I
> > need
> > >> to limit it by item type, or not return everything that doesn't have an
> > >> item record attached. We've got lots of components in our catalogue that
> > >> don't have items and it's returning all of them:
> > >>
> > >> SELECT
> > >>  CONCAT(
> > >>  ' > ',
> > >>  bib.biblionumber,
> > >>  '\">',
> > >>  bib.title,
> > >>  ''
> > >>  ) AS Title,
> > >>  bib.title,
> > >>  bib.biblionumber,
> > >>  lostitems.barcode,
> > >>  lostitems.ccode,
> > >>  lostitems.onloan AS 'Checkout date'
> > >> FROM
> > >>  biblio bib
> > >>  LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
> > >> items.itemlost = 0)
> > >>  LEFT JOIN items AS lostitems ON (bib.biblionumber =
> > >> lostitems.biblionumber AND lostitems.itemlost != 0)
> > >> GROUP BY bib.biblionumber
> > >> HAVING count(items.itemnumber) = 0
> > > While I'm not an SQL expert by any means, I think I was able to get
> > > this to work on my catalog by removing the HAVING clause and
> > > putting "WHERE items.itemnumber IS NULL" before the GROUP clause:
> > >
> > > SELECT
> > >  CONCAT(
> > >  ' > ',
> > >  bib.biblionumber,
> > >  '\">',
> > >  bib.title,
> > >   

Re: [Koha] Report help needed - lost items

[Elaine Bradtke]

Mark, that still didn't do the trick, but thanks!
Caroline, this is much closer to what I need. But still not quite right.
It's messy because I've altered a report that I found in the report library
rather inelegantly.

What I really need is a list of titles and biblio numbers of biblios where
the last item is lost, or missing, or withdrawn (I can change that part of
the query myself).  I have to limit it by format because we have articles
and components in the catalogue that don't have item records attached.
What I'm getting now lis a list of biblios with items that are missing but
it includes biblios that still have one or more items that are on the shelf
(but at least one item that is missing). Or in other words, I'm looking for
biblios where the all the items are missing/lost/withdrawn.

Many thanks for your help!

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 Fri, Aug 23, 2019 at 12:30 PM Caroline Cyr-La-Rose <
caroline.cyr-la-r...@inlibro.com> wrote:

> Hello Elaine,
>
> from what I understand, you want bibliographic information on lost items?
>
> I'd need to know exactly the purpose of the report to help you more as
> there are things in there I don't understand (like why select barcode,
> when afterwards you group by biblionumber, or why the title is there
> twice).
>
> First, instead of selecting from the biblio table, I would go the other
> way and select from the items table. That way, you are sure of getting
> only things that have an item.
>
> If you want to limit by itemtype, you can add WHERE itype = < type|itemtypes>> in your query to have the ability to choose your
> itemtype upon executing your report.
>
> I cleaned your query up a little bit, but like I mentioned before, as I
> don't know the purpose, it might not be what you need.
>
> SELECT
>  CONCAT(
>  '  biblionumber,
>  '\">',
>  title,
>  ''
>  ) AS Title,
>  biblionumber,
>  barcode,
>  ccode,
>  onloan AS 'Checkout date'
> FROM
>  items
>  JOIN biblio USING (biblionumber)
> WHERE itemlost != 0
>AND itype = <>
>
> Let us know if this works.
> Caroline
>
>
> On 19-08-23 15 h 00, Mark Alexander wrote:
> > Excerpts from Elaine Bradtke's message of 2019-08-23 11:32:44 -0700:
> >> I've got the following adapted from one in the reports library.  But I
> need
> >> to limit it by item type, or not return everything that doesn't have an
> >> item record attached. We've got lots of components in our catalogue that
> >> don't have items and it's returning all of them:
> >>
> >> SELECT
> >>  CONCAT(
> >>  ' ',
> >>  bib.biblionumber,
> >>  '\">',
> >>  bib.title,
> >>  ''
> >>  ) AS Title,
> >>  bib.title,
> >>  bib.biblionumber,
> >>  lostitems.barcode,
> >>  lostitems.ccode,
> >>  lostitems.onloan AS 'Checkout date'
> >> FROM
> >>  biblio bib
> >>  LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
> >> items.itemlost = 0)
> >>  LEFT JOIN items AS lostitems ON (bib.biblionumber =
> >> lostitems.biblionumber AND lostitems.itemlost != 0)
> >> GROUP BY bib.biblionumber
> >> HAVING count(items.itemnumber) = 0
> > While I'm not an SQL expert by any means, I think I was able to get
> > this to work on my catalog by removing the HAVING clause and
> > putting "WHERE items.itemnumber IS NULL" before the GROUP clause:
> >
> > SELECT
> >  CONCAT(
> >  ' ',
> >  bib.biblionumber,
> >  '\">',
> >  bib.title,
> >  ''
> >  ) AS Title,
> >  bib.title,
> >  bib.biblionumber,
> >  lostitems.barcode,
> >  lostitems.ccode,
> >  lostitems.onloan AS 'Checkout date'
> > FROM
> >  biblio bib
> >  LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
> > items.itemlost = 0)
> >  LEFT JOIN items AS lostitems ON (bib.biblionumber =
> > lostitems.biblionumber AND lostitems.itemlost != 0)
> > WHERE items.itemnumber IS NULL
> > GROUP BY bib.biblionumber
> > ___
> > Koha mailing list  http://koha-community.org
> > Koha@lists.katipo.co.nz
> > https://lists.katipo.co.nz/mailman/listinfo/koha
>
> --
> Caroline Cyr La Rose, M.S.I.
> Bibliothécaire | Responsable de produit
>
> Tél. : 1-833-465-4276, poste 221
> caroline.cyr-la-r...@inlibro.com 

Re: [Koha] Report help needed - lost items

[Caroline Cyr-La-Rose]

Hello Elaine,

from what I understand, you want bibliographic information on lost items?

I'd need to know exactly the purpose of the report to help you more as 
there are things in there I don't understand (like why select barcode, 
when afterwards you group by biblionumber, or why the title is there twice).


First, instead of selecting from the biblio table, I would go the other 
way and select from the items table. That way, you are sure of getting 
only things that have an item.


If you want to limit by itemtype, you can add WHERE itype = > in your query to have the ability to choose your 
itemtype upon executing your report.


I cleaned your query up a little bit, but like I mentioned before, as I 
don't know the purpose, it might not be what you need.


SELECT
CONCAT(
'',
title,
''
) AS Title,
biblionumber,
barcode,
ccode,
onloan AS 'Checkout date'
FROM
items
JOIN biblio USING (biblionumber)
WHERE itemlost != 0
  AND itype = <>

Let us know if this works.
Caroline


On 19-08-23 15 h 00, Mark Alexander wrote:

Excerpts from Elaine Bradtke's message of 2019-08-23 11:32:44 -0700:

I've got the following adapted from one in the reports library.  But I need
to limit it by item type, or not return everything that doesn't have an
item record attached. We've got lots of components in our catalogue that
don't have items and it's returning all of them:

SELECT
 CONCAT(
 '',
 bib.title,
 ''
 ) AS Title,
 bib.title,
 bib.biblionumber,
 lostitems.barcode,
 lostitems.ccode,
 lostitems.onloan AS 'Checkout date'
FROM
 biblio bib
 LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
items.itemlost = 0)
 LEFT JOIN items AS lostitems ON (bib.biblionumber =
lostitems.biblionumber AND lostitems.itemlost != 0)
GROUP BY bib.biblionumber
HAVING count(items.itemnumber) = 0

While I'm not an SQL expert by any means, I think I was able to get
this to work on my catalog by removing the HAVING clause and
putting "WHERE items.itemnumber IS NULL" before the GROUP clause:

SELECT
 CONCAT(
 '',
 bib.title,
 ''
 ) AS Title,
 bib.title,
 bib.biblionumber,
 lostitems.barcode,
 lostitems.ccode,
 lostitems.onloan AS 'Checkout date'
FROM
 biblio bib
 LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
items.itemlost = 0)
 LEFT JOIN items AS lostitems ON (bib.biblionumber =
lostitems.biblionumber AND lostitems.itemlost != 0)
WHERE items.itemnumber IS NULL
GROUP BY bib.biblionumber
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha


--
Caroline Cyr La Rose, M.S.I.
Bibliothécaire | Responsable de produit

Tél. : 1-833-465-4276, poste 221
caroline.cyr-la-r...@inlibro.com 

INLiBRO | Spécialistes en technologies documentaires | www.inLibro.com 


___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed - lost items

[Mark Alexander]

Excerpts from Elaine Bradtke's message of 2019-08-23 11:32:44 -0700:
> I've got the following adapted from one in the reports library.  But I need
> to limit it by item type, or not return everything that doesn't have an
> item record attached. We've got lots of components in our catalogue that
> don't have items and it's returning all of them:
> 
> SELECT
> CONCAT(
> ' bib.biblionumber,
> '\">',
> bib.title,
> ''
> ) AS Title,
> bib.title,
> bib.biblionumber,
> lostitems.barcode,
> lostitems.ccode,
> lostitems.onloan AS 'Checkout date'
> FROM
> biblio bib
> LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
> items.itemlost = 0)
> LEFT JOIN items AS lostitems ON (bib.biblionumber =
> lostitems.biblionumber AND lostitems.itemlost != 0)
> GROUP BY bib.biblionumber
> HAVING count(items.itemnumber) = 0

While I'm not an SQL expert by any means, I think I was able to get
this to work on my catalog by removing the HAVING clause and
putting "WHERE items.itemnumber IS NULL" before the GROUP clause:

SELECT
CONCAT(
'',
bib.title,
''
) AS Title,
bib.title,
bib.biblionumber,
lostitems.barcode,
lostitems.ccode,
lostitems.onloan AS 'Checkout date'
FROM
biblio bib
LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
items.itemlost = 0)
LEFT JOIN items AS lostitems ON (bib.biblionumber =
lostitems.biblionumber AND lostitems.itemlost != 0)
WHERE items.itemnumber IS NULL 
GROUP BY bib.biblionumber
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

[Koha] Report help needed - lost items

[Elaine Bradtke]

I've got the following adapted from one in the reports library.  But I need
to limit it by item type, or not return everything that doesn't have an
item record attached. We've got lots of components in our catalogue that
don't have items and it's returning all of them:

SELECT
CONCAT(
'',
bib.title,
''
) AS Title,
bib.title,
bib.biblionumber,
lostitems.barcode,
lostitems.ccode,
lostitems.onloan AS 'Checkout date'
FROM
biblio bib
LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
items.itemlost = 0)
LEFT JOIN items AS lostitems ON (bib.biblionumber =
lostitems.biblionumber AND lostitems.itemlost != 0)
GROUP BY bib.biblionumber
HAVING count(items.itemnumber) = 0

Thanks!
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
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Elaine Bradtke]

Yes, that did the trick!  Thanks
This is the revised report:
SELECT DISTINCT biblionumber, heading FROM (
 SELECT biblionumber, ExtractValue(metadata,
'//datafield[@tag="100"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="100"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="100"]/subfield[@code="9"]')) = 0
UNION
 SELECT biblionumber, ExtractValue(metadata,
'//datafield[@tag="110"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="110"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="110"]/subfield[@code="9"]')) = 0
UNION
 SELECT biblionumber, ExtractValue(metadata,
'//datafield[@tag="111"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="111"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="111"]/subfield[@code="9"]')) = 0
UNION
 SELECT biblionumber, ExtractValue(metadata,
'//datafield[@tag="130"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="130"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="130"]/subfield[@code="9"]')) = 0
) AS heads
ORDER BY heading

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
---
"Writing about music is like dancing about architecture"
--Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)

On Fri, Jul 13, 2018 at 11:53 AM, Paul Hoffman  wrote:

> On Fri, Jul 13, 2018 at 10:06:56AM -0700, Elaine Bradtke wrote:
> > This report finds Authors not linked to authorities.  But it only lists
> > their names (some of them are quite prolific, and sometimes it's only one
> > biblio that needs checking).
> > How can I make the following give the biblio number as well as the name?
> >
> > SELECT DISTINCT heading FROM (
> >  SELECT ExtractValue(metadata,
> > '//datafield[@tag="100"]/subfield[@code="a"]') AS heading
> >  FROM biblio_metadata
> >  WHERE
> >   length(ExtractValue(metadata,
> > '//datafield[@tag="100"]/subfield[@code="a"]')) != 0 AND
> >   length(ExtractValue(metadata,
> > '//datafield[@tag="100"]/subfield[@code="9"]')) = 0
> > UNION
> > [...]
>
> I assume that you can just add biblionumber to the output everywhere
> that a heading appears, something like this (untested!):
>
> SELECT DISTINCT biblionumber, heading FROM (
> SELECT biblionumber, ExtractValue(metadata, 
> '//datafield[@tag="100"]/subfield[@code="a"]')
> AS heading
> FROM   biblio_metadata
> WHERE  length(ExtractValue(metadata, 
> '//datafield[@tag="100"]/subfield[@code="a"]'))
> != 0
> ANDlength(ExtractValue(metadata, 
> '//datafield[@tag="100"]/subfield[@code="9"]'))
> = 0
> UNION
> [...]
>
> Paul.
>
> --
> Paul Hoffman 
> Software Manager
> Fenway Libraries Online
> c/o Wentworth Institute of Technology
> 550 Huntington Ave.
> Boston, MA 02115
> (617) 442-2384 (FLO main number)
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Paul Hoffman]

On Fri, Jul 13, 2018 at 10:06:56AM -0700, Elaine Bradtke wrote:
> This report finds Authors not linked to authorities.  But it only lists
> their names (some of them are quite prolific, and sometimes it's only one
> biblio that needs checking).
> How can I make the following give the biblio number as well as the name?
> 
> SELECT DISTINCT heading FROM (
>  SELECT ExtractValue(metadata,
> '//datafield[@tag="100"]/subfield[@code="a"]') AS heading
>  FROM biblio_metadata
>  WHERE
>   length(ExtractValue(metadata,
> '//datafield[@tag="100"]/subfield[@code="a"]')) != 0 AND
>   length(ExtractValue(metadata,
> '//datafield[@tag="100"]/subfield[@code="9"]')) = 0
> UNION
> [...]

I assume that you can just add biblionumber to the output everywhere 
that a heading appears, something like this (untested!):

SELECT DISTINCT biblionumber, heading FROM (
SELECT biblionumber, ExtractValue(metadata, 
'//datafield[@tag="100"]/subfield[@code="a"]') AS heading
FROM   biblio_metadata
WHERE  length(ExtractValue(metadata, 
'//datafield[@tag="100"]/subfield[@code="a"]')) != 0
ANDlength(ExtractValue(metadata, 
'//datafield[@tag="100"]/subfield[@code="9"]')) = 0
UNION
[...]

Paul.

-- 
Paul Hoffman 
Software Manager
Fenway Libraries Online
c/o Wentworth Institute of Technology
550 Huntington Ave.
Boston, MA 02115
(617) 442-2384 (FLO main number)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

[Koha] Report help needed

[Elaine Bradtke]

This report finds Authors not linked to authorities.  But it only lists
their names (some of them are quite prolific, and sometimes it's only one
biblio that needs checking).
How can I make the following give the biblio number as well as the name?

SELECT DISTINCT heading FROM (
 SELECT ExtractValue(metadata,
'//datafield[@tag="100"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="100"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="100"]/subfield[@code="9"]')) = 0
UNION
 SELECT ExtractValue(metadata,
'//datafield[@tag="110"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="110"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="110"]/subfield[@code="9"]')) = 0
UNION
 SELECT ExtractValue(metadata,
'//datafield[@tag="111"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="111"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="111"]/subfield[@code="9"]')) = 0
UNION
 SELECT ExtractValue(metadata,
'//datafield[@tag="130"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="130"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="130"]/subfield[@code="9"]')) = 0
) AS heads
ORDER BY heading

Thanks in advance!

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
---
"Writing about music is like dancing about architecture"
--Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

[Koha] Report help needed

[Elaine Bradtke]

We have one record in the catalogue that is missing a 942 $c.
but  I can't find it.  I'm looking for a 942$c that is empty. If I could
get the biblio number of the problem record, I could fix it.

Thanks in advance

-- 
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
---
"Writing about music is like dancing about architecture"
--Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Elaine Bradtke]

Thanks Heather, and double thanks for the hint about commas.  It only takes
one little missing piece of the puzzle for the whole thing to fail.
I could really use something like SQL queries for Dummies.
Elaine

On Mon, Nov 17, 2014 at 11:47 PM, Heather Braum (NEKLS) hbr...@nekls.org
wrote:

 Elaine, try the below. It looks like you were missing some commas -- that
 was what was causing the syntax errors; you can then name the columns like
 I did below or however you want using as ___ single-word (as 780t) or as
 two or more words (as 780w field_).

 Make sure every column of data in the select statement has a comma after
 it, except for the last on before the from statement.

 Hope that helps!  -- it ran on my system.

 SELECT
 biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype,

 ExtractValue(biblioitems.marcxml,
 '//datafield[@tag=780]/subfield[@code=t]') as 780t,
 ExtractValue(biblioitems.marcxml,
 '//datafield[@tag=780]/subfield[@code=w]') as 780w field,
 ExtractValue(biblioitems.marcxml,
 '//datafield[@tag=866]/subfield[@code=a]') as 866a,
 ExtractValue(biblioitems.marcxml,
 '//datafield[@tag=866]/subfield[@code=z]') as 866z
 FROM biblioitems LEFT JOIN biblio USING (biblionumber)
 WHERE biblioitems.itemtype='CR'

 Heather Braum
 NExpress Coordinator
 Resource Sharing Librarian
 Northeast Kansas Library System
 hbr...@nekls.org

 The illiterate of the 21st century will not be those who cannot read
 and write, but those who cannot learn, unlearn, and relearn. ~Alvin
 Toffler, *Rethinking the Future*




 On Mon, Nov 17, 2014 at 5:38 PM, Elaine Bradtke e...@efdss.org wrote:

 I have a request for a report that is beyond my paltry abilities and that
 of the report builder.


 I have this, and it works (thanks Liz!):
 SELECT

  
 biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype
 FROM biblioitems LEFT JOIN biblio USING (biblionumber)   WHERE
 biblioitems.itemtype='CR'

 But I need to add in more columns that involve the MARC XML

 Specifically
 780 t (previous title)
 780 w (previous control no.)
 866 a (holdings)
 866 z (note)
 867 e (source)

 When I try to add in:
   Extract value ExtractValue(marcxml,
 '//datafield[@tag=780]/subfield[@code=t]')
 I manage to break the above query and it complains of syntax errors.
 Does it matter where this Extract phrase comes in?
 How do you handle multiple subfields (780 t and w for instance)
 For that matter, how do you handle multiple fields?  And how can I label
 the columns

 The following (and a few variations on the theme) did not work:

 SELECT

  
 biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype
 ExtractValue(marcxml, '//datafield[@tag=780]/subfield[@code=t]')
 ExtractValue(marcxml, '//datafield[@tag=780]/subfield[@code=w]')
 ExtractValue(marcxml, '//datafield[@tag=866]/subfield[@code=a]')
 ExtractValue(marcxml, '//datafield[@tag=866]/subfield[@code=z]')
 FROM biblioitems LEFT JOIN biblio USING (biblionumber)   WHERE
 biblioitems.itemtype='CR'


 As always I am very, very grateful for whatever help you can give.
 Thanks
 --
 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)
 ___
 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)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

[Koha] Report help needed

[Elaine Bradtke]

I have a request for a report that is beyond my paltry abilities and that
of the report builder.


I have this, and it works (thanks Liz!):
SELECT
 
biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype
FROM biblioitems LEFT JOIN biblio USING (biblionumber)   WHERE
biblioitems.itemtype='CR'

But I need to add in more columns that involve the MARC XML

Specifically
780 t (previous title)
780 w (previous control no.)
866 a (holdings)
866 z (note)
867 e (source)

When I try to add in:
  Extract value ExtractValue(marcxml,
'//datafield[@tag=780]/subfield[@code=t]')
I manage to break the above query and it complains of syntax errors.
Does it matter where this Extract phrase comes in?
How do you handle multiple subfields (780 t and w for instance)
For that matter, how do you handle multiple fields?  And how can I label
the columns

The following (and a few variations on the theme) did not work:

SELECT
 
biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype
ExtractValue(marcxml, '//datafield[@tag=780]/subfield[@code=t]')
ExtractValue(marcxml, '//datafield[@tag=780]/subfield[@code=w]')
ExtractValue(marcxml, '//datafield[@tag=866]/subfield[@code=a]')
ExtractValue(marcxml, '//datafield[@tag=866]/subfield[@code=z]')
FROM biblioitems LEFT JOIN biblio USING (biblionumber)   WHERE
biblioitems.itemtype='CR'


As always I am very, very grateful for whatever help you can give.
Thanks
-- 
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)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Heather Braum (NEKLS)]

Elaine, try the below. It looks like you were missing some commas -- that
was what was causing the syntax errors; you can then name the columns like
I did below or however you want using as ___ single-word (as 780t) or as
two or more words (as 780w field_).

Make sure every column of data in the select statement has a comma after
it, except for the last on before the from statement.

Hope that helps!  -- it ran on my system.

SELECT
biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype,

ExtractValue(biblioitems.marcxml,
'//datafield[@tag=780]/subfield[@code=t]') as 780t,
ExtractValue(biblioitems.marcxml,
'//datafield[@tag=780]/subfield[@code=w]') as 780w field,
ExtractValue(biblioitems.marcxml,
'//datafield[@tag=866]/subfield[@code=a]') as 866a,
ExtractValue(biblioitems.marcxml,
'//datafield[@tag=866]/subfield[@code=z]') as 866z
FROM biblioitems LEFT JOIN biblio USING (biblionumber)
WHERE biblioitems.itemtype='CR'

Heather Braum
NExpress Coordinator
Resource Sharing Librarian
Northeast Kansas Library System
hbr...@nekls.org

The illiterate of the 21st century will not be those who cannot read
and write, but those who cannot learn, unlearn, and relearn. ~Alvin
Toffler, *Rethinking the Future*




On Mon, Nov 17, 2014 at 5:38 PM, Elaine Bradtke e...@efdss.org wrote:

 I have a request for a report that is beyond my paltry abilities and that
 of the report builder.


 I have this, and it works (thanks Liz!):
 SELECT

  
 biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype
 FROM biblioitems LEFT JOIN biblio USING (biblionumber)   WHERE
 biblioitems.itemtype='CR'

 But I need to add in more columns that involve the MARC XML

 Specifically
 780 t (previous title)
 780 w (previous control no.)
 866 a (holdings)
 866 z (note)
 867 e (source)

 When I try to add in:
   Extract value ExtractValue(marcxml,
 '//datafield[@tag=780]/subfield[@code=t]')
 I manage to break the above query and it complains of syntax errors.
 Does it matter where this Extract phrase comes in?
 How do you handle multiple subfields (780 t and w for instance)
 For that matter, how do you handle multiple fields?  And how can I label
 the columns

 The following (and a few variations on the theme) did not work:

 SELECT

  
 biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype
 ExtractValue(marcxml, '//datafield[@tag=780]/subfield[@code=t]')
 ExtractValue(marcxml, '//datafield[@tag=780]/subfield[@code=w]')
 ExtractValue(marcxml, '//datafield[@tag=866]/subfield[@code=a]')
 ExtractValue(marcxml, '//datafield[@tag=866]/subfield[@code=z]')
 FROM biblioitems LEFT JOIN biblio USING (biblionumber)   WHERE
 biblioitems.itemtype='CR'


 As always I am very, very grateful for whatever help you can give.
 Thanks
 --
 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)
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed - modification date

[Elaine Bradtke]

Thanks, that helps!
Elaine



On Wed, Sep 3, 2014 at 12:08 PM, Nick Clemens n...@quecheelibrary.org
wrote:

 To get the '005' data, I think you have to pull from the marcxml in
 biblioitems:

 ExtractValue(marcxml,'//controlfield[@tag=005]')

 I don't think it is accessible through any of the table directly


 On Tue, Sep 2, 2014 at 6:39 PM, Elaine Bradtke e...@efdss.org wrote:

 How do I filter records based on the MARC 005?  DATE AND TIME OF LATEST
 TRANSACTION
 biblio.timestamp doesn't do it.

 This is what I have:
 SELECT

  
 biblio.biblionumber,items.barcode,biblio.author,biblio.title,biblio.timestamp,biblio.datecreated
 FROM items LEFT JOIN biblioitems on
 (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on
 (biblioitems.biblionumber=biblio.biblionumber)   WHERE biblio.timestamp =
 '2014-05-01' AND biblio.timestamp = '2014-05-30' ORDER BY
 biblio.biblionumber asc

 In the guided report the option I chose was Modification date /
 biblio.timestamp

 But after testing this I've discovered that  it isn't using the date in
 the
 005.  I'm not sure where the  Modification date / biblio.timestamp
 information is coming from, it's neither the creation date, nor the
 modification date, but some other date entirely.

 I'm trying to hunt down a batch of records modified on a certain date.
 Thanks for any help.

 --
 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)
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha




 --
 Nick Clemens
 Quechee  Wilder Libraries
 n...@quecheelibrary.org
 http://www.QuecheeLibrary.org
 Q (802) 295-1232 W (802) 295-6341




-- 
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)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

[Koha] Report help needed - modification date

[Elaine Bradtke]

How do I filter records based on the MARC 005?  DATE AND TIME OF LATEST
TRANSACTION
biblio.timestamp doesn't do it.

This is what I have:
SELECT
 
biblio.biblionumber,items.barcode,biblio.author,biblio.title,biblio.timestamp,biblio.datecreated
FROM items LEFT JOIN biblioitems on
(items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on
(biblioitems.biblionumber=biblio.biblionumber)   WHERE biblio.timestamp =
'2014-05-01' AND biblio.timestamp = '2014-05-30' ORDER BY
biblio.biblionumber asc

In the guided report the option I chose was Modification date /
biblio.timestamp

But after testing this I've discovered that  it isn't using the date in the
005.  I'm not sure where the  Modification date / biblio.timestamp
information is coming from, it's neither the creation date, nor the
modification date, but some other date entirely.

I'm trying to hunt down a batch of records modified on a certain date.
Thanks for any help.

-- 
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)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed - filter by itemtype

[vishal patil]

hi,

For item pull down list try this:

Replace this: biblioitems.itemtype=‘BK’  with:

items.itype=Select Itemtype|itemtypes



On Mon, Jun 9, 2014 at 5:49 AM, Robin Sheat ro...@catalyst.net.nz wrote:

 Elaine Bradtke schreef op vr 06-06-2014 om 21:42 [+0100]:
  WHERE biblioitems.itemtype=‘BK’ AND datecreated

 You have smartquotes around the BK. You can't have smartquotes anywhere.

 --
 Robin Sheat
 Catalyst IT Ltd.
 ✆ +64 4 803 2204
 GPG: 5FA7 4B49 1E4D CAA4 4C38  8505 77F5 B724 F871 3BDF

 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha




-- 


*Warm Regards,Vishal Patil*Software Engineer - Nucsoft Ltd.
phone: 022 32080161
www.osslabs.biz
www.nucsoft.com
vishal.pa...@osslabs.biz
 http://nucsoft.com/nucweb/images/OSS_labs.jpg
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed - filter by itemtype

[Robin Sheat]

Elaine Bradtke schreef op vr 06-06-2014 om 21:42 [+0100]:
 WHERE biblioitems.itemtype=‘BK’ AND datecreated

You have smartquotes around the BK. You can't have smartquotes anywhere.

-- 
Robin Sheat
Catalyst IT Ltd.
✆ +64 4 803 2204
GPG: 5FA7 4B49 1E4D CAA4 4C38  8505 77F5 B724 F871 3BDF

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

[Koha] Report help needed - filter by itemtype

[Elaine Bradtke]

I can't seem to get the report to filter on item type.  Probably another
typo somewhere. it works if I take out this
line: biblioitems.itemtype=‘BK’   But I want to filter by books (and
eventually other item types).


SELECT
ExtractValue(b.marcxml, '//datafield[@tag=100]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=245]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=245]/subfield[@code=b]'),
ExtractValue(b.marcxml, '//datafield[@tag=245]/subfield[@code=c]'),
ExtractValue(b.marcxml, '//datafield[@tag=260]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=773]/subfield[@code=t]'),
ExtractValue(b.marcxml, '//datafield[@tag=773]/subfield[@code=g]'),
ExtractValue(b.marcxml, '//datafield[@tag=260]/subfield[@code=b]'),
ExtractValue(b.marcxml, '//datafield[@tag=260]/subfield[@code=c]'),
ExtractValue(b.marcxml, '//datafield[@tag=300]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=020]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=022]/subfield[@code=a]'),
monthname(datecreated) AS month,
year(datecreated) AS year,
biblionumber AS biblionumber
LEFT JOIN biblioitems b USING (biblionumber)
FROM biblio
WHERE biblioitems.itemtype=‘BK’ AND datecreated
BETWEEN Between (-mm-dd) AND and (-mm-dd)

-- 
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)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

[Koha] Report help needed

[Elaine Bradtke]

I'll admit, I'm the queen of cut and paste.  What I don't know about SQL
would fill a book.

Using a report I already have on hand:
SELECT ExtractValue(b.marcxml,
'//datafield[@tag=245]/subfield[@code=a]'), ExtractValue(b.marcxml,
'//datafield[@tag=260]/subfield[@code=a]'),ExtractValue(b.marcxml,
'//datafield[@tag=260]/subfield[@code=c]'), monthname(datecreated) AS
month, year(datecreated) AS year, biblionumber AS biblionumber
FROM biblio
LEFT JOIN biblioitems b USING (biblionumber)
WHERE datecreated BETWEEN Between (-mm-dd) AND and (-mm-dd)

I tried to add some fields as per a request from one of our staff who would
like a rather thorough list of items added to the catalogue within a time
frame.

SELECT ExtractValue(b.marcxml,
'//datafield[@tag=“100”]/subfield[@code=a]'), ExtractValue(b.marcxml,
'//datafield[@tag=245]/subfield[@code=a]'), ExtractValue(b.marcxml,
'//datafield[@tag=245]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
'//datafield[@tag=245]/subfield[@code=“c”]’), ExtractValue(b.marcxml,
'//datafield[@tag=260]/subfield[@code=a]'), ExtractValue(b.marcxml,
'//datafield[@tag=“773”]/subfield[@code=“t”]’), ExtractValue(b.marcxml,
'//datafield[@tag=“773”]/subfield[@code=“g”]’), ExtractValue(b.marcxml,
'//datafield[@tag=260]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
'//datafield[@tag=260]/subfield[@code=c]'), ExtractValue(b.marcxml,
'//datafield[@tag=“300”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
'//datafield[@tag=“020”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
'//datafield[@tag=“022”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS
biblionumber
FROM biblio
LEFT JOIN biblioitems b USING (biblionumber)
WHERE datecreated BETWEEN Between (-mm-dd) AND and (-mm-dd)


The first report works.  The second doesn't.  I'm sure it's a syntax error,
or something like that, but I'm blundering around in the dark.


Her request was for the following info: 100 field = Author | 245 = Title
proper (including subtitles and statement of responsibility) | 773(?) =
Periodical title |260 = place, publisher, and date | 300 = Extent | 020 =
ISBN / 022 = ISSN


Can anyone help?

-- 
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)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Steven Nickerson]

Hi Elaine,
   I'm really not positive this is the issue, but I would double-check to make 
sure that all of your single and double quotes are standard ASCII quote marks.  
 It looks like several of them are special characters, perhaps automatically 
converted somewhere along the way in your copy/paste (thank you smart 
programs like MS Word).   Again, just a guess, and maybe those characters were 
correct when you executed the MySQL in the report and only got converted when 
entered into your email application.   They just look suspect to me.

-Original Message-
From: Elaine Bradtke [mailto:e...@efdss.org] 
Sent: Tuesday, May 20, 2014 12:58 PM
To: koha
Subject: [Koha] Report help needed

I'll admit, I'm the queen of cut and paste.  What I don't know about SQL would 
fill a book.

Using a report I already have on hand:
SELECT ExtractValue(b.marcxml,
'//datafield[@tag=245]/subfield[@code=a]'), ExtractValue(b.marcxml, 
'//datafield[@tag=260]/subfield[@code=a]'),ExtractValue(b.marcxml,
'//datafield[@tag=260]/subfield[@code=c]'), monthname(datecreated) AS 
month, year(datecreated) AS year, biblionumber AS biblionumber FROM biblio LEFT 
JOIN biblioitems b USING (biblionumber) WHERE datecreated BETWEEN Between 
(-mm-dd) AND and (-mm-dd)

I tried to add some fields as per a request from one of our staff who would 
like a rather thorough list of items added to the catalogue within a time frame.

SELECT ExtractValue(b.marcxml,
'//datafield[@tag=“100”]/subfield[@code=a]'), ExtractValue(b.marcxml, 
'//datafield[@tag=245]/subfield[@code=a]'), ExtractValue(b.marcxml, 
'//datafield[@tag=245]/subfield[@code=“b”]’), ExtractValue(b.marcxml, 
'//datafield[@tag=245]/subfield[@code=“c”]’), ExtractValue(b.marcxml, 
'//datafield[@tag=260]/subfield[@code=a]'), ExtractValue(b.marcxml, 
'//datafield[@tag=“773”]/subfield[@code=“t”]’), ExtractValue(b.marcxml, 
'//datafield[@tag=“773”]/subfield[@code=“g”]’), ExtractValue(b.marcxml, 
'//datafield[@tag=260]/subfield[@code=“b”]’), ExtractValue(b.marcxml, 
'//datafield[@tag=260]/subfield[@code=c]'), ExtractValue(b.marcxml, 
'//datafield[@tag=“300”]/subfield[@code=“a”]’), ExtractValue(b.marcxml, 
'//datafield[@tag=“020”]/subfield[@code=“a”]’), ExtractValue(b.marcxml, 
'//datafield[@tag=“022”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS 
biblionumber FROM biblio LEFT JOIN biblioitems b USING (biblionumber) WHERE 
datecreated BETWEEN Between (-mm-dd) AND and (-mm-dd)


The first report works.  The second doesn't.  I'm sure it's a syntax error, or 
something like that, but I'm blundering around in the dark.


Her request was for the following info: 100 field = Author | 245 = Title proper 
(including subtitles and statement of responsibility) | 773(?) = Periodical 
title |260 = place, publisher, and date | 300 = Extent | 020 = ISBN / 022 = ISSN


Can anyone help?

--
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)


___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Bernardo Gonzalez Kriegel]

Hi Elaine,
yes, copy  paste :)

You paste an extra 'ExtractValue(b.marcxml,' before monthname(datecreated)
AS month

Try

SELECT
ExtractValue(b.marcxml, '//datafield[@tag=100]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=245]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=245]/subfield[@code=b]'),
ExtractValue(b.marcxml, '//datafield[@tag=245]/subfield[@code=c]'),
ExtractValue(b.marcxml, '//datafield[@tag=260]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=773]/subfield[@code=t]'),
ExtractValue(b.marcxml, '//datafield[@tag=773]/subfield[@code=g]'),
ExtractValue(b.marcxml, '//datafield[@tag=260]/subfield[@code=b]'),
ExtractValue(b.marcxml, '//datafield[@tag=260]/subfield[@code=c]'),
ExtractValue(b.marcxml, '//datafield[@tag=300]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=020]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=022]/subfield[@code=a]'),
monthname(datecreated) AS month,
year(datecreated) AS year,
biblionumber AS biblionumber
FROM biblio
LEFT JOIN biblioitems b USING (biblionumber)
WHERE datecreated
BETWEEN Between (-mm-dd) AND and (-mm-dd)


Bernardo

-- 
Bernardo Gonzalez Kriegel
bgkrie...@gmail.com


On Tue, May 20, 2014 at 1:57 PM, Elaine Bradtke e...@efdss.org wrote:

 I'll admit, I'm the queen of cut and paste.  What I don't know about SQL
 would fill a book.

 Using a report I already have on hand:
 SELECT ExtractValue(b.marcxml,
 '//datafield[@tag=245]/subfield[@code=a]'), ExtractValue(b.marcxml,
 '//datafield[@tag=260]/subfield[@code=a]'),ExtractValue(b.marcxml,
 '//datafield[@tag=260]/subfield[@code=c]'), monthname(datecreated) AS
 month, year(datecreated) AS year, biblionumber AS biblionumber
 FROM biblio
 LEFT JOIN biblioitems b USING (biblionumber)
 WHERE datecreated BETWEEN Between (-mm-dd) AND and (-mm-dd)

 I tried to add some fields as per a request from one of our staff who would
 like a rather thorough list of items added to the catalogue within a time
 frame.

 SELECT ExtractValue(b.marcxml,
 '//datafield[@tag=“100”]/subfield[@code=a]'), ExtractValue(b.marcxml,
 '//datafield[@tag=245]/subfield[@code=a]'), ExtractValue(b.marcxml,
 '//datafield[@tag=245]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=245]/subfield[@code=“c”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=260]/subfield[@code=a]'), ExtractValue(b.marcxml,
 '//datafield[@tag=“773”]/subfield[@code=“t”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=“773”]/subfield[@code=“g”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=260]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=260]/subfield[@code=c]'), ExtractValue(b.marcxml,
 '//datafield[@tag=“300”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=“020”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=“022”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
 monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS
 biblionumber
 FROM biblio
 LEFT JOIN biblioitems b USING (biblionumber)
 WHERE datecreated BETWEEN Between (-mm-dd) AND and (-mm-dd)


 The first report works.  The second doesn't.  I'm sure it's a syntax error,
 or something like that, but I'm blundering around in the dark.


 Her request was for the following info: 100 field = Author | 245 = Title
 proper (including subtitles and statement of responsibility) | 773(?) =
 Periodical title |260 = place, publisher, and date | 300 = Extent | 020 =
 ISBN / 022 = ISSN


 Can anyone help?

 --
 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)
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Paul A]

It could be just the way that my email client is formatting characters, but 
you may have a problem with the  and '  (double and single quote 
marks.)  I read them below as “, ”, and ’


Maybe you used a text editor that allows curlies rather than straights?

Best -- Paul

At 05:57 PM 5/20/2014 +0100, Elaine Bradtke wrote:

I'll admit, I'm the queen of cut and paste.  What I don't know about SQL
would fill a book.

Using a report I already have on hand:
SELECT ExtractValue(b.marcxml,
'//datafield[@tag=245]/subfield[@code=a]'), ExtractValue(b.marcxml,
'//datafield[@tag=260]/subfield[@code=a]'),ExtractValue(b.marcxml,
'//datafield[@tag=260]/subfield[@code=c]'), monthname(datecreated) AS
month, year(datecreated) AS year, biblionumber AS biblionumber
FROM biblio
LEFT JOIN biblioitems b USING (biblionumber)
WHERE datecreated BETWEEN Between (-mm-dd) AND and (-mm-dd)

I tried to add some fields as per a request from one of our staff who would
like a rather thorough list of items added to the catalogue within a time
frame.

SELECT ExtractValue(b.marcxml,
'//datafield[@tag=“100”]/subfield[@code=a]'), ExtractValue(b.marcxml,
'//datafield[@tag=245]/subfield[@code=a]'), ExtractValue(b.marcxml,
'//datafield[@tag=245]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
'//datafield[@tag=245]/subfield[@code=“c”]’), ExtractValue(b.marcxml,
'//datafield[@tag=260]/subfield[@code=a]'), ExtractValue(b.marcxml,
'//datafield[@tag=“773”]/subfield[@code=“t”]’), 
ExtractValue(b.marcxml,
'//datafield[@tag=“773”]/subfield[@code=“g”]’), 
ExtractValue(b.marcxml,

'//datafield[@tag=260]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
'//datafield[@tag=260]/subfield[@code=c]'), ExtractValue(b.marcxml,
'//datafield[@tag=“300”]/subfield[@code=“a”]’), 
ExtractValue(b.marcxml,
'//datafield[@tag=“020”]/subfield[@code=“a”]’), 
ExtractValue(b.marcxml,
'//datafield[@tag=“022”]/subfield[@code=“a”]’), 
ExtractValue(b.marcxml,

monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS
biblionumber
FROM biblio
LEFT JOIN biblioitems b USING (biblionumber)
WHERE datecreated BETWEEN Between (-mm-dd) AND and (-mm-dd)


The first report works.  The second doesn't.  I'm sure it's a syntax error,
or something like that, but I'm blundering around in the dark.


Her request was for the following info: 100 field = Author | 245 = Title
proper (including subtitles and statement of responsibility) | 773(?) =
Periodical title |260 = place, publisher, and date | 300 = Extent | 020 =
ISBN / 022 = ISSN


Can anyone help?

--
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)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha


---
Maritime heritage and history, preservation and conservation,
research and education through the written word and the arts.
http://NavalMarineArchive.com and http://UltraMarine.ca

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Daniel Sanford]

It looks like the issue is
'//datafield[@tag=“022”]/
subfield[@code=“a”]’), ExtractValue(b.marcxml,
monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS

The last
ExtractValue(b.marcxml,
doesn't have a closing ) and you've stopped extracting XML so that is
causing one error use


//datafield[@tag=“022”]/
subfield[@code=“a”]’),
monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS

You've also got a mix of ’ and ' which are slightly different and causing
MySQL to misread the sections.

Likewise on the line
'//datafield[@tag=245]/subfield[@code=a]'), ExtractValue(b.marcxml,
'//datafield[@tag=245]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
you'll note the a and b subfield codes have different quotation marks, the
one around a are right and the ones around b are not, a lot of the tags and
codes are using those and MySQL often errors out due to the that. The first
query you sent has all of those properly formatted.

Here's a cleaned up copy

 SELECT
ExtractValue(b.marcxml, '//datafield[@tag=100]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=245]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=245]/subfield[@code=b]'),
ExtractValue(b.marcxml, '//datafield[@tag=245]/subfield[@code=c]'),
ExtractValue(b.marcxml, '//datafield[@tag=260]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=773]/subfield[@code=t]'),
ExtractValue(b.marcxml, '//datafield[@tag=773]/subfield[@code=g]'),
ExtractValue(b.marcxml, '//datafield[@tag=260]/subfield[@code=b]'),
ExtractValue(b.marcxml, '//datafield[@tag=260]/subfield[@code=c]'),
ExtractValue(b.marcxml, '//datafield[@tag=300]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=020]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=022]/subfield[@code=a]'),
monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS
biblionumber
FROM biblio
LEFT JOIN biblioitems b USING (biblionumber)
WHERE datecreated BETWEEN Between (-mm-dd) AND and (-mm-dd)



Daniel Sanford
Assistant Technical Librarian for Digital Infrastructure
American Philosophical Society
105 South 5th Street
Philadelphia, PA 19106
Telephone: (215)-599-4313



On Tue, May 20, 2014 at 12:57 PM, Elaine Bradtke e...@efdss.org wrote:

 I'll admit, I'm the queen of cut and paste.  What I don't know about SQL
 would fill a book.

 Using a report I already have on hand:
 SELECT ExtractValue(b.marcxml,
 '//datafield[@tag=245]/subfield[@code=a]'), ExtractValue(b.marcxml,
 '//datafield[@tag=260]/subfield[@code=a]'),ExtractValue(b.marcxml,
 '//datafield[@tag=260]/subfield[@code=c]'), monthname(datecreated) AS
 month, year(datecreated) AS year, biblionumber AS biblionumber
 FROM biblio
 LEFT JOIN biblioitems b USING (biblionumber)
 WHERE datecreated BETWEEN Between (-mm-dd) AND and (-mm-dd)

 I tried to add some fields as per a request from one of our staff who would
 like a rather thorough list of items added to the catalogue within a time
 frame.

 SELECT ExtractValue(b.marcxml,
 '//datafield[@tag=“100”]/subfield[@code=a]'), ExtractValue(b.marcxml,
 '//datafield[@tag=245]/subfield[@code=a]'), ExtractValue(b.marcxml,
 '//datafield[@tag=245]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=245]/subfield[@code=“c”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=260]/subfield[@code=a]'), ExtractValue(b.marcxml,
 '//datafield[@tag=“773”]/subfield[@code=“t”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=“773”]/subfield[@code=“g”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=260]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=260]/subfield[@code=c]'), ExtractValue(b.marcxml,
 '//datafield[@tag=“300”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=“020”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=“022”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
 monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS
 biblionumber
 FROM biblio
 LEFT JOIN biblioitems b USING (biblionumber)
 WHERE datecreated BETWEEN Between (-mm-dd) AND and (-mm-dd)


 The first report works.  The second doesn't.  I'm sure it's a syntax error,
 or something like that, but I'm blundering around in the dark.


 Her request was for the following info: 100 field = Author | 245 = Title
 proper (including subtitles and statement of responsibility) | 773(?) =
 Periodical title |260 = place, publisher, and date | 300 = Extent | 020 =
 ISBN / 022 = ISSN


 Can anyone help?

 --
 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 

[Koha] Report help needed 999$c

[Elaine Bradtke]

 I've got something that extracts the biblio numbers from the 999c. Is
there any way to ask it to only show the ones that contain multiple 999c?


SELECT biblionumber, ExtractValue(marcxml,
'//datafield[@tag=999]/subfield[@code=c]') AS comp FROM
biblioitems

-- 
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)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed 999$c

[Robin Sheat]

Elaine Bradtke schreef op ma 05-05-2014 om 22:55 [+0100]:
  I've got something that extracts the biblio numbers from the 999c. Is
 there any way to ask it to only show the ones that contain multiple 999c?
 
 
 SELECT biblionumber, ExtractValue(marcxml,
 '//datafield[@tag=999]/subfield[@code=c]') AS comp FROM
 biblioitems

This is totally untested, but you might be able to use the XPath count()
function in your ExtractValue. So,:

ExtractValue(marcxml, 'count(//datafield[@tag=999]/subfield[@code=c])')

An example here:

http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html#function_extractvalue

Note that it looked like your data also had multiple entries in one 999
$c field, separated with '|'. That's weird, and this won't find that. On
the other hand, you can just do WHERE field LIKE %|% to catch those.

-- 
Robin Sheat
Catalyst IT Ltd.
✆ +64 4 803 2204
GPG: 5FA7 4B49 1E4D CAA4 4C38  8505 77F5 B724 F871 3BDF

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed 999$c

[Elaine Bradtke]

Note that it looked like your data also had multiple entries in one 999
$c field, separated with '|'. That's weird, and this won't find that. On
the other hand, you can just do WHERE field LIKE %|% to catch those.

Yes, that is weird. I'm trying to figure out where it is coming from.  In a
few cases I've seen like the one I posted, it appears they are multiple
editions of the same book, in which case the cataloguer probably duplicated
the record.  I haven't tested this theory out, but I could see where it
might just add in another biblionumber to the string already there.



On Mon, May 5, 2014 at 11:57 PM, Robin Sheat ro...@catalyst.net.nz wrote:

 Elaine Bradtke schreef op ma 05-05-2014 om 22:55 [+0100]:
   I've got something that extracts the biblio numbers from the 999c. Is
  there any way to ask it to only show the ones that contain multiple 999c?
 
 
  SELECT biblionumber, ExtractValue(marcxml,
  '//datafield[@tag=999]/subfield[@code=c]') AS comp FROM
  biblioitems

 This is totally untested, but you might be able to use the XPath count()
 function in your ExtractValue. So,:

 ExtractValue(marcxml, 'count(//datafield[@tag=999]/subfield[@code=c])')

 An example here:


 http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html#function_extractvalue

 Note that it looked like your data also had multiple entries in one 999
 $c field, separated with '|'. That's weird, and this won't find that. On
 the other hand, you can just do WHERE field LIKE %|% to catch those.

 --
 Robin Sheat
 Catalyst IT Ltd.
 ✆ +64 4 803 2204
 GPG: 5FA7 4B49 1E4D CAA4 4C38  8505 77F5 B724 F871 3BDF

 ___
 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)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

[Koha] Report help needed for List of Items added by cataloger with link to biblio title

[Vimal Kumar V.]

Dear Friends,
I made some changes in the report with title Titles added by cataloger
from SQL Report Library.
My aim is to build a report which list of biblios added by cataloguer.
Now this report display time stamp and biblio number with link.
I request your help to add bibio.title in this report.

SELECT
  timestamp AS 'titles added',
  CONCAT('a
href=\/cgi-bin/koha/catalogue/detail.pl?biblionumber=',object,'\',object,'/a')
AS Biblionumber
FROM action_logs
WHERE module='CATALOGUING' AND user=Borrower number
  AND info!='item' AND action='ADD'


Regards,
-- 
Vimal Kumar V.
Technical Assistant
Mahatma Gandhi University Library
Kottayam, Kerala- 686 560
Web: http://www.vimalkumar.info
Blog: http://linuxhalwa.blogspot.com http://kohageek.blogspot.in
---
I forget what I was taught. I only remember what I have learnt
-Patrick White
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

[Koha] Report help needed

[Sue McMillan]

Hello everyone,

I need  a report that shows the average age of our item collections  at 
specific branches.  I've looked at the pre-made sql reports on the 
Koha-Community pages but nothing there is useful for this report and I've also 
tried to make my own with no success.

I would be grateful if someone could give guide me in the right direction.

Regards

Susan McMillan
Acquisitions and Systems Librarian
South Taranaki District Libraries
06 278 0555 or freecall 0800 111 323 ext 8706
,sue.mcmil...@stdc.govt.nz
http://www.southtaranaki.com


This e-mail and any attachments may contain confidential and 
privileged 
information. If you are not the intended recipient, please notify the 
sender immediately by return e-mail, delete this e-mail and destroy 
any 
copies. Any dissemination or use of this information by a person other 
than the intended recipient is unauthorised and may be illegal. Please 
note that this communication does not designate an information system 
for the purposes of the Electronic Transactions Act 2002.

pbCAN'T OPEN ATTACHMENTS?/b/p
The Council has upgraded to Microsoft office 2007 suite. This may mean you 
cannot open attachments if you have older versions of office. a 
href=http://www.microsoft.com/downloads/details.aspx?familyid=941B3470-3AE9-4AEE-8F43-C6BB74CD1466displaylang=en;
 Click here to access Microsoft Office 2007's compatibility website.a/
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha