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
[EMAIL PROTECTED] 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
