[Koha] SQL help please

[Kerrie Stevens]

I have the following report which almost does what I need but I can't quite get it there.

I want to be able get a list of titles and the Dewey from 082$a but I also want to be able to select for which collection I want the results - that is where I'm getting stuck.
I can select the collection when I run the report, but I get the following error so there is something else missing...
Error: The following error was encountered: The database returned the following error:  Unknown column 'items.ccode' in 'where clause'

Here is my report so far:

SELECT biblio.biblionumber, biblio.title, biblio.author, concat (ExtractValue( metadata, '//datafield[@tag="082"]/subfield[@code="a"]' ), " ", ExtractValue( metadata, '//datafield[@tag="082"]/subfield[@code="b"]' )) as Dewey
FROM biblio_metadata
JOIN biblio ON ( biblio_metadata.biblionumber = biblio.biblionumber )
WHERE items.ccode=<<Collection|CCODE>>
ORDER BY biblio.title ASC

It works if I remove the WHERE line, but then I can't select which collection.

Thanks for any help you may provide!


Kerrie Stevens  AALIA(CP), MAppSci(LibMgt), BBus(Info&LibMgt)
Melbourne Campus - Librarian

kerrie.stevens at ac.edu.au | ac.edu.au<http://ac.edu.au/>
P +61 3 8799 1155 | F +61 3 8799 1199
1 Keith Campbell Court, Scoresby VIC 3179 Australia

This email and any attachments may contain privileged and confidential information and are intended for the named addressee only. If you have received this email in error, please notify the sender and delete this email immediately. Any confidentiality, privilege or copyright is not waived or lost because this email has been sent to you in error. It is your responsibility to check this email and any attachments for viruses.


<p>
Scanned by the Alphacrucis Netbox from 
<a href="http://cyberhound.com/">CyberHound</a>