"may be you should try to use a query customizer." Maybe that would be a good solution barring the expanded XML definition. I see that you can define one for a collection-descriptor, but don't see how that would be done for a reference-descriptor, and the documentation for the collection-descriptor one is sparse (about all that it does is says it exists, but not what the fields mean, when it's called [it doesn't seem to be when the SQL is constructed], etc).
How would I customize the query? -----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]
