Hi Diego, The message is correct and Derby is behaving as it should.
If you are using GROUP BY, you are aggregating a number of rows into a smaller number of rows, so instead of e.g. 100 rows all of which contain the same clienti.provincia, you aggregate all 100 rows into a single row. In order to do this, you need to either propagate the information you want (the GROUP BY items) or aggregate them using one of the aggregate functions MIN, MAX, AVG, SUM, etc.
So you could e.g. aggregate clienti.birthday to find the youngest and oldest clienti in each provincia using SELECT provincia, MIN (clienti.birthday), MAX(clienti.birthday) FROM clienti GROUP BY clienti.provincia but this doesn't sound like what you are trying to do.
From the looks of the query, you don't want the GROUP BY clause at all...
Craig On Feb 5, 2007, at 1:31 PM, Diego Zanga wrote:
lo, I'm using a query developed for mysql. actually it works with derby only IF there is NO <group by clienti.id> ------ select clienti.id , clienti.cognome , clienti.nome , clienti.display , clienti.idtitolo, wk7.descrizione , clienti.indirizzo , clienti.citta , clienti.provincia , clienti.cap , clienti.idnazione, wk13.description , clienti.pi , clienti.codfis , clienti.idlegalerappresentante, wk17.display , clienti.birthday , clienti.born_prov , clienti.born_city, wk21.codicearchivio , clienti.note , clienti.phone1 , clienti.phone2 , clienti.phone3 , clienti.home_phone , clienti.fax , clienti.mobile1 , clienti.mobile2 , clienti.email1 , clienti.email2 , clienti.email_pec , clienti.fido , clienti.esposizione , clienti.tariffa_oraria from clienti left outer join subcliente on clienti.id = subcliente.idcliente left outer join pratiche as wk21 on wk21.id = subcliente.idpratica left join legale_rappresentante_cliente as wk17 on wk17.id = clienti.idlegalerappresentante left join nazione as wk13 on wk13.id = clienti.idnazione , titoli as wk7 group by clienti.id order by cognome,nome -------- the query is <developed> by an engine, and is one of the smallest :OP the error is: java.sql.SQLException: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions. I dont understand how the select could hold one invalid expression if without the <group by> it works: there's even no aggregate expression, so no <invalid> ones.... any idea? cya, thx -- Diego Zanga ------------------------------ http://www.eLawOffice.it http://www.blogstudiolegale.eu http://www.javablog.eu Skype NAARANI
Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp!
smime.p7s
Description: S/MIME cryptographic signature
