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
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,
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
fter 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"]/subf
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.
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,
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
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
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
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
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
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
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
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
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
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
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
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
>
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(
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,
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
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
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
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:
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)
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
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
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
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’
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
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]'),
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,
[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
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,
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
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
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
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]')
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
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
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
42 matches
Mail list logo
