[Koha] Subject Wise Report

[Michael Kuhn]

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 at adminkuhn.ch · W www.adminkuhn.ch