Hi, It's a bit hard to explain why this is not allowed... basically the database doesn't "know" what ucase does. PostgreSQL throws an exception as well, with the message "for SELECT DISTINCT, ORDER BY expressions must appear in select list 42P10/0" for the statements below:
drop table component; create table component(description varchar(255), component_execution varchar(255)); select distinct description, component_execution from component order by upper(component.description); Regards, Thomas On Friday, April 20, 2012, Remo wrote: > Hi > > When executing the query below, H2 raises an error: Order by > expression "UCASE(COMPONENT.DESCRIPTION)" must be in the result list > in this case; > > SELECT DISTINCT > COMPONENT.DESCRIPTION, > COMPONENT.COMPONENT_EXECUTION > FROM > COMPONENT > ORDER BY UCASE(COMPONENT.DESCRIPTION) > > The same query without 'DISTINCT' or a modified query that returns > distinct records by means of GROUP BY (see below) works fine. > > SELECT > COMPONENT.DESCRIPTION, > COMPONENT.COMPONENT_EXECUTION > FROM > COMPONENT > GROUP BY COMPONENT.DESCRIPTION, COMPONENT.COMPONENT_EXECUTION > ORDER BY UCASE(COMPONENT.DESCRIPTION) > > What's the reason for this behaviour? > > Thanks, > Remo > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To post to this group, send email to > [email protected]<javascript:;> > . > To unsubscribe from this group, send email to > [email protected] <javascript:;>. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
