Return-Path: Delivered-To: apmail-db-torque-dev-archive@www.apache.org Received: (qmail 56645 invoked from network); 21 Sep 2006 16:48:52 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 21 Sep 2006 16:48:52 -0000 Received: (qmail 33648 invoked by uid 500); 21 Sep 2006 16:48:51 -0000 Delivered-To: apmail-db-torque-dev-archive@db.apache.org Received: (qmail 33628 invoked by uid 500); 21 Sep 2006 16:48:51 -0000 Mailing-List: contact torque-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Unsubscribe: List-Help: List-Post: List-Id: "Apache Torque Developers List" Reply-To: "Apache Torque Developers List" Delivered-To: mailing list torque-dev@db.apache.org Received: (qmail 33617 invoked by uid 99); 21 Sep 2006 16:48:51 -0000 Received: from idunn.apache.osuosl.org (HELO idunn.apache.osuosl.org) (140.211.166.84) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 21 Sep 2006 09:48:51 -0700 Authentication-Results: idunn.apache.osuosl.org smtp.mail=thilka@vps.de; spf=permerror X-ASF-Spam-Status: No, hits=0.0 required=5.0 tests= Received-SPF: error (idunn.apache.osuosl.org: domain vps.de from 212.86.208.21 cause and error) Received: from [212.86.208.21] ([212.86.208.21:33292] helo=avp.vps.de) by idunn.apache.osuosl.org (ecelerity 2.1.1.8 r(12930)) with ESMTP id E2/00-03329-E62C2154 for ; Thu, 21 Sep 2006 09:48:48 -0700 Received: from localhost ([127.0.0.1]) by avp.vps.de with esmtp (Exim 4.34) id 1GQRj8-0001P5-3K for torque-dev@db.apache.org; Thu, 21 Sep 2006 18:48:42 +0200 Received: from avp.vps.de ([127.0.0.1]) by localhost (avp [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 04197-06 for ; Thu, 21 Sep 2006 18:48:34 +0200 (CEST) Received: from [10.0.0.19] (helo=scuderia09.vps.local) by avp.vps.de with esmtp (Exim 4.34) id 1GQRj0-0001Ow-2l for torque-dev@db.apache.org; Thu, 21 Sep 2006 18:48:34 +0200 MIME-Version: 1.0 Subject: Limiting joins in Oracle Content-class: urn:content-classes:message X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Date: Thu, 21 Sep 2006 18:48:58 +0200 Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=SHA1; boundary="----=_NextPart_000_0054_01C6DDAE.98C0D4A0" Message-ID: <0765101BA5724F4DAC1607B4CF4BC1712C1B30@scuderia09.vps.local> X-MS-Has-Attach: yes X-MS-TNEF-Correlator: Thread-Topic: Limiting joins in Oracle Thread-Index: AcbdndPEIaUPzr6vQK+nis/JR1O8qA== From: "Tobias Hilka" To: "Apache Torque Developers List" X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at vps.de X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N ------=_NextPart_000_0054_01C6DDAE.98C0D4A0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi everybody, We are facing the following problem: We would like to execute a limited query over a join of two (or more) database tables in oracle. We build our Criteria like this (more or less): crit.setOffset(0); crit.setLimit(50); crit.addJoin(mytablealias1.FIELDNAME, mytablealias2.FIELDNAME, Critiera.INNER_JOIN); crit.addSelectColumn(mytablealias1.FIELDNAME); crit.addAlias(mytablealias1, TABLE1); crit.addAlias(mytablealias2, TABLE2); The SQL-query string that is generated looks like this: SELECT B.* FROM ( SELECT A.*, rownum AS TORQUE$ROWNUM FROM ( SELECT MYTABLEALIAS1.FIELDNAME FROM T_TABLE1 mytablealias1 INNER JOIN TABLE mytablealias2 ON mytablealias1.FIELDNAME=mytablealias1.FIELDNAME ) A ) B WHERE B.TORQUE$ROWNUM <= 50 When executing this query, torque (and the tool we are using TOAD)) tells us the following error code: ORA-00918 column ambiguously defined. It seems that Oracle takes all fields from the innermost select and cuts off the alias names, resulting in two columns with the name "FIELDNAME". I understand the problem oracle is having at this point, but is there any solution to this problem? Thanks in advance. Best regards, Tobias Hilka ------=_NextPart_000_0054_01C6DDAE.98C0D4A0 Content-Type: application/x-pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIJ9TCCBN8w ggPHoAMCAQICAh1OMA0GCSqGSIb3DQEBBQUAMHQxCzAJBgNVBAYTAkRFMRUwEwYDVQQKDAxELVRy dXN0IEdtYkgxJjAkBgNVBAMMHUQtVFJVU1QgQWR2YW5jZWQgQ0EgMjAwMyAxOlBOMSYwJAYDVQRB DB1ELVRSVVNUIEFkdmFuY2VkIENBIDIwMDMgMTpQTjAeFw0wMzA5MzAwODU1MzNaFw0wOTA5MzAw ODU1MzNaMHQxCzAJBgNVBAYTAkRFMRUwEwYDVQQKDAxELVRydXN0IEdtYkgxJjAkBgNVBAMMHUQt VFJVU1QgQWR2YW5jZWQgQ0EgMjAwMyAxOlBOMSYwJAYDVQRBDB1ELVRSVVNUIEFkdmFuY2VkIENB IDIwMDMgMTpQTjCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAKyII0hvTclS//5hUKDW 24f1KdCMdxpDX9he2D97xILvwuhb8awoeBf9KdNbe6fViSSIHH/lmbSU/WiSlW3AwvfcXJlslVzd ILjT08WtDurWATMVVhoOarEQrC1fvBax6S9DUmVrkeTRHc9tL3mlsM9+xJ0Q0r26IeVBnLm/CLkB V/ToienlbON1odk2OelO0i+Y9rf8B/qNWnl/OGFxEkUZ0Q7GnuGbGGd2PYqmMNBAaR1otV3BuDrr Wemtw3qbFjSmwwBJtIuo6aG+3Gxw645EWhzuYf23ilW/WfEgT/prQZTK1giIhEEE+kpidBTL0Lnj mYFV7LLnI5SnhOMsA4kCAwEAAaOCAXkwggF1MBIGA1UdEwEB/wQIMAYBAf8CAQAwEQYDVR0OBAoE CEMKn2I9TS5sMDYGCCsGAQUFBwEBBCowKDAmBggrBgEFBQcwAYYaaHR0cDovL29jc3AuZC10cnVz dC5uZXQ6ODAwFwYDVR0gBBAwDjAMBgorBgEEAaU0AhwBMIGGBgNVHREEfzB9gRBpbmZvQGQtdHJ1 c3QubmV0hhZodHRwOi8vd3d3LmQtdHJ1c3QubmV0hlFsZGFwOi8vZGlyZWN0b3J5LmQtdHJ1c3Qu bmV0L0NOPUQtVFJVU1QgQWR2YW5jZWQgQ0EgMjAwMyAxOlBOLE89RC1UcnVzdCBHbWJILEM9REUw DgYDVR0PAQH/BAQDAgEGMGIGA1UdHwRbMFkwV6BVoFOGUWxkYXA6Ly9kaXJlY3RvcnkuZC10cnVz dC5uZXQvQ049RC1UUlVTVCBBZHZhbmNlZCBDQSAyMDAzIDE6UE4sTz1ELVRydXN0IEdtYkgsQz1E RTANBgkqhkiG9w0BAQUFAAOCAQEAJUd0ZkIoDuHcM013zrTT5Xi4watM9EqK8h+xiEdl2oRjEGOe 3J3kJFQeNcFMNal4CRUcarwgutZnjfL7DTMeEtcmhYUKiiIyfPWWg3b/VdqNC9QgLFxfMXEinu2c uhYiGVsAILqAx53iD41zermqObxBB24hXuOd71SCC5VmQ09Wi5ZZQflDKXz/sTFzsYcuPSJ5u/Gp pb0cMnfLK6sKqYCDrYZUqghw9rMRgGK2QaeX1maLwlq/y+g9L4lgl3Cb9eYlZCRW3U7wOf8DE6LO HgcFhPs50Y6BS3qjSLng35D4rf+jc7EuzBCvwf30VEXL2KK9pJ3zXcoBjGT7E+UZNDCCBQ4wggP2 oAMCAQICAwC6MjANBgkqhkiG9w0BAQUFADB0MQswCQYDVQQGEwJERTEVMBMGA1UECgwMRC1UcnVz dCBHbWJIMSYwJAYDVQQDDB1ELVRSVVNUIEFkdmFuY2VkIENBIDIwMDMgMTpQTjEmMCQGA1UEQQwd RC1UUlVTVCBBZHZhbmNlZCBDQSAyMDAzIDE6UE4wHhcNMDYwNTA0MTMyMzM4WhcNMDgwNTA0MTMy MzM4WjCBgzELMAkGA1UEBhMCREUxJTAjBgNVBAoMHHZwcyBWaWRlbyBQcmludCBTeXN0ZW1lIEdt YkgxFTATBgNVBAMMDFRvYmlhcyBIaWxrYTEPMA0GA1UEKgwGVG9iaWFzMQ4wDAYDVQQEDAVIaWxr YTEVMBMGA1UEBRMMODc4OTY4MjEwMDAxMIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQCyPjdp 3C/5u1g1tA6Ry+N3+RVUwTwuoIgZ318nK8JX+KmHgw9KeDopSwCxPKTOA5A4tsIQgqs0NEhrtS/E eSJbHXIVUOBpAUMEKRmDd7z7YThu6zvGMZYZEqfu16dFFZGREZTYQPg8xvYxVW8HINfZRJ8Kk5EK GZBOVPX8OKBf3wIDAQABo4ICGzCCAhcwHQYDVR0lBBYwFAYIKwYBBQUHAwIGCCsGAQUFBwMEMBMG A1UdIwQMMAqACEMKn2I9TS5sMDMGCCsGAQUFBwEBBCcwJTAjBggrBgEFBQcwAYYXaHR0cDovL29j c3AuZC10cnVzdC5uZXQwFwYDVR0gBBAwDjAMBgorBgEEAaU0AhwBMGIGA1UdHwRbMFkwV6BVoFOG UWxkYXA6Ly9kaXJlY3RvcnkuZC10cnVzdC5uZXQvQ049RC1UUlVTVCBBZHZhbmNlZCBDQSAyMDAz IDE6UE4sTz1ELVRydXN0IEdtYkgsQz1ERTCBhgYDVR0SBH8wfYEQaW5mb0BkLXRydXN0Lm5ldIYW aHR0cDovL3d3dy5kLXRydXN0Lm5ldIZRbGRhcDovL2RpcmVjdG9yeS5kLXRydXN0Lm5ldC9DTj1E LVRSVVNUIEFkdmFuY2VkIENBIDIwMDMgMTpQTixPPUQtVHJ1c3QgR21iSCxDPURFMBEGA1UdDgQK BAhIviJ5+dI84zAOBgNVHQ8BAf8EBAMCBLAwgYIGA1UdEQR7MHmBDXRoaWxrYUB2cHMuZGWGUGxk YXA6Ly9kaXJlY3RvcnkuZC10cnVzdC5kZS9DTj1ELVRSVVNUIEFkdmFuY2VkIENBIDIwMDMgMTpQ TixPPUQtVHJ1c3QgR21iSCxDPURFhhZodHRwOi8vd3d3LmQtdHJ1c3QubmV0MA0GCSqGSIb3DQEB BQUAA4IBAQCnwNJ+BDREp0UNeH39LBxxLe11BEuz4iJn8P3tCMUE8KzmMRRq/Fay/AWzS2CgR60h laHk4DgnhiLzTcV8dZ0OtF297WBtkPuiUa79h0wlqPNzQGvLm/vNYAVq20E0D8Fd+xfoByeGPw7C ks9HOGa7SXxHc2UquJq9N9gfA4rH4EwTLBYV1kjGFA796A9lpZLGHt5RuHF+QqjzRS9jpet4vfaI Xs/f7pREXFcGOx1oXXv1SBKIFuV0erXtcoQbVdC6NwyNC86lGvEWBHHACQEm7oyTaeHMsiF2xrdM Si+IPBgGHFqdCXNTuqGxCuJRghlLd65wlek07QKkLfIFOCWrMYIDBzCCAwMCAQEwezB0MQswCQYD VQQGEwJERTEVMBMGA1UECgwMRC1UcnVzdCBHbWJIMSYwJAYDVQQDDB1ELVRSVVNUIEFkdmFuY2Vk IENBIDIwMDMgMTpQTjEmMCQGA1UEQQwdRC1UUlVTVCBBZHZhbmNlZCBDQSAyMDAzIDE6UE4CAwC6 MjAJBgUrDgMCGgUAoIIB4jAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEP Fw0wNjA5MjExNjQ4NThaMCMGCSqGSIb3DQEJBDEWBBTwp/ckjvd9CUqqTOnErfjiCSDgpDBnBgkq hkiG9w0BCQ8xWjBYMAoGCCqGSIb3DQMHMA4GCCqGSIb3DQMCAgIAgDANBggqhkiG9w0DAgIBQDAH BgUrDgMCBzANBggqhkiG9w0DAgIBKDAHBgUrDgMCGjAKBggqhkiG9w0CBTCBigYJKwYBBAGCNxAE MX0wezB0MQswCQYDVQQGEwJERTEVMBMGA1UECgwMRC1UcnVzdCBHbWJIMSYwJAYDVQQDDB1ELVRS VVNUIEFkdmFuY2VkIENBIDIwMDMgMTpQTjEmMCQGA1UEQQwdRC1UUlVTVCBBZHZhbmNlZCBDQSAy MDAzIDE6UE4CAwC6MjCBjAYLKoZIhvcNAQkQAgsxfaB7MHQxCzAJBgNVBAYTAkRFMRUwEwYDVQQK DAxELVRydXN0IEdtYkgxJjAkBgNVBAMMHUQtVFJVU1QgQWR2YW5jZWQgQ0EgMjAwMyAxOlBOMSYw JAYDVQRBDB1ELVRSVVNUIEFkdmFuY2VkIENBIDIwMDMgMTpQTgIDALoyMA0GCSqGSIb3DQEBAQUA BIGAaD3UpVB17JzwTZKe1txXu+9KEIUNo+y1iFmY7Flt2+29b4W8rS95v2QVuk+BiOa+lT9iGJIX wtO0tbubdOPB1x1zDrm4OHhP8N9KPDBtFCbRYd9ZF9RYyowMPPTO4aRrC8d3T2QMPb7a84E3K+Rx 5z/0ZyA/SVduGF8W5TXwccIAAAAAAAA= ------=_NextPart_000_0054_01C6DDAE.98C0D4A0--