Asif, Maybe you missed my subsequent message.
Try your tests again, using the DISTINCT keyword. On Wednesday, July 31, 2002, at 02:26 AM, Asif R. Ahmed wrote: > All: > > Well I am not sure about other databases but Oracle and Mysql both do > not > require the Column to be in the select list if you want to order by > that. > > I just ran test on both Oracle 8i and Mysql 3.23.51 version both work > fine > if the order by column is not in the select statement. > > -Asif Rafiq Ahmed > > ----- Original Message ----- > From: "Dain Sundstrom" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Monday, July 29, 2002 8:07 PM > Subject: Re: [JBoss-user] JBossQL problem > > >> That is super lame. Just another reason Oracle sucks. Can one of you >> post a feature request for this and I'll add support for >> order-must-be-in-select for 4.0. >> >> -dain >> >> Stephen Coy wrote: >>> I know exactly what is happening here. >>> >>> Some databases, Oracle for one, insist that columns in an ORDER BY >>> clause also appear in the SELECT clause. >>> >>> JBossQL generates queries like: >>> >>> SELECT pk FROM MyEntity WHERE [predicate] ORDER BY someOtherColumn >>> >>> Oracle, for instance, insists upon: >>> >>> SELECT pk, someOtherColumn FROM mytable WHERE [predicate] ORDER BY >>> someOtherColumn >>> >>> I've worked around this by using load-groups and an on-find strategy: >>> >>> <load-groups> >>> <load-group> >>> <load-group-name>myloadgroup</load-group-name> >>> <field-name> someOtherColumn </field-name> >>> </load-group> >>> </load-groups> >>> >>> <query> >>> <query-method> >>> <method-name>ejbSelectInSequence</method-name> >>> <method-params> >>> </method-params> >>> </query-method> >>> <jboss-ql>SELECT DISTINCT OBJECT(s) FROM MyEntity AS s >>> ORDER >>> BY s. someOtherColumn ASC</jboss-ql> >>> <read-ahead> >>> <strategy>on-find</strategy> >>> <page-size>16</page-size> >>> <eager-load-group> myloadgroup </eager-load-group> >>> </read-ahead> >>> </query> >>> >>> >>> But it sure would be neater if JBoss could automatically include ORDER >>> BY columns in the SELECT clause. >>> >>> >>> On Tuesday, July 30, 2002, at 08:06 AM, Dain Sundstrom wrote: >>> >>>> JbossCMP only needs to load the primary key columns, and it does not >>>> think lname is a pk column. You either have the pk fields >>>> misconfigured or the custom pk class is not written correctly. >>>> >>>> -dain >>>> >>>> Greg Turner wrote: >>>> >>>>> In JBoss 3.0, I have a JBoss query: >>>>> <jboss-ql> >>>>> <![CDATA[SELECT OBJECT(a) FROM Customer a WHERE >>>>> a.zip = ?1 >>>>> ORDER BY a.zip DESC]]> >>>>> </jboss-ql> >>>>> This query fails because the SQL that is executed is only selecting >>>>> on >>>>> customer.id, not both customer.id and customer.lname. >>>>> How can I make it select on the required fields? >>>>> Thanks >>>>> Greg ------------------------------------------------------- This sf.net email is sponsored by: Dice - The leading online job board for high-tech professionals. Search and apply for tech jobs today! http://seeker.dice.com/seeker.epl?rel_code=31 _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
