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

[Jakob Braeuchi]

hi jim,

ojb only uses joins based on relationships, not on simple values !
unfortunately the query customizer can be used for 1:n and m:n queriies 
only.
it's called in PersitenceBrokerImpl#getFKQuery.

any help will be appreciated.

jakob

Jim Moore wrote:

Btw: I don't just gripe without being willing to do anything about it.  If
someone can confirm that this is in fact a bug and not intended
functionality, I'll take a look at what it would take to fix...
-Jim Moore

-----Original Message-----
From: Jim Moore 
Sent: Sunday, August 03, 2003 10:36 PM
To: 'OJB Users List'
Subject: RE: Associating an outer join with a value for a report

Unfortunately, "setPathOuterJoin" only applies the outer join to joins with
other tables, not values.  In other words, whereas I need
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'
instead what it gives me is

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'
Or, using LEFT JOIN syntax, it should be:

SELECT t.name, l.meaning
FROM tasks t LEFT JOIN lookup l
 ON (l.lookup_code = t.lang AND
     l.lookup_type = 'PREFERRED_LANGUAGE')
WHERE t.userid = 9999
But instead it's giving

SELECT t.name, l.meaning
FROM tasks t LEFT JOIN lookup l
 ON (l.lookup_code = t.lang)
WHERE t.userid = 9999 AND
l.lookup_type  = 'PREFERRED_LANGUAGE'
which of course doesn't work.  As near as I can tell, that happens because
OJB isn't taking the fact that the outer join was applied to that path when
it processes the criteria.  So it appears to be a bug in how the SQL is
constructed when there's a value in an outer-joined path.
-----Original Message-----
From: Jakob Braeuchi [mailto:[EMAIL PROTECTED] 
Sent: Sunday, August 03, 2003 5:55 AM
To: OJB Users List
Subject: Re: Associating an outer join with a value for a report

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]
---------------------------------------------------------------------
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]