db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: [Derby 10.1] SELECT ... raises "An ON clause associated with a JOIN operator is not valid"
Date Tue, 08 Nov 2005 21:19:34 GMT
Hi Erik,

I am not sure I understand your query. However, it looks to me as though 
it is trying to inner join DATACOLLECTION_ELEMENT to the previous left 
join result. The compiler is objecting that THIS.ID is not a column in 
either DATACOLLECTION_ELEMENT or the previous left join. THIS is the 
correlation name for another table in the FROM list but it is outside 
the scope of that inner join.

I have taken the liberty of rearranging your query. Given the 
commutativity and associativity of the inner join operator, I hope that 
this re-arranged query accomplishes what you intend. This rewritten 
query does compile:

SELECT DISTINCT 'org.jpox.samples.performance.DataCollection' AS
JPOXMETADATA,THIS.DATA_GROUP,THIS.ID
FROM
(
   (
      DATACOLLECTION THIS INNER JOIN DATACOLLECTION_ELEMENTS THIS_ELEMENTS
      ON THIS_ELEMENTS.ID_OID = THIS.ID
   )
   INNER JOIN DATAELEMENT UNBOUND_F ON THIS_ELEMENTS.ID_EID = UNBOUND_F.ID
),
(
   DATAELEMENT UNBOUND_E
   LEFT OUTER JOIN DATAELEMENT UNBOUND_E_DATA
   ON UNBOUND_E.ID = UNBOUND_E_DATA.ID
)
WHERE UNBOUND_E_DATA."DATA" = 'Something' AND UNBOUND_E.ID = UNBOUND_F.ID
AND (THIS.DATA_GROUP = 1);

Regards,
-Rick

Erik.Bengtson@intl.westernunion.com wrote:

>Should I understand the silence as YES?
>
>Erik Bengtson
>
>
>-----Original Message-----
>From: Erik Bengtson 
>Sent: Saturday, November 05, 2005 2:11 PM
>To: derby-user@db.apache.org
>Subject: [Derby 10.1] SELECT ... raises "An ON clause associated with a
>JOIN operator is not valid"
>
>Hi,
>
>Below is the script to reproduce the issue. When the SELECT stmt is run,
>derby raises "An ON clause associated with a JOIN operator is not valid"
>
>Show I raise a JIRA defect?
>
>CREATE TABLE DATAELEMENT
>(
>    ID INTEGER NOT NULL,
>    "DATA" VARCHAR(255)
>)
>ALTER TABLE DATAELEMENT ADD CONSTRAINT DATAELEMENT_PK PRIMARY KEY (ID)
>CREATE TABLE DATACOLLECTION
>(
>    ID INTEGER NOT NULL,
>    "DATA_GROUP" INTEGER NOT NULL
>)
>ALTER TABLE DATACOLLECTION ADD CONSTRAINT DATACOLLECTION_PK PRIMARY KEY
>(ID)
>CREATE TABLE DATACOLLECTION_ELEMENTS
>(
>    ID_OID INTEGER NOT NULL,
>    ID_EID INTEGER NOT NULL
>)
>ALTER TABLE DATACOLLECTION_ELEMENTS ADD CONSTRAINT DATACOLLECTI8E_PK
>PRIMARY KEY (ID_OID,ID_EID)
>CREATE TABLE DATACOLLECTION_ELEMENTSCACHED
>(
>    ID_OID INTEGER NOT NULL,
>    ID_EID INTEGER NOT NULL
>)
>ALTER TABLE DATACOLLECTION_ELEMENTSCACHED ADD CONSTRAINT DATACOLLECTIKP_PK
>PRIMARY KEY (ID_OID,ID_EID)
>
>
>SELECT DISTINCT 'org.jpox.samples.performance.DataCollection' AS
>JPOXMETADATA,THIS.DATA_GROUP,THIS.ID 
>FROM DATACOLLECTION THIS,
>DATAELEMENT UNBOUND_E
>LEFT OUTER JOIN DATAELEMENT UNBOUND_E_DATA ON UNBOUND_E.ID =
>UNBOUND_E_DATA.ID 
>INNER JOIN DATACOLLECTION_ELEMENTS THIS_ELEMENTS ON THIS_ELEMENTS.ID_OID =
>THIS.ID 
>INNER JOIN DATAELEMENT UNBOUND_F ON THIS_ELEMENTS.ID_EID = UNBOUND_F.ID
>WHERE UNBOUND_E_DATA."DATA" = 'Something' AND UNBOUND_E.ID = UNBOUND_F.ID
>AND (THIS.DATA_GROUP = 1)
>
>Regards,
>
>Erik Bengtson
>[De
>RE
>  
>


Mime
View raw message