Jakob, Sure, the generated SQL:
SELECT A0.CREATED_DATE,A0.REVENUE,A0.SOL_NAME2,A0.SOL_DESC,A0.CLOSING_DATE,A0.TERM_ REASON_ID,A0.CURR_ISO_CODE,A0.NOTE,A0.SOL_MARK_TYPE_ID,A0.CREATED,A0.SOUR_TY PE_ID,A0.SOL_NAME,A0.CONT_ID,A0.CURR_PROPOSAL_ID,A0.LAST_UPDATE_DATE,A0.CHAN GED,A0.SOL_STATUS_ID,A0.PROBABILITY,A0.CREATOR,A0.CURR_PRESENTATION_ID,A0.CH ANGER,A0.EXPECTED_VALUE,A0.CURR_FINANCE_ID,A0.SOL_ID,A0.PRIMARY_QUOTE_ID,A0. OWNER_ID,A0.SAL_CHAN_ID,A0.CUST_ID,A0.CURR_QUOTATION_ID,A2.CITY as ojb_col_29 FROM R_SOLUTION A0 INNER JOIN M_CUSTOMER A1 ON A0.CUST_ID=A1.CUST_ID INNER JOIN M_ADDRESS A2 ON A1.ADDRESS_ID=A2.ADDRESS_ID ORDER BY 30 I did noticed that Criteria#addOrderByAscending had been deprecated, unfortunately, switching to QueryByCriteria is going to take a lot of reworking of my application to use. I did take a look into the OJB code, and the problem appears to be in SqlQueryStatement. The ensureColumns method was changed (between versions 1.36 and 1.37) to call appendColName with a false for useOuterJoins. Changing that to true, resulted in the following query being generated which worked. (I however have no idea what side effects this would have, I assume the true-->false change was made for a reason). SELECT A0.CREATED_DATE,A0.REVENUE,A0.SOL_NAME2,A0.SOL_DESC,A0.CLOSING_DATE,A0.TERM_ REASON_ID,A0.CURR_ISO_CODE,A0.NOTE,A0.SOL_MARK_TYPE_ID,A0.CREATED,A0.SOUR_TY PE_ID,A0.SOL_NAME,A0.CONT_ID,A0.CURR_PROPOSAL_ID,A0.LAST_UPDATE_DATE,A0.CHAN GED,A0.SOL_STATUS_ID,A0.PROBABILITY,A0.CREATOR,A0.CURR_PRESENTATION_ID,A0.CH ANGER,A0.EXPECTED_VALUE,A0.CURR_FINANCE_ID,A0.SOL_ID,A0.PRIMARY_QUOTE_ID,A0. OWNER_ID,A0.SAL_CHAN_ID,A0.CUST_ID,A0.CURR_QUOTATION_ID,A2.CITY as ojb_col_29 FROM R_SOLUTION A0 LEFT OUTER JOIN M_CUSTOMER A1 ON A0.CUST_ID=A1.CUST_ID LEFT OUTER JOIN M_ADDRESS A2 ON A1.ADDRESS_ID=A2.ADDRESS_ID ORDER BY 30 Alan Olmanson. -----Original Message----- From: Jakob Braeuchi [mailto:[EMAIL PROTECTED] Sent: Monday, March 15, 2004 4:01 PM To: OJB Users List Subject: Re: Order by and joins hi alan, please post the generated sql. is there an ORDER BY clause ? btw Criteria#addOrderByAscending is deprecated has been moved to QueryByCriteria. jakob Olmanson, Alan wrote: > Hello, > > I'm having a problem with query sorting not working the same in the CVS I > pulled on 3/4/04 as it did in the 0.9.8. > > I do the following query: > > Criteria crit = new Criteria(); > crit.addOrderByAscending("customer.mainAddress.city"); > query = QueryFactory.newQuery("com.firepond.domain.Solution", > crit); > > In 0.9.8 this would work, I'd get all the solutions, in the current version > this returns an empty list. > > The problem seem to be that OJB is now using INNER JOIN to join solution, > customer, and address, rather than left outer joins. > > > Repository (fragment): > <class-descriptor class="com.firepond.domain.Solution" table="R_SOLUTION"> > > <field-descriptor id="1" name="id" column="SOL_ID" jdbc-type="NUMERIC" > primarykey="true" autoincrement="true"> > </field-descriptor> > > <field-descriptor id="14" name="customerId" column="CUST_ID" > jdbc-type="NUMERIC"> > </field-descriptor> > > <reference-descriptor name="customer" > class-ref="com.firepond.domain.Customer" proxy="true" auto-update="true"> > <foreignkey field-id-ref="14"/> <!-- customerId --> > </reference-descriptor> > > </class-descriptor> > > <class-descriptor class="com.firepond.domain.Customer" table="M_CUSTOMER"> > > <field-descriptor id="1" name="id" column="CUST_ID" jdbc-type="NUMERIC" > primarykey="true" autoincrement="true"> > </field-descriptor> > > <field-descriptor id="26" name="addressId" column="ADDRESS_ID" > jdbc-type="NUMERIC"> > </field-descriptor> > > <reference-descriptor name="mainAddress" > class-ref="com.firepond.domain.Address" proxy="true" auto-update="true"> > <foreignkey field-id-ref="26"/> <!-- addressId --> > </reference-descriptor> > </class-descriptor> > > > <class-descriptor class="com.firepond.domain.Address" table="M_ADDRESS" > sequence-manager="com.firepond.domain.ojb.SequenceManagerHighLowImpl"> > > <field-descriptor id="1" name="id" column="ADDRESS_ID" > jdbc-type="NUMERIC" primarykey="true" autoincrement="true"> > </field-descriptor> > > <field-descriptor id="16" name="city" column="CITY" jdbc-type="VARCHAR"> > </field-descriptor> > </class-descriptor> > > > Alan Olmanson > > --------------------------------------------------------------------- > 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]
