4 Replies Latest reply on Jan 27, 2011 5:04 PM by Andrew Swan

    How to ORDER BY case-insensitively using jboss-ql?

    Andrew Swan Newbie

      I'd like to be able to find entities in a case-insensitive order. In normal JDBC I would do something like

      select * from Employee order by upper(Employee.FirstName)

      so in jboss-ql I tried this:
      SELECT DISTINCT OBJECT(p)
      FROM PersonSchema p
      ORDER BY UCASE(p.firstName)

      However at deploy time I got this error:
      14:49:44,919 ERROR [EntityContainer] Starting failed jboss.j2ee:jndiName=local/Person,service=EJB
      org.jboss.deployment.DeploymentException: Error compiling JBossQL statement 'SELECT DISTINCT OBJECT(p)
       FROM PersonSchema p
       WHERE p.lastName = ?1
       ORDER BY UCASE(p.firstName)'; - nested throwable: (org.jboss.ejb.plugins
      .cmp.ejbql.ParseException: Encountered "UCASE" at line 4, column 58.
      Was expecting one of:
       <NUMERIC_VALUED_PATH> ...
       <STRING_VALUED_PATH> ...
       <DATETIME_VALUED_PATH> ...
       )
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCJBossQLQuery.<init>(JDBCJBossQLQuery.java:52)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCCommandFactory.createJBossQLQuery(JDBCCommandFactory.java:72)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCQueryManager.start(JDBCQueryManager.java:260)
      etc. etc.

      Does jboss-ql not support the UCASE function in ORDER BY clauses? It seems to work fine in WHERE clauses.

      Also, is there a JBoss-ql reference somewhere? I looked through the online docs (http://docs.jboss.org/jbossas/jboss4guide/r2/html/ch11.chapter.html#ch11.queries.sect) but couldn't find anything definitive, only some examples.

      Thanks for any help.