RE: Associating an outer join with a value for a report

[Jim Moore]

I've done a work-around by creating a view, but I'd rather not create one
for all the possible values of lookup_type.  Is there no other way?  It
seems like a pretty significant deficiency in the API if I can't even FORCE
the outer join in code.  :(


-----Original Message-----
From: Jim Moore 
Sent: Friday, August 01, 2003 3:42 PM
To: 'OJB Users List'
Subject: Associating an outer join with a value for a report


First, what I'm trying to accomplish...

I need to get this SQL:

SELECT t.name, l.meaning
FROM tasks t, lookup l
WHERE t.userid = 9999 AND
 l.lookup_code (+) = t.lang AND
 l.lookup_type (+) = 'PREFERRED_LANGUAGE'


Currently I have this

Task:
<reference-descriptor name="primaryLanguage" class-ref="Lookup">
  <foreignkey field-ref="lang"/>
</reference-descriptor>

Lookup:
<field-descriptor name="lookupCode"
  primarykey="true"
  column="LOOKUP_CODE" jdbc-type="VARCHAR"/>
<field-descriptor name="lookupType"
  column="LOOKUP_TYPE" jdbc-type="VARCHAR"/>


Unfortunately, it can't think of how to express the relationship in the
above SQL in the XML.  So I tried doing the following code:


Criteria criteria = new Criteria(); criteria.addEqualTo("userid", new
Long(9999)); criteria.addEqualTo("primaryLanguage.lookupType",
"PREFERRED_LANGUAGE"); ReportQueryByCriteria q =
QueryFactory.newReportQuery(Task.class, criteria);
q.setPathOuterJoin("primaryLanguage");
q.setPathOuterJoin("primaryLanguage.lookupType");
q.setColumns(new String[] {"name", "primaryLanguage.meaning"});


Unfortunately, I can't figure out a way to get it to apply the outer-join to
the LOOKUP_TYPE column, as setPathOuterJoin("primaryLanguage.lookupType")
doesn't do anything, and there's no "addLeftJoinEqualTo" or the like.

Ideas?

What I'd LIKE to be able to do is something along the lines of


Lookup:
<field-descriptor name="lookupCode"
  primarykey="true"
  column="LOOKUP_CODE" jdbc-type="VARCHAR"/>
<field-descriptor name="lookupType"
  primarykey="true"
  column="LOOKUP_TYPE" jdbc-type="VARCHAR"/>

Task:
<reference-descriptor name="primaryLanguage" class-ref="Lookup">
  <foreignkey field-ref="lang"/>
  <foreignkey field-value="PREFERRED_LANGUAGE"/>
</reference-descriptor>


That way the relationship can be declaratively stated in the XML.

Thanks!

-Jim Moore

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]