Help with SQL script please
Hi everyone I'm trying to produce a list of itemnumbers for a list of bibliographic records with biblionumbers where subfield $9 in the 100 tag is in a list of authids for authorities with the value "Jesuit" in 373$a (where trying to populate a particular ccode with a set of items that we know will be relevant for that ccode). I have the following SQL query which SEEMS to run but brings up no results (even though I know that we have authority records with 373$aJesuit linked from bibliographic records with items attached): ---- SELECT i.itemnumber, b.title, b.author FROM items i LEFT JOIN biblio b ON b.biblionumber = i.biblionumber WHERE i.biblionumber IN (SELECT biblionumber FROM biblio_metadata WHERE ExtractValue(metadata, '//datafield[tag=100]/subfield[@code="9"]') IN (SELECT authid FROM auth_header WHERE ExtractValue(marcxml, '//datafield[@tag="373"]/subfield[@code="a"]') LIKE "Jesuit%")) ---- Can anyone spot where I've gone wrong? Or perhaps point me to an easier way of doing this? With kind regards from the Dalton McCaughey Library Team Carlos Lopez Dalton McCaughey Library | 29 College Crescent, Parkville, VICTORIA 3052 Ph: 03 9340 8888 ext.1 | library@dml.vic.edu.au<mailto:library@dml.vic.edu.au> | library.dmlibrary.org.au
Hi Gustavo Not sure where the error might be but for troubleshooting you can split your query from the inside out and see where it stops working. Also, if you do know the values for Jesuit%, you might replace that query with a list of values or a specific value for testing purposes. A) Test SELECT authid FROM auth_header WHERE ExtractValue(marcxml, '//datafield[@tag="373"]/subfield[@code="a"]') LIKE "Jesuit%") B) SELECT biblionumber FROM biblio_metadata WHERE ExtractValue(metadata, '//datafield[tag=100]/subfield[@code="9"]') IN (a list of known values or the results of A) C) SELECT i.itemnumber, b.title, b.author FROM items i LEFT JOIN biblio b ON b.biblionumber = i.biblionumber WHERE i.biblionumber IN (list) (you can use the values got in B) Hope helps 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 lun. 24 août 2020 à 19:29, Carlos Lopez <clopez@dml.vic.edu.au> a écrit :
Hi everyone
I'm trying to produce a list of itemnumbers for a list of bibliographic records with biblionumbers where subfield $9 in the 100 tag is in a list of authids for authorities with the value "Jesuit" in 373$a (where trying to populate a particular ccode with a set of items that we know will be relevant for that ccode).
I have the following SQL query which SEEMS to run but brings up no results (even though I know that we have authority records with 373$aJesuit linked from bibliographic records with items attached):
---- SELECT i.itemnumber, b.title, b.author FROM items i LEFT JOIN biblio b ON b.biblionumber = i.biblionumber WHERE i.biblionumber IN (SELECT biblionumber FROM biblio_metadata WHERE ExtractValue(metadata, '//datafield[tag=100]/subfield[@code="9"]') IN (SELECT authid FROM auth_header WHERE ExtractValue(marcxml, '//datafield[@tag="373"]/subfield[@code="a"]') LIKE "Jesuit%")) ----
Can anyone spot where I've gone wrong? Or perhaps point me to an easier way of doing this?
With kind regards from the Dalton McCaughey Library Team
Carlos Lopez
Dalton McCaughey Library | 29 College Crescent, Parkville, VICTORIA 3052
Ph: 03 9340 8888 ext.1 | library@dml.vic.edu.au<mailto: library@dml.vic.edu.au> | library.dmlibrary.org.au
_______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Hi all I sent this through yesterday (see below) and it's really annoying me: The query seems to bring up 0 results when I'm trying to compare authids (UNSIGNED) to an ExtractValue string from biblio_metadata.metadata (LONGTEXT?). Is this even possible? If so, how do I go about CONVERTing my authids so that they're the same data type as the ExtractValue strings? Or am I doing this the wrong way? Is there a better way for me to list a set of itemnumbers from a known set of authids? With kind regards from the Dalton McCaughey Library Team Carlos Lopez Dalton McCaughey Library | 29 College Crescent, Parkville, VICTORIA 3052 Ph: 03 9340 8888 ext.1 | library@dml.vic.edu.au<mailto:library@dml.vic.edu.au> | library.dmlibrary.org.au ________________________________ From: Koha <koha-bounces@lists.katipo.co.nz> on behalf of Carlos Lopez <clopez@dml.vic.edu.au> Sent: Tuesday, 25 August 2020 10:28 AM To: koha <koha@lists.katipo.co.nz> Subject: [Koha] Help with SQL script please Hi everyone I'm trying to produce a list of itemnumbers for a list of bibliographic records with biblionumbers where subfield $9 in the 100 tag is in a list of authids for authorities with the value "Jesuit" in 373$a (where trying to populate a particular ccode with a set of items that we know will be relevant for that ccode). I have the following SQL query which SEEMS to run but brings up no results (even though I know that we have authority records with 373$aJesuit linked from bibliographic records with items attached): ---- SELECT i.itemnumber, b.title, b.author FROM items i LEFT JOIN biblio b ON b.biblionumber = i.biblionumber WHERE i.biblionumber IN (SELECT biblionumber FROM biblio_metadata WHERE ExtractValue(metadata, '//datafield[tag=100]/subfield[@code="9"]') IN (SELECT authid FROM auth_header WHERE ExtractValue(marcxml, '//datafield[@tag="373"]/subfield[@code="a"]') LIKE "Jesuit%")) ---- Can anyone spot where I've gone wrong? Or perhaps point me to an easier way of doing this? With kind regards from the Dalton McCaughey Library Team Carlos Lopez Dalton McCaughey Library | 29 College Crescent, Parkville, VICTORIA 3052 Ph: 03 9340 8888 ext.1 | library@dml.vic.edu.au<mailto:library@dml.vic.edu.au> | library.dmlibrary.org.au _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Hi Carlos. Your SQL does not need type-casting. It is just missing an @ and a couple of " in the ExtractValue for the 100 tag. It should be: WHERE ExtractValue(metadata, '//datafield[@tag="100"]/subfield[@code="9"]') IN rather than WHERE ExtractValue(metadata, '//datafield[tag=100]/subfield[@code="9"]') IN Hoping this helps :-) On 26/08/2020 02:11, Carlos Lopez wrote:
Hi all
I sent this through yesterday (see below) and it's really annoying me: The query seems to bring up 0 results when I'm trying to compare authids (UNSIGNED) to an ExtractValue string from biblio_metadata.metadata (LONGTEXT?). Is this even possible? If so, how do I go about CONVERTing my authids so that they're the same data type as the ExtractValue strings?
Or am I doing this the wrong way? Is there a better way for me to list a set of itemnumbers from a known set of authids?
With kind regards from the Dalton McCaughey Library Team
Carlos Lopez
Dalton McCaughey Library | 29 College Crescent, Parkville, VICTORIA 3052
Ph: 03 9340 8888 ext.1 | library@dml.vic.edu.au<mailto:library@dml.vic.edu.au> | library.dmlibrary.org.au
________________________________ From: Koha <koha-bounces@lists.katipo.co.nz> on behalf of Carlos Lopez <clopez@dml.vic.edu.au> Sent: Tuesday, 25 August 2020 10:28 AM To: koha <koha@lists.katipo.co.nz> Subject: [Koha] Help with SQL script please
Hi everyone
I'm trying to produce a list of itemnumbers for a list of bibliographic records with biblionumbers where subfield $9 in the 100 tag is in a list of authids for authorities with the value "Jesuit" in 373$a (where trying to populate a particular ccode with a set of items that we know will be relevant for that ccode).
I have the following SQL query which SEEMS to run but brings up no results (even though I know that we have authority records with 373$aJesuit linked from bibliographic records with items attached):
---- SELECT i.itemnumber, b.title, b.author FROM items i LEFT JOIN biblio b ON b.biblionumber = i.biblionumber WHERE i.biblionumber IN (SELECT biblionumber FROM biblio_metadata WHERE ExtractValue(metadata, '//datafield[tag=100]/subfield[@code="9"]') IN (SELECT authid FROM auth_header WHERE ExtractValue(marcxml, '//datafield[@tag="373"]/subfield[@code="a"]') LIKE "Jesuit%")) ----
Can anyone spot where I've gone wrong? Or perhaps point me to an easier way of doing this?
With kind regards from the Dalton McCaughey Library Team
Carlos Lopez
Dalton McCaughey Library | 29 College Crescent, Parkville, VICTORIA 3052
Ph: 03 9340 8888 ext.1 | library@dml.vic.edu.au<mailto:library@dml.vic.edu.au> | library.dmlibrary.org.au
_______________________________________________
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
-- Ian Bays Director, PTFS Europe Limited Content Management and Library Solutions +44 (0) 7774 995297 (mobile) skype: ian.bays email: ian.bays@ptfs-europe.com
participants (3)
-
Alvaro Cornejo -
Carlos Lopez -
Ian Bays