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

[Jakob Braeuchi]

hi jim,

you can force ojb to use an outer join by using setPathOuterJoin :

       ReportQueryByCriteria query;
       Criteria crit, having;
      
       crit = new Criteria();
       having = new Criteria();
       having.addGreaterThan("sum(konti.saldo)", new Integer(200));
      
       query = new ReportQueryByCriteria(Person.class, crit);
       query.setColumns(new String[] { "id", "name", "vorname", 
"sum(konti.saldo)" });
       query.addGroupBy(new String[]{ "id", "name", "vorname" });
       query.setHavingCriteria(having);
       query.setPathOuterJoin("konti");
            
       broker.getReportQueryIteratorByQuery(query);

produces this sql using an OUTER join :

SELECT A0.id,A0.name,A0.vorname,sum(A1.saldo) FROM tabPerson A0 LEFT 
OUTER JOIN tabKonto A1 ON A0.id=A1.idPerson GROUP BY 
A0.id,A0.name,A0.vorname HAVING sum(A1.saldo) >  '200'

the outer join is used for the columns defined in the relationship 
descriptor. in your case on 'lang'  only. 
as far as i can see you want to have some kind of a predefined value 
'PREFERRED_LANGUAGE' in your query ??
may be you should try to use a query customizer.

jakob

Jim Moore wrote:

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]
 



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