RE: [DERBY-39] An ON clause associated with a JOIN operator is not valid

[Erik . Bengtson]

Using DB2’s Command Editor.   Erik Bengtson +32 2 639-7260   -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 14, 2006 3:04 PM To: derby-user@db.apache.org Subject: Re: [DERBY-39] An ON clause associated with a JOIN operator is not valid   Can you please tell me which tools u used to generate that graphical query plan ? I would be very helpful for me to find out how i can generate such easy to understand plans for my queries. Thank you. [EMAIL PROTECTED] wrote: Someone might want see the query plan attached   Erik Bengtson     -----Original Message----- From: Erik Bengtson Sent: Tuesday, February 14, 2006 1:17 PM To: derby-user@db.apache.org Subject: [DERBY-39] An ON clause associated with a JOIN operator is not valid   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