RE: Can 0.94h no longer handle Outer Join syntax?

["Spencer Roberts at Tellusoft" <sroberts@xxxxxxxxxxxxx>]

Right - and so in this example, the "contact" table must be on the right
side.

Thanks 4 debugging my code (as if you have time 4 that, I'm sure). Actually,
I didn't write it, but I paid for it... Anyway, that did the trick.

As usual Toby, you seem to be dead-on. Your demonstrated knowledge across a
wide array of technical issues lends to an increased confidence in the
product (even though it doesn't need it). Let me know if this open source
thing doesn't pan out for you...
;-)

Cheers,
Spencer

-----Original Message-----
From: mckoidb@mckoi.com [mailto:mckoidb@mckoi.com]On Behalf Of Tobias
Downer
Sent: Tuesday, February 25, 2003 1:43 PM
To: mckoidb@mckoi.com
Subject: Re: Can 0.94h no longer handle Outer Join syntax?


Try this rewrite,

  SELECT c.*, count(e.id_contact), cs.name, cs.avg_score
  FROM csr cs, contact c
  LEFT OUTER JOIN evaluation e
  ON c.id_contact = e.id_contact
  WHERE cs.id_csr = c.id_csr
  AND c.id_csr = 1

When you use an OUTER JOIN the ON expression must only contain
references to the left and right table of the join.

Toby.

Spencer Roberts at Tellusoft wrote:
> Hi All,
>
> I just upgraded to 0.94h from 0.94g and received this error...
>
> This SQL:
>
> SELECT c.*, count(e.id_contact), cs.name, cs.avg_score
> FROM contact c, csr cs
> LEFT OUTER JOIN evaluation e
> ON c.id_contact = e.id_contact
> WHERE cs.id_csr = c.id_csr
> AND c.id_csr = 1
>

Now produces this error:

CAUSE: java.lang.RuntimeException: Unable to find table with variable
reference:
 c.id_contact
        at
com.mckoi.database.interpret.Planner$QueryTableSetPlanner.findTableSource(Pl
anner.java:1162)
        at
com.mckoi.database.interpret.Planner$QueryTableSetPlanner.joinAllPlansWithVa
riables(Planner.java:1253)
        at
com.mckoi.database.interpret.Planner$QueryTableSetPlanner.access$600(Planner
.java:1006)
        at
com.mckoi.database.interpret.Planner$QueryTableSetPlanner$StandardJoinExpres
sionPlan.addToPlanTree(Planner.java:1718)
        at
com.mckoi.database.interpret.Planner$QueryTableSetPlanner.planForExpressionL
ist(Planner.java:2283)
        at
com.mckoi.database.interpret.Planner$QueryTableSetPlanner.planForExpression(
Planner.java:2426)
        at
com.mckoi.database.interpret.Planner$QueryTableSetPlanner.logicalEvaluate(Pl
anner.java:2448)
        at
com.mckoi.database.interpret.Planner$QueryTableSetPlanner.naturallyJoinPlans
(Planner.java:1437)
        at
com.mckoi.database.interpret.Planner$QueryTableSetPlanner.planAllOuterJoins(
Planner.java:1478)
        at
com.mckoi.database.interpret.Planner$QueryTableSetPlanner.planSearchExpressi
on(Planner.java:2477)
        at
com.mckoi.database.interpret.Planner.formQueryPlan(Planner.java:480)
        at com.mckoi.database.interpret.Select.prepare(Select.java:123)
        at
com.mckoi.database.interpret.SQLQueryExecutor.execute(SQLQueryExecutor.java:
144)
        at
com.mckoi.database.jdbcserver.AbstractJDBCDatabaseInterface.execQuery(Abstra
ctJDBCDatabaseInterface.java:321)
        at
com.mckoi.database.jdbcserver.JDBCDatabaseInterface.execQuery(JDBCDatabaseIn
terface.java:253)
        at
com.mckoi.database.jdbc.MConnection.executeQuery(MConnection.java:348)
        at
com.mckoi.database.jdbc.MStatement.executeQuery(MStatement.java:110)
        at
com.mckoi.database.jdbc.MPreparedStatement.executeQuery(MPreparedStatement.j
ava:120)
        at
com.mckoi.jfccontrols.QueryAgent$QueryThread.run(QueryAgent.java:282)

Any ideas?

TIA,
Spencer



---------------------------------------------------------------
Mckoi SQL Database mailing list  http://www.mckoi.com/database/
To unsubscribe, send a message to mckoidb-unsubscribe@mckoi.com