Re: [Koha] SQL help

Hi Tomas, It’s 19.11.07 From: Tomas Cohen Arazi Sent: Wednesday 19 August 2020 16:19 To: Patricia Dowling Cc: koha@lists.katipo.co.nz Subject: Re: [Koha] SQL help What Koha version are you using? The table and column names have changed a bit between versions. El mié., 19 ago. 2020

[Koha] SQL help

I wonder if anyone in the community can steer me on this basic SQL query? I am a very novice SQL user so apologies in advance! I want to get a list of bibnumbers for all records with a value of “Irish Office pamphlets” in the 440a tag (series) with title, author and publication date so that I

Re: [Koha] SQL help

gt;) escribió: > Hi Tomas, > > It’s 19.11.07 > > > > > > > > > > > > > > *From:* Tomas Cohen Arazi > *Sent:* Wednesday 19 August 2020 16:19 > *To:* Patricia Dowling > *Cc:* koha@lists.katipo.co.nz > *Subject:* Re: [Koha] SQL help > &g

Re: [Koha] SQL help

What Koha version are you using? The table and column names have changed a bit between versions. El mié., 19 ago. 2020 a las 11:13, Patricia Dowling (< patricia.dowl...@oireachtas.ie>) escribió: > > I wonder if anyone in the community can steer me on this basic SQL query? > I am a very novice

Re: [Koha] SQL help

Hi Patricia. The three tables are often joined using biblionumber and this might get something close to what you want: SELECT biblionumber, title, author, copyrightdate FROM biblio LEFT JOIN biblioitems USING (biblionumber) LEFT JOIN biblio_metadata USING

Re: [Koha] SQL help

You didn't mention your koha version. That's kinda necessary info if you are trying to extract from marcxml -indranil On Wed, 19 Aug, 2020, 7:42 pm Patricia Dowling, < patricia.dowl...@oireachtas.ie> wrote: > > I wonder if anyone in the community can steer me on this basic SQL query? > I am a

Re: [Koha] SQL help please

I think you will find the data required in items.itemnotes_nonpublic so your WHERE clause might be: WHERE items.itemnotes_nonpublic LIKE '%Added to LA%' Use the LIKE comparison in case there is anything else in the notes. On 03/06/2020 05:53, Ketan Kulkarni wrote: You will have to use the

Re: [Koha] SQL help please

You will have to use the function ExtractValue() for extracting the public note. Once you do that, just download the CSV and sort that column in a spreadsheet application. Should do the trick. On Wed, Jun 3, 2020 at 10:18 AM Kerrie Stevens wrote: > Hi All, > > I want to be able to run a report

[Koha] SQL help please

Hi All, I want to be able to run a report that pulls out bib number, title, author & barcode for resources that have "Added to LA" in the 'x-non-public note' field of the item record. That last bit is the bit I'm not sure how to do...? Thank you for any assistance you may provide... Kerrie

Re: [Koha] SQL help?

Hi Kerrie, The workflow that we've developed utilizes patron lists. When the list of prospective graduates is prepared by the registrar's office, I put them all into a patron list. Yes, this involves looking up individual students by name, but I only have to do this once. After they are in the

[Koha] SQL Help

Can someone suggest report for getting accession number with a range from and to -- Assistant Librarian, ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] SQL help?

