db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Erik.Bengt...@intl.westernunion.com
Subject [DERBY-39] An ON clause associated with a JOIN operator is not valid
Date Tue, 14 Feb 2006 12:16:42 GMT
Hi,

This is issue DERBY-39, can someone give an opinion on this?

It works in MSSQL and DB2

SELECT UNBOUND_P.PROJID FROM applicationidentity0.PERSONS THIS,
applicationidentity0.PROJECTS UNBOUND_P WHERE
EXISTS (SELECT 1 FROM applicationidentity0.PROJECT_MEMBER THIS_PROJECTS_P
LEFT OUTER JOIN applicationidentity0.PROJECTS UNBOUND_P_NAME ON
UNBOUND_P.PROJID = UNBOUND_P_NAME.PROJID
WHERE THIS_PROJECTS_P."MEMBER" = THIS.PERSONID AND
THIS_PROJECTS_P."MEMBER" = THIS.PERSONID AND UNBOUND_P.PROJID =
THIS_PROJECTS_P.PROJID AND UNBOUND_P_NAME."NAME" = ?
AND (THIS.DISCRIMINATOR = ? OR THIS.DISCRIMINATOR = ? OR
THIS.DISCRIMINATOR = ?))

---

ERROR 42972: An ON clause associated with a JOIN operator is not valid.
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at
org.apache.derby.impl.sql.compile.JoinNode.deferredBindExpressions(Unknown
Source)
at org.apache.derby.impl.sql.compile.JoinNode.bindResultColumns(Unknown
Source)
at org.apache.derby.impl.sql.compile.FromList.bindResultColumns(Unknown
Source)
at org.apache.derby.impl.sql.compile.SelectNode.bindResultColumns(Unknown
Source)
at org.apache.derby.impl.sql.compile.SubqueryNode.bindExpression(Unknown
Source)
at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown
Source)
at
org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown
Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
at org.apache.derby.impl.sql.compile.ReadCursorNode.bind(Unknown Source)
at org.apache.derby.impl.sql.compile.CursorNode.bind(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInt
ernalStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
Source)
at org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at
com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConne
ction.java:241)
at org.jpox.store.rdbms.RDBMSManager.getStatement(RDBMSManager.java:355)
at org.jpox.store.StatementText.prepareStatement(StatementText.java:204)

---- SCHEMA ----

CREATE TABLE persons (
    PERSONID INTEGER NOT NULL,
    FIRSTNAME VARCHAR(32) NOT NULL,
    LASTNAME VARCHAR(32) NOT NULL,
    MIDDLENAME VARCHAR(32),
    BIRTHDATE VARCHAR(32) NOT NULL,
    ADDRID INTEGER,
    STREET VARCHAR(64),
    CITY VARCHAR(64),
    STATE CHAR(2),
    ZIPCODE CHAR(5),
    COUNTRY VARCHAR(64),
    HIREDATE VARCHAR(32),
    WEEKLYHOURS REAL,
    DEPARTMENT INTEGER,
    FUNDINGDEPT INTEGER,
    MANAGER INTEGER,
    MENTOR INTEGER,
    HRADVISOR INTEGER,
    SALARY REAL,
    WAGE REAL,
    DISCRIMINATOR varchar(64) NOT NULL,
    CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
);

CREATE TABLE projects (
    PROJID INTEGER NOT NULL,
    NAME VARCHAR(32) NOT NULL,
    BUDGET DECIMAL(11,2) NOT NULL,
    CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
);

CREATE TABLE project_member (
    PROJID INTEGER NOT NULL,
    MEMBER INTEGER NOT NULL
);


Erik Bengtson


ed

Mime
View raw message