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 m...@adminkuhn.ch · W www.adminkuhn.ch
_______________________________________________
Koha mailing list http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha
