Re: [Koha] Subject Wise Report
Please reply to the mailing list so that others can participate. On Wed, Apr 17, 2019 at 12:25 AM Rizwan Majeed <rizwanmajeed@ue.edu.pk> wrote:
this is the query
SELECT * FROM(SELECT items.dateaccessioned, items.barcode, items.itemcallnumber, biblio.title, biblio.author, biblioitems.publishercode, (SELECT ExtractValue(biblioitems.marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]')) AS Subject FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) WHERE items.dateaccessioned BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>) AS t WHERE Subject LIKE concat('%',<<Subject>>,'%') ORDER BY dateaccessioned DESC
Response to: Please reply to the mailing list so that others can participate. I am new and have two questions. (1) Can we back up in MARC21 format? If yes, how? (2) Can we restore the data from above mentioned MARC21 format? If yes, how? I ask the questions since we have an old koha, maybe in version 1, running on Window XP and the computer is totally dead. If the above can be done, it is totally independent of the machine and database used. At least for small library, it should not take too much time to use the backup this way and recover the data no matter what database is used. By the way, has anyone the idea to recover the old data in our case? Thanks. On Wed, Apr 17, 2019 at 4:31 AM Owen Leonard <oleonard@myacpl.org> wrote: Please reply to the mailing list so that others can participate.
On Wed, Apr 17, 2019 at 12:25 AM Rizwan Majeed <rizwanmajeed@ue.edu.pk> wrote:
this is the query
SELECT * FROM(SELECT items.dateaccessioned, items.barcode, items.itemcallnumber, biblio.title, biblio.author, biblioitems.publishercode, (SELECT
ExtractValue(biblioitems.marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]'))
AS Subject FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) WHERE items.dateaccessioned BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>) AS t WHERE Subject LIKE concat('%',<<Subject>>,'%') ORDER BY dateaccessioned DESC
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
You can back the holdings and biblio records in marc21 format. You cannot backup circulation history, patron data, serials data, acquisition settings, authorities, system settings, etc using just marc21. Joel Coehoorn Director of Information Technology 402.363.5603 *jcoehoorn@york.edu <jcoehoorn@york.edu>* *Please contact helpdesk@york.edu <helpdesk@york.edu> for technical assistance.* The mission of York College is to transform lives through Christ-centered education and to equip students for lifelong service to God, family, and society On Wed, Apr 17, 2019 at 12:13 PM Tsu-Shuan Chang <tschang.ucdavis@gmail.com> wrote:
Response to: Please reply to the mailing list so that others can participate.
I am new and have two questions.
(1) Can we back up in MARC21 format? If yes, how?
(2) Can we restore the data from above mentioned MARC21 format? If yes, how?
I ask the questions since we have an old koha, maybe in version 1, running on Window XP and the computer is totally dead. If the above can be done, it is totally independent of the machine and database used. At least for small library, it should not take too much time to use the backup this way and recover the data no matter what database is used.
By the way, has anyone the idea to recover the old data in our case?
Thanks.
On Wed, Apr 17, 2019 at 4:31 AM Owen Leonard <oleonard@myacpl.org> wrote:
Please reply to the mailing list so that others can participate.
On Wed, Apr 17, 2019 at 12:25 AM Rizwan Majeed <rizwanmajeed@ue.edu.pk> wrote:
this is the query
SELECT * FROM(SELECT items.dateaccessioned, items.barcode, items.itemcallnumber, biblio.title, biblio.author, biblioitems.publishercode, (SELECT
ExtractValue(biblioitems.marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]'))
AS Subject FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) WHERE items.dateaccessioned BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>) AS t WHERE Subject LIKE concat('%',<<Subject>>,'%') ORDER BY dateaccessioned DESC
_______________________________________________ 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
Hi Joel, Regarding your email, our church is not so advanced. Basically, we need to catalogue items including books, DVDs, youth books, etc. We can self check out and just leave a borrowing card. Of course, we can lose some books in this way, but not too many. I'll try to put some thinking starting with an * on the topics I asked. Response to: Please reply to the mailing list so that others can participate. I am new and have two questions. (1) Can we back up in MARC21 format? If yes, how? * This is the data we entered. There are two parts for each item (book for example). Part 1 is all the information regarding the book in MARC21 format. Part 2 is to add the item including the access number. If we can do back up by including all the information we entered, we do not lose any information. * It seems to me that this can be done just by modifying the programs a little bit for someone knows koha and C++ well. Since MAC21 information is on screen already. We can use the same program by just putting the output into a text file with delimter for each item and going though all the records. Although this may not be the most efficient way, it seems feasible. (2) Can we restore the data from above mentioned MARC21 format? If yes, how? * To use the back up in (1), it seems that we can write programs which goes through each record and enter the data again by computer directly. This means that we can read the back up file for each book, and enter that information by bypassing the graphical input on the screen. By this way, no matter what database we use, we can recover all data this way. Of course, this may be slow. But for small enough library, it should not take a very long time. This may not be efficient, but should be able to be done quickly. With (1) and (2) together, such a back up should be universal since we re-enter ther same informattion. # Unfortunately, I do not know C++ and only a little on C. I cannot do this myself. If the new version includes this feature, it should solve problems in the future like what we encountered. I ask the questions since we have an old koha, maybe in version 1, running on Window XP and the computer is totally dead. If the above can be done, it is totally independent of the machine and database used. At least for small library, it should not take too much time to use the backup this way and recover the data no matter what database is used. Thanks. Tsu-Shuan On Thu, Apr 18, 2019 at 8:13 PM Coehoorn, Joel <jcoehoorn@york.edu> wrote:
You can back the holdings and biblio records in marc21 format.
You cannot backup circulation history, patron data, serials data, acquisition settings, authorities, system settings, etc using just marc21.
Joel Coehoorn Director of Information Technology 402.363.5603 *jcoehoorn@york.edu <jcoehoorn@york.edu>*
*Please contact helpdesk@york.edu <helpdesk@york.edu> for technical assistance.*
The mission of York College is to transform lives through Christ-centered education and to equip students for lifelong service to God, family, and society
On Wed, Apr 17, 2019 at 12:13 PM Tsu-Shuan Chang < tschang.ucdavis@gmail.com> wrote:
Response to: Please reply to the mailing list so that others can participate.
I am new and have two questions.
(1) Can we back up in MARC21 format? If yes, how?
(2) Can we restore the data from above mentioned MARC21 format? If yes, how?
I ask the questions since we have an old koha, maybe in version 1, running on Window XP and the computer is totally dead. If the above can be done, it is totally independent of the machine and database used. At least for small library, it should not take too much time to use the backup this way and recover the data no matter what database is used.
By the way, has anyone the idea to recover the old data in our case?
Thanks.
On Wed, Apr 17, 2019 at 4:31 AM Owen Leonard <oleonard@myacpl.org> wrote:
Please reply to the mailing list so that others can participate.
On Wed, Apr 17, 2019 at 12:25 AM Rizwan Majeed <rizwanmajeed@ue.edu.pk> wrote:
this is the query
SELECT * FROM(SELECT items.dateaccessioned, items.barcode, items.itemcallnumber, biblio.title, biblio.author, biblioitems.publishercode, (SELECT
ExtractValue(biblioitems.marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]'))
AS Subject FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) WHERE items.dateaccessioned BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>) AS t WHERE Subject LIKE concat('%',<<Subject>>,'%') ORDER BY dateaccessioned DESC
_______________________________________________ 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
Hi Rizwan You wrote:
I am try to write a query in which all fields from items, biblio, bilioitems tables are required to be shown. But I am facing difficulties to show subject heading and classification number. I also tried extractvalue() function to show subject heading but error displayed. Has anyone the solution of this issue?
this is the query
SELECT * FROM(SELECT items.dateaccessioned, items.barcode, items.itemcallnumber, biblio.title, biblio.author, biblioitems.publishercode, (SELECT ExtractValue(biblioitems.marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]')) AS Subject FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) WHERE items.dateaccessioned BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>) AS t WHERE Subject LIKE concat('%',<<Subject>>,'%') ORDER BY dateaccessioned DESC
You wrote you need "a query in which all fields from items, biblio, bilioitems tables are required to be shown". You can achieve this with a query as follows (old SQL syntax, I don't like the new one): SELECT * FROM biblio, biblioitems, items WHERE biblioitems.biblionumber=biblio.biblionumber AND items.biblioitemnumber=biblioitems.biblioitemnumber I didn't try to find out about the "difficulties to show subject heading and classification number". However, there are two things at least: 1. You cannot have a query with two FROM clauses like "SELECT * FROM (SELECT ... SELECT (...)) AS Subject FROM items - this is definitely incorrect syntax, whatever you try to achieve. 2. Also it is not possible to have "AS t" in your first where clause. I recommend to start with a simple query, then build it up, always respecting the correct SQL syntax. Hope this helps. Best wishes: Michael -- Geschäftsführer · Diplombibliothekar BBS, Informatiker eidg. Fachausweis Admin Kuhn GmbH · Pappelstrasse 20 · 4123 Allschwil · Schweiz T 0041 (0)61 261 55 61 · E mik@adminkuhn.ch · W www.adminkuhn.ch
participants (4)
-
Coehoorn, Joel -
Michael Kuhn -
Owen Leonard -
Tsu-Shuan Chang