From derby-user-return-6102-apmail-db-derby-user-archive=db.apache.org@db.apache.org Mon Feb 05 22:34:06 2007 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 98657 invoked from network); 5 Feb 2007 22:34:05 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 5 Feb 2007 22:34:05 -0000 Received: (qmail 76814 invoked by uid 500); 5 Feb 2007 22:34:10 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 76781 invoked by uid 500); 5 Feb 2007 22:34:10 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 76770 invoked by uid 99); 5 Feb 2007 22:34:10 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 05 Feb 2007 14:34:10 -0800 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests=UNPARSEABLE_RELAY X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: local policy) Received: from [192.18.42.249] (HELO nwk-ea-fw-1.sun.com) (192.18.42.249) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 05 Feb 2007 14:33:59 -0800 Received: from d1-sfbay-09.sun.com ([192.18.39.119]) by nwk-ea-fw-1.sun.com (8.13.6+Sun/8.12.9) with ESMTP id l15MXYO1012023 for ; Mon, 5 Feb 2007 14:33:35 -0800 (PST) Received: from conversion-daemon.d1-sfbay-09.sun.com by d1-sfbay-09.sun.com (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) id <0JD000301H7PC000@d1-sfbay-09.sun.com> (original mail from Craig.Russell@Sun.COM) for derby-user@db.apache.org; Mon, 05 Feb 2007 14:33:34 -0800 (PST) Received: from [129.145.133.119] by d1-sfbay-09.sun.com (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) with ESMTPSA id <0JD000CKJHBYXBA0@d1-sfbay-09.sun.com> for derby-user@db.apache.org; Mon, 05 Feb 2007 14:33:34 -0800 (PST) Date: Mon, 05 Feb 2007 14:33:33 -0800 From: Craig L Russell Subject: Re: Group By - problems In-reply-to: <3b2806290702051331u1fd4b289ya79b1f9bc181705e@mail.gmail.com> Sender: Craig.Russell@Sun.COM To: Derby Discussion Message-id: <5F8CB685-779C-4B59-8462-6853A3DF7FD3@SUN.com> MIME-version: 1.0 X-Mailer: Apple Mail (2.752.2) Content-type: multipart/signed; protocol="application/pkcs7-signature"; boundary=Apple-Mail-1--865914158; micalg=sha1 References: <3b2806290702051331u1fd4b289ya79b1f9bc181705e@mail.gmail.com> X-Virus-Checked: Checked by ClamAV on apache.org --Apple-Mail-1--865914158 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed 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 > ------ > 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 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 it works: there's even no aggregate expression, > so no 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:Craig.Russell@sun.com P.S. A good JDO? O, Gasp! --Apple-Mail-1--865914158 Content-Transfer-Encoding: base64 Content-Type: application/pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGUDCCAwkw ggJyoAMCAQICECpJVMO68ii+Xfsc1O1YYFIwDQYJKoZIhvcNAQEFBQAwYjELMAkGA1UEBhMCWkEx JTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQ ZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA2MTIwOTE5NTEwNVoXDTA3MTIwOTE5NTEw NVowbDEQMA4GA1UEBBMHUnVzc2VsbDEUMBIGA1UEKhMLQ3JhaWcgTGFpcmQxHDAaBgNVBAMTE0Ny YWlnIExhaXJkIFJ1c3NlbGwxJDAiBgkqhkiG9w0BCQEWFUNyYWlnLlJ1c3NlbGxAU3VuLkNPTTCC ASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMNAB4Ih+ShsCt89HzqIdwEx8L4o1UHiY6V7 16mrCedfd4Y0/uI7z9Zam8ysgEh+F7aDnQEKmEsVFN35G4nPMfLU6dZYkvADwUjbq82t/dJ3FDDg Q945nHHpqECZff/S/UMho9AFfj6PZvZBAlDCJAayb4RdKIlfuvPW9YcQStQ1IfVJcVuKnC0Q+tdc a4A7zn7IzLOQohO1lTc3hXSBigEIGiGYn6Ny0wmexfA3X1WsXekFx5czd+M4GjDjswn8CNoBmnBr jOTGK1mOsXR6GSRHnly2s9xTdE4qv9qimM+7C2yzMHbKcszV7OQoLsRsZKDh+6u9wYU+TrjcY4ym bA8CAwEAAaMyMDAwIAYDVR0RBBkwF4EVQ3JhaWcuUnVzc2VsbEBTdW4uQ09NMAwGA1UdEwEB/wQC MAAwDQYJKoZIhvcNAQEFBQADgYEAU/EpPDztnb55Fz7iGSVm1mYEVj5m2OQKTYG26POUAomCBRrt /CdBBvqYmcHUTpra0qLELHAQadYFl2v11iQkqwF5PPJs19oU/zA0m5qFnOMTAiCvel7IprIwA2r6 eJR9siaPwDRgVJ/Sj71dD+utwf+nRrNy0/7PMNK5y+ocsYQwggM/MIICqKADAgECAgENMA0GCSqG SIb3DQEBBQUAMIHRMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQH EwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYDVQQLEx9DZXJ0aWZp Y2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVyc29uYWwgRnJlZW1h aWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRoYXd0ZS5jb20wHhcNMDMw NzE3MDAwMDAwWhcNMTMwNzE2MjM1OTU5WjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3Rl IENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWls IElzc3VpbmcgQ0EwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAMSmPFVzVftOucqZWh5owHUE cJ3f6f+jHuy9zfVb8hp2vX8MOmHyv1HOAdTlUAow1wJjWiyJFXCO3cnwK4Vaqj9xVsuvPAsH5/Ef kTYkKhPPK9Xzgnc9A74r/rsYPge/QIACZNenprufZdHFKlSFD0gEf6e20TxhBEAeZBlyYLf7AgMB AAGjgZQwgZEwEgYDVR0TAQH/BAgwBgEB/wIBADBDBgNVHR8EPDA6MDigNqA0hjJodHRwOi8vY3Js LnRoYXd0ZS5jb20vVGhhd3RlUGVyc29uYWxGcmVlbWFpbENBLmNybDALBgNVHQ8EBAMCAQYwKQYD VR0RBCIwIKQeMBwxGjAYBgNVBAMTEVByaXZhdGVMYWJlbDItMTM4MA0GCSqGSIb3DQEBBQUAA4GB AEiM0VCD6gsuzA2jZqxnD3+vrL7CF6FDlpSdf0whuPg2H6otnzYvwPQcUCCTcDz9reFhYsPZOhl+ hLGZGwDFGguCdJ4lUJRix9sncVcljd2pnDmOjCBPZV+V2vf3h9bGCE6u9uo05RAaWzVNd+NWIXiC 3CEZNd4ksdMdRv9dX2VPMYIDEDCCAwwCAQEwdjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhh d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVt YWlsIElzc3VpbmcgQ0ECECpJVMO68ii+Xfsc1O1YYFIwCQYFKw4DAhoFAKCCAW8wGAYJKoZIhvcN AQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDcwMjA1MjIzMzM0WjAjBgkqhkiG9w0B CQQxFgQUHplrFK5nXa7vLvHIATh5cwuTxTowgYUGCSsGAQQBgjcQBDF4MHYwYjELMAkGA1UEBhMC WkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0 ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhAqSVTDuvIovl37HNTtWGBSMIGHBgsqhkiG 9w0BCRACCzF4oHYwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQ dHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhAq SVTDuvIovl37HNTtWGBSMA0GCSqGSIb3DQEBAQUABIIBAH7982dAAQnzQ2LRj/mjWtW/9U/uW5tj oBGjTGYanbSRRHTQtLmVosQp8CQLgjZ3PkCtwFVvc/K7WIfN9Uh/7UImT33Y1APYfo87pfS3nr6a wYOrmas7adff6s3d708z9JZtRZEpFTorWkDR4CoxOSbtM0zBy+KHOyK1PQQLxJ+bpfeic58KUcfC 6olTozyCsQEKbr3Qql2DHgv/DhIKVzFuwMRHm9tLW9b3I/uOR+H7QQac8B/cs805t0WjVGeAVJ5C TW1xx0BAxlPcWI48cnUal3dBFQBsIPo/Uif5L5PcK/LjbigxlaFAWOzPzarQgjp9jhGhEeYT7NbN mccweE0AAAAAAAA= --Apple-Mail-1--865914158--