[Koha] Help with SQL script please

[Ian Bays]

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 at dml.vic.edu.au<mailto:library at dml.vic.edu.au> | library.dmlibrary.org.au
>
>
> ________________________________
> From: Koha <koha-bounces at lists.katipo.co.nz> on behalf of Carlos Lopez <clopez at dml.vic.edu.au>
> Sent: Tuesday, 25 August 2020 10:28 AM
> To: koha <koha at 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 at dml.vic.edu.au<mailto:library at dml.vic.edu.au> | library.dmlibrary.org.au
>
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> Koha at 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 at ptfs-europe.com