FYI Just for completeness, I did a bit more research on this topic. Thr Oracle documentation actually says:
Restrictions on the order_by_clause: * If you have specified the DISTINCT operator in this statement, then this clause cannot refer to columns unless they appear in the select list. * An order_by_clause can contain no more than 255 expressions. On Tuesday, July 30, 2002, at 01:07 PM, Dain Sundstrom wrote: > 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
