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]