: Mittwoch, 27. Mai 2020 11:00 An: Kerrie Stevens Cc: koha@lists.katipo.co.nz Betreff: Re: [Koha] SQL help? Hello Kerrie, I am not sure this is what you want, but if you create a SQL report with this query you will see the patrons with outstanding loans: SELECT b.borrowernumber, concat(b.surname

Re: [Koha] SQL help?

Hello Kerrie, I am not sure this is what you want, but if you create a SQL report with this query you will see the patrons with outstanding loans: SELECT b.borrowernumber, concat(b.surname, ' ', b.firstname), count(*) FROM issues LEFT JOIN borrowers b on b.borrowernumber=issues.borrowernumber

Re: [Koha] SQL help?

Hi Mysql does not have the ability to read info from any other place than its own tables. You can, however write an script in any language you know to loop through your borrowers file and check if they owe something. This shall be done at server level, not koha. Regards, Alvaro

[Koha] SQL help?

Every year, we need to confirm graduating students have no outstanding loans so they can graduate. And every year I think I must ask for some help in doing it more efficiently than searching borrower numbers one by one... I'd like to be able to copy & paste a number of borrower numbers into

Re: [Koha] SQL help

On Wed, Dec 11, 2019 at 5:53 PM Kerrie Stevens wrote: > We have version 18.11.11 and I’ve looked through the reports library but > can’t see anything that looks similar to be tweaked into what I need. > Try this, for year 2019 SELECT month, day, suc+uns AS total, suc AS succesful, uns AS

Re: [Koha] SQL help

@lists.katipo.co.nz Subject: Re: [Koha] SQL help Kerrie, Which version of Koha are you running? You would likely need to modify the following report found on the Koha Reports Library <https://wiki.koha-community.org/wiki/SQL_Reports_Library> to meet your need. https://wiki.koha-community.or

Re: [Koha] SQL help

‘Successful’ would be searches that retrieved results > Thanks, > > > Kerrie Stevens > > > From: Coehoorn, Joel > Sent: Wednesday, 11 December 2019 3:54 PM > To: Kerrie Stevens > Subject: Re: [Koha] SQL help > > Define "unsuccessful" > &

Re: [Koha] SQL help

‘Unsuccessful’ would be a search where no search results were returned. ‘Successful’ would be searches that retrieved results Thanks, Kerrie Stevens From: Coehoorn, Joel Sent: Wednesday, 11 December 2019 3:54 PM To: Kerrie Stevens Subject: Re: [Koha] SQL help Define "unsuccessful&quo

[Koha] SQL help

Our overall library collection is currently split between 2 different catalogues. The other catalogue can produce a report showing the number of opac searches (total, successful and unsuccessful) for each day of each month for the year. Can anyone help with how I can replicate that in Koha?

[Koha] SQL help: find biblios with duplicate subject headings

Hello, I’m trying to figure out how to find biblios with duplicate subject headings. For example, this record that has multiple instances of “‡aAfrican Americans‡xSuffrage” and other headings. This is one case where Koha’s ignorance of the delimiters helps me out! 650 _ 0 ‡aAfrican

Re: [Koha] SQL help please

Hi Kerrie, You haven't joined the Items table. So: JOIN items ON (biblio_metadata.biblionumber = items.biblionumber) HTH, Bob Calyx On 12/06/18 08:43, Kerrie Stevens wrote: I have the following report which almost does what I need but I can't quite get it there. I want to be able get a list

[Koha] SQL help please

I have the following report which almost does what I need but I can't quite get it there. I want to be able get a list of titles and the Dewey from 082$a but I also want to be able to select for which collection I want the results - that is where I'm getting stuck. I can select the collection

Re: [Koha] SQL help - Bulk Card Number modification

Hi Satish, maybe this piece of SQL code could help you... Please adjust the parameters @prefix and @offset. In the case you're satisfied with results, use the formulas in your UPDATE command. Regards, Radek select * FROM (     SELECT         @prefix:="4GH" as cardnumber,         @offset:=17

Re: [Koha] SQL help - Bulk Card Number modification

Dear Ramakant, Yes, I did the same way for modification of a single cardnumber. but how to do it for bulk? and also data is in excel. with thanks satish ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz

Re: [Koha] SQL help - Bulk Card Number modification

Dear Satish, With the help of sql query you will resolve your problem. Make your data in excel sheet then use below query for the same and resolve your query in bulk e.g. update borrowers set cardnumber = '4GH17ME18' where cardnumber = '17ME01'; Regards,

Re: [Koha] SQL help - Bulk Card Number modification

Hi the easiest way is to create a temporary table in Koha where you store the old and the new cardnumber and with a sql statement update the borrowers.cardnumber with the new value Kindest Regards 2018-01-18 12:57 GMT+01:00 SATISH : > Hi Pedro, > > Thank you for the

Re: [Koha] SQL help - Bulk Card Number modification

Hi Pedro, Thank you for the reply. I try to make my question little clear now, I am not referring to 'borrowernumber' (assigned by Koha) instead referring to 'cardumber' Temporary cardnumber does not prefix with TMP ( now, I understand it's importance) Current patron "cardnumber " designed

Re: [Koha] SQL help - Bulk Card Number modification

Hello Satish, I'm not sure I fully understand your situation but I'll try to provide help based on what I could figure out: *We use, students' register number as card number for circulation. Andregarding bulk 'card number' modifications :* Does this mean you use the patrons' borrowernumber as

[Koha] SQL help - Bulk Card Number modification

Hi, With Koha 16.11.11 / Package Installation/ Ubuntu 14.04 Desktop with MySQL - mysql Ver 14.14 Distrib 5.5.57 We use, students' register number as card number for circulation. And regarding bulk 'card number' modifications : I have a situation, where, first semester borrowers will only get

Re: [Koha] SQL help

Excellent, that works! Thank you 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

Re: [Koha] SQL help

Excerpts from Elaine Bradtke's message of 2018-01-09 11:06:16 -0800: > "Unknown column 'place' in 'field list'" > Here's the report - what field name do I need to replace place?: > select > b.biblionumber, author, title, > ExtractValue(metadata, '//datafield[@tag="245"]/subfield[@code="b"]')

Re: [Koha] SQL help

Hi Elaine, Looks like you are looking for this query: SELECT b.biblionumber, b.author, b.title, ExtractValue(bm.metadata, '//datafield[@tag="245"]/subfield[@code="b"]') as subtitle, ExtractValue(bm.metadata, '//datafield[@tag="250"]/subfield[@code="a"]') as edition, bi.place,

[Koha] SQL help

I've been going through our reports, and updating them. I've fixed all the old marcxml issues. But I have a new error message with a report that used to work: "Unknown column 'place' in 'field list'" Here's the report - what field name do I need to replace place?: select b.biblionumber, author,

Re: [Koha] SQL help please

This is awesome, Nick. I'm bookmarking this for future reference, thanks a lot! On 13 December 2017 at 15:27, Nick Clemens wrote: > This is a quite unfinished stub, but it aims to allow for letting you input > a list into a report: >

Re: [Koha] SQL help please

This is a quite unfinished stub, but it aims to allow for letting you input a list into a report: https://github.com/bywatersolutions/koha-reports-plus On Mon, Dec 11, 2017 at 8:20 PM DHD.KOHA wrote: > what version of KOHA ? > > > > On 11/12/2017 02:18 πμ, Kerrie Stevens

Re: [Koha] SQL help please

what version of KOHA ? On 11/12/2017 02:18 πμ, Kerrie Stevens wrote: Is there a way to write an SQL report where you can input a number of item barcodes (as you do when using batch modify or delete) that results in a table showing title, author, call number etc...? Thanks for any help you

Re: [Koha] SQL help please

Excerpts from Pedro Amorim's message of 2017-12-11 14:18:08 -0100: > This should work: > > Select title, author, i.itemcallnumber from biblio b > Left join biblioitems bi on b.biblionumber=bi.biblionumber > Left join items i on bi.biblionumber=i.biblionumber > where i.barcode IN ("BARCODE1",

Re: [Koha] SQL help please

Hello Kerrie, This should work: Select title, author, i.itemcallnumber from biblio b Left join biblioitems bi on b.biblionumber=bi.biblionumber Left join items i on bi.biblionumber=i.biblionumber where i.barcode IN ("BARCODE1", "BARCODE2", "BARCODE3"); GL, Pedro Amorim On 11 December 2017 at

Re: [Koha] SQL help please

And Barcodes can be searched with wild cards '%' or '_' eg: 45% results in 45, 451,452,453..459, 4500 this would help you to some extent. ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz

Re: [Koha] SQL help please

Hi Kerrie, I can only find single barcode search that you could find it in SQL Library..here.. https://wiki.koha-community.org/wiki/SQL_Reports_Library#Barcode_Search_Report And somebody could help you in enhancing this sql for multiple barcode search. Regards Satish Librarian Govt.

[Koha] SQL help please

Is there a way to write an SQL report where you can input a number of item barcodes (as you do when using batch modify or delete) that results in a table showing title, author, call number etc...? Thanks for any help you may provide! Kerrie Stevens AALIA(CP), MAppSc(Lib)(CSU),

Re: [Koha] SQL help please

Hi Kerry, you are right, I think this one should be better: SELECT i.itemnumber, i.biblionumber, b.title, b.author, i.itemcallnumber, i.barcode, i.homebranch FROM items i LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) WHERE homebranch = "QLD" AND i.biblionumber not in (select distinct

Re: [Koha] SQL help please

: koha@lists.katipo.co.nz Subject: Aw: [Koha] SQL help please Hi Kerrie, I think DISTINCT is not quite what you want here, but this seems to work for one of our libraries with multiple branches: SELECT i.itemnumber, b.title, b.author, i.itemcallnumber, i.barcode, i.homebranch FROM items i LEF

Re: [Koha] SQL help please

WHERE items.homebranch != 'QLD' ) ORDER BY items.itemcallnu' at line 10 Kerrie Stevens From: Barton Chittenden [mailto:bar...@bywatersolutions.com] Sent: Wednesday, April 12, 2017 4:59 PM To: Kerrie Stevens <kstev...@harvest.edu.au> Cc: koha@lists.katipo.co.nz Subject: Re: [Koha] SQL help

Re: [Koha] SQL help please

Kerrie, Just to be sure that I'm understanding you here ... you wan to list the item if it's held at 'QLD', but *not* if it's held at 'QLD' but also held at another branch? I think this will work: SELECT items.itemnumber, biblio.title, biblio.author, items.itemcallnumber,

[Koha] SQL help please

I have this report that lists all the items in our QLD library but I want to restrict it to items ONLY held in our QLD library and not another branch... I don't know where to put the DISTINCT tag? Please advise: SELECT items.itemnumber, biblio.title, biblio.author, items.itemcallnumber,

Re: [Koha] SQL Help - Daily (on Monthly basis) & Monthly (on Yearly basis) Transactions Counts (Circulation)

On Mar 10, 2017 23:06, "SATISH" wrote: Hi, Please find the SQL reports wiki page is updated today Excellent! Thanks for adding these! --Barton ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz

Re: [Koha] SQL Help - Daily (on Monthly basis) & Monthly (on Yearly basis) Transactions Counts (Circulation)

Hi, Please find the SQL reports wiki page is updated today for (1) Month wise circulation report (counts) for a specific year & (2) Date wise daily circulation report

Re: [Koha] SQL Help - Daily (on Monthly basis) & Monthly (on Yearly basis) Transactions Counts (Circulation)

HI I have compiled Date wise daily transactions/circulation report which I had asked in my previous mails. Thanks to Barton Chittenden and Mark Alexander for quick response. Please also advice me, how to add this in the sql wiki library. 1. Date wise daily Transactions (Issue, Renewal, Return)

Re: [Koha] SQL Help - Daily (on Monthly basis) & Monthly (on Yearly basis) Transactions Counts (Circulation)

On Mon, Mar 6, 2017 at 8:25 AM, Mark Alexander wrote: > > Pretty close! Just some misplaced parens: > > SELECT > monthname(datetime), > SUM( IF(type = 'issue', 1, 0 )) as issues, > SUM( IF(type = 'renew', 1, 0 )) as renewals, > SUM( IF(type = 'return',

Re: [Koha] SQL Help - Daily (on Monthly basis) & Monthly (on Yearly basis) Transactions Counts (Circulation)

Excerpts from Barton Chittenden's message of 2017-03-06 08:08:04 -0500: > I'll do the 'month, by year'; the 'day by month' one will follow the same > pattern. > > SELECT > monthname(datetime), > SUM( IF(type = 'issue'), 1, 0 ) as issues, > SUM( IF(type = 'renew'), 1, 0 ) as renewals,

Re: [Koha] SQL Help - Daily (on Monthly basis) & Monthly (on Yearly basis) Transactions Counts (Circulation)

Statish, I'll do the 'month, by year'; the 'day by month' one will follow the same pattern. SELECT monthname(datetime), SUM( IF(type = 'issue'), 1, 0 ) as issues, SUM( IF(type = 'renew'), 1, 0 ) as renewals, SUM( IF(type = 'return'), 1, 0 ) as returns FROM statistics WHERE

[Koha] SQL Help - Daily (on Monthly basis) & Monthly (on Yearly basis) Transactions Counts (Circulation)

Hi, With Koha 16.5/Package Installation/ Ubuntu 14.04 Desktop / Can you please help me to prepare the Circulation report (SQL), which contain. 1* Daily and 2* Monthly Counts for Circulation. 1* Counts- Daily transactions/circulation report for a Month:

Re: [Koha] SQL help request

Hi, Please check following: SELECT barcode, itemcallnumber,biblionumber FROM items WHERE datelastseen < CAST(<> AS DATETIME) AND homebranch = <> AND itemnumber NOT IN (SELECT itemnumber FROM issues) ORDER BY barcode Vikram Zadgaonkar On Thu, Nov 10, 2016 at 9:41 AM, Kerrie Stevens

[Koha] SQL help request

How would I insert another column in the results table of this report to show the biblio number as well? I keep getting syntax errors with everything I try. SELECT barcode, itemcallnumber FROM items WHERE datelastseen < CAST(<> AS DATETIME) AND homebranch = <> AND itemnumber NOT IN

Re: [Koha] SQL help - Retrieve itemnumbers based on local subject keyword

24 x338 > www.rcmusic.ca > > > -Original Message- > From: Koha [mailto:koha-boun...@lists.katipo.co.nz] On Behalf Of Craig > Butosi > Sent: October-21-16 12:54 PM > To: koha@lists.katipo.co.nz > Subject: [Koha] SQL help - Retrieve itemnumbers based on local subject >

Re: [Koha] SQL Help - Add barcode column to SQL query that searches subject keywords

Hi Craig Butosi, There will be many copies for each biblionumber so you want to list out all the barcodes related to that biblionumber or biblioitemnumber? If you want every barcode related to that biblioitemnumber then the query will be as below: SELECT CONCAT('',items.biblionumber,'') AS

[Koha] SQL Help - Add barcode column to SQL query that searches subject keywords

I think will work for you: SELECT CONCAT('',biblionumber,'') AS bibnumber, lcsh,* barcode* FROM (SELECT biblionumber, *barcode*, EXTRACTVALUE(marcxml,'//datafield[@tag="690"]/subfield[@code>="a"]') AS lcsh FROM biblioitems LEFT JOIN items USING(biblionumber)) AS subjects WHERE lcsh LIKE <> >Hi

[Koha] SQL Help - Add barcode column to SQL query that searches subject keywords

Hi all, I'm trying to get the barcodes column from the items database to display on the results page after running the following SQL query: SELECT CONCAT('',biblionumber,'') AS bibnumber, lcsh FROM (SELECT biblionumber, ExtractValue(marcxml,'//datafield[@tag="690"]/subfield[@code>="a"]') AS

Re: [Koha] SQL help please

Hi-- I believe that Nicole Engard wrote the following report for me. It seems to work. SELECT biblio.biblionumber, biblio.title, biblio.author FROM biblioitems JOIN biblio ON ( biblioitems.biblionumber = biblio.biblionumber ) WHERE ExtractValue( marcxml,

Re: [Koha] SQL help please

:-P El mié., 10 ago. 2016 a las 10:56, Jonathan Druart (< jonathan.dru...@bugs.koha-community.org>) escribió: > Erk indeed! Thanks Barton. > > I have the habit of looking at 995 for items ;) > > 2016-08-10 14:32 GMT+01:00 Barton Chittenden >: > > Jonathan's use of

Re: [Koha] SQL help please

Erk indeed! Thanks Barton. I have the habit of looking at 995 for items ;) 2016-08-10 14:32 GMT+01:00 Barton Chittenden : > Jonathan's use of ExtractValue is correct, but item data isn't stored in > biblioitems.marcxml -- as a matter of fact, it's not stored in marc

Re: [Koha] SQL help please

Jonathan's use of ExtractValue is correct, but item data isn't stored in biblioitems.marcxml -- as a matter of fact, it's not stored in marc at all... it's *always* generated on the fly. You can query against items.itype instead. --Barton On Tue, Aug 9, 2016 at 4:18 AM, Jonathan Druart <

Re: [Koha] SQL help please

Hi Kerrie, I'd say that the following query returns what you want: SELECT biblioitems.biblionumber, marcxml from biblioitems where ExtractValue( marcxml, '//datafield[@tag="942"]/subfield[@code="c"]' ) = ""; Regards, Jonathan 2016-08-09 7:36 GMT+01:00 Kerrie Stevens : >

[Koha] SQL help please

Hi Everyone, I'm trying to get a report to identify which bib records in my Koha do not have anything in the 942 $c Koha Item Type marc field. I tried to tweak a report from the report library, but it doesn't appear to work correctly as some of the results do have things in the 942$c field.

[Koha] SQL help needed: borrower info needed in 2 contexts

Here's one for you SQL hotshots. We took a report from the library and modified it slightly. It shows all items that are checked out and also have holds on them. Here it is: SELECT b.title, i.itemcallnumber AS Call Number, i.barcode AS Barcode, date(r.timestamp) AS Hold Date,