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 | [email protected]<mailto:[email protected]>
| library.dmlibrary.org.au
________________________________
From: Koha <[email protected]> on behalf of Carlos Lopez
<[email protected]>
Sent: Tuesday, 25 August 2020 10:28 AM
To: koha <[email protected]>
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 | [email protected]<mailto:[email protected]>
| library.dmlibrary.org.au
_______________________________________________
Koha mailing list http://koha-community.org
[email protected]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________
Koha mailing list http://koha-community.org
[email protected]
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: [email protected]
_______________________________________________
Koha mailing list http://koha-community.org
[email protected]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
