Thanks, but why? Why doesn't OJB just do it for me?
Since "SELECT t.* FROM task t, task_resolution r WHERE t.task_resolution_id
= r.task_resolution_id" is GUARANTEED to be wrong (since
t.task_resolution_id can be null) shouldn't OJB automaticly do (in Oracle
syntax) "SELECT t.* FROM task t, task_resolution r WHERE
t.task_resolution_id = r.task_resolution_id (+)"?
The only exeption to that is when resolution is used in the criteria. In
that case an inner join can and should be used for efficiency. But
otherwise it's simply wrong.
Which brings up a separate, but related issue: Using the
query.setPathOuterJoin("aTaskResolution") method, it does the outer join --
but too often. If your criteria has ("aTaskResolution.desc",
"can_not_repro") then it still uses the outer join even though it logicly
makes no sense for it to: because there's a criteria on (or through) that
table it should be inner-joined.
Granted, my code can do things like detect if "aTaskResolution" is used in
the results but not in the criteria and issue the setPathOuterJoin if that's
the case, but isn't that EXACTLY the kind of thing that OJB's supposed to do
for me?
Is there something I'm missing?
-Jim Moore
-----Original Message-----
From: Guidos, Jay [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 31, 2003 8:31 PM
To: 'OJB Users List'
Subject: RE: Outer joins
Hi Jim,
You want to set
query.setPathOuterJoin("aTaskResolution");
on the query before you execute it. That will force a outer join as you
require.
Jay
-----Original Message-----
From: Jim Moore [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 31, 2003 6:27 PM
To: '[EMAIL PROTECTED]'
Subject: Outer joins
In OJB 1.0RC4 I can't figure out how to get outer joins to work.
Below the rest of my email I've included a simplified version of my XML:
In other words, I have a Task that has a status and may-or-may-not have a
resolution (ie, nullable="true").
When I construct a ReportQuery where the return columns are ["taskName",
"aTaskStatus.desc", "aTaskResolution.desc"] and a criteria of ("taskId",
123) it does an inner join with the TASK_STATUS table (as it should, since
neither side can be null), but it also does an inner join with the
TASK_RESOLUTION table (which it shouldn't do since taskResolutionId for the
Task can be null).
I've searched through the documentation, the mailing list archives, Google,
everything I can think of, but can't find anything telling me how to get it
to work right. I even tried forcing the issue with the setPathOuterJoin
method, which obviously I shouldn't have to do since there's plenty of
information for the engine to know that in the declaration, but even that's
not working. I checked the docs and the DTD, but there isn't even something
I can put in the reference-descriptor to tell it "This needs an outer join."
Help!
-Jim Moore
---
<class-descriptor class="Task" table="TASK">
<field-descriptor
name="taskId"
column="TASK_ID"
jdbc-type="BIGINT"
indexed="true"
nullable="false"
primarykey="true"/>
<field-descriptor
name="taskName"
column="TASK_NAME"
nullable="false"
jdbc-type="VARCHAR"/>
<field-descriptor
name="taskResolutionId"
column="TASK_RESOLUTION_ID"
nullable="true"
jdbc-type="BIGINT"/>
<field-descriptor
name="caseTaskStatusId"
indexed="true"
nullable="false"
column="CASE_TASK_STATUS_ID"
jdbc-type="BIGINT"/>
<reference-descriptor
name="aTaskResolution"
class-ref="TaskResolution">
<foreignkey field-ref="taskResolutionId"/>
</reference-descriptor>
<reference-descriptor
name="aTaskStatus"
class-ref="TaskStatus">
<foreignkey field-ref="caseTaskStatusId"/>
</reference-descriptor>
</class-descriptor>
<class-descriptor class="TaskResolution" table="TASK_RESOLUTION">
<field-descriptor
name="taskResolutionId"
column="TASK_RESOLUTION_ID"
jdbc-type="BIGINT"
nullable="false"
primarykey="true"/>
<field-descriptor
name="desc"
column="TASK_RESOLUTION_DESC"
nullable="false"
jdbc-type="VARCHAR"/>
<collection-descriptor
name="collTask"
element-class-ref="Task">
<inverse-foreignkey field-ref="taskResolutionId"/>
</collection-descriptor>
</class-descriptor>
<class-descriptor class="TaskStatus" table="TASK_STATUS">
<field-descriptor
name="taskStatusId"
column="TASK_STATUS_ID"
jdbc-type="BIGINT"
nullable="false"
primarykey="true"/>
<field-descriptor
name="desc"
column="TASK_STATUS_DESC"
nullable="false"
jdbc-type="VARCHAR"/>
<collection-descriptor
name="collTask"
element-class-ref="Task">
<inverse-foreignkey field-ref="taskStatusId"/>
</collection-descriptor>
</class-descriptor>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
