Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Subject: RE: [Derby 10.1] SELECT ... raises "An ON clause associated with a
 JOIN
 operator is not valid"
Date: Tue, 8 Nov 2005 15:10:18 +0100
MIME-Version: 1.0
To: derby-user@db.apache.org
Cc: 
From: Erik.Bengtson@intl.westernunion.com
Message-ID: <OF59254495.61E615F3-ON412570B3.004DC754@hqintl1.com>
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Should I understand the silence as YES?

Erik Bengtson


-----Original Message-----
From: Erik Bengtson=20
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=20
FROM DATACOLLECTION THIS,
DATAELEMENT UNBOUND_E
LEFT OUTER JOIN DATAELEMENT UNBOUND_E_DATA ON UNBOUND_E.ID =3D
UNBOUND_E_DATA.ID=20
INNER JOIN DATACOLLECTION_ELEMENTS THIS_ELEMENTS ON THIS_ELEMENTS.ID_OID =
=3D
THIS.ID=20
INNER JOIN DATAELEMENT UNBOUND_F ON THIS_ELEMENTS.ID_EID =3D =
UNBOUND_F.ID
WHERE UNBOUND_E_DATA."DATA" =3D 'Something' AND UNBOUND_E.ID =3D =
UNBOUND_F.ID
AND (THIS.DATA_GROUP =3D 1)

Regards,

Erik Bengtson
[De
RE