Announcement Announcement Module
No announcement yet.
Missing right parenthesis in CriteriaQuery expression Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Missing right parenthesis in CriteriaQuery expression

    I'm trying to execute the next query using CriteriaBuilder and CriteriaQuery interfaces:

    select DES_EVE_CAT, COUNT(*) from KWXS.TKWXSSAU where ( DES_EVE_CAT in ('asociaServicioMejorado') ) and ( TIM_EVENTO<=TO_DATE('31/10/2014','DD/MM/YYYY') ) group by DES_EVE_CAT;
    My code to represent the sql expression is like this:

    List<String> consumptionTypes = Arrays.asList(EventoAplicacion.EVENTOAPLICACION_RE GISTER_SOA_SERVICE_CONSUMPTION);
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<TkwxssauWrapper> cq = cb.createQuery(TkwxssauWrapper.class);
    Root <Tkwxssau> tkwxssau = cq.from(Tkwxssau.class);
    List<Predicate> predicates = new ArrayList<Predicate>();

    Expression<String> expDesEveCat = tkwxssau.get("desEveCat");

    Path<Long> desEveCatPath = tkwxssau.get( "desEveCat" );, desEveCatPath, cb.count(tkwxssau)));

    cq.where(cb.and(predicates.toArray(new Predicate[predicates.size()])));

    TypedQuery<TkwxssauWrapper> q = entityManager.createQuery(cq);
    List<TkwxssauWrapper> results = q.getResultList();
    It seems the code is right, but when I'm debugging it, it finishes with next error :

    org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.SQLGrammarException: could not execute query; nested exception isjavax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
    at org.springframework.orm.jpa.EntityManagerFactoryUt ils.convertJpaAccessExceptionIfPossible(EntityMana
    Caused by: java.sql.SQLException: ORA-00907: missing right parenthesis
    The sentence generated by my code is this :

    select new TkwxssauWrapper(generatedAlias0.desEveCat, count(generatedAlias0)) from Tkwxssau as generatedAlias0 where ( generatedAlias0.desEveCat in (aram0) ) and ( generatedAlias0.timEvento<=aram1 ) group by generatedAlias0.desEveCat
    if I translate it to sql to execute in the Oracle SQL Database like this,

    select DES_EVE_CAT, COUNT(generatedAlias0) from KWXS.TKWXSSAU as generatedAlias0 where ( generatedAlias0.DES_EVE_CAT in ('asociaServicioMejorado') ) and ( generatedAlias0.TIM_EVENTO<=TO_DATE('31/10/2014','DD/MM/YYYY') ) group by generatedAlias0.DES_EVE_CAT;
    shows the followed error :

    Error: ORA-00933: SQL command not properly ended

    SQLState: 42000
    ErrorCode: 933
    I have seen that Oracle does not accept the alias clause AS: from KWXS.TKWXSSAU as generatedAlias0, and I think this is my error.

    I've checked my configurations files and I 've seen that the mapping to Oracle is correctly indicated in the persintence.xml file :

    <property name="hibernate.dialect" value="org.hibernate.dialect.OracleDialect"/>
    I have been looking for the answer for other pages and I still havent found any solution.

    Is there any possibility to remove the alias or generate the alias without AS clause?

    Thanks for you help.

    José Pascual

  • #2
    Hm, it's true that Oracle SQL may not contain the "as" word at this place in the query. Are you 100% sure that the "hibernate.dialect" property is used from this file during runtime?


    • #3
      Hi Gwieser,
      I have been seeing the logs when my server is uploading and I have saw this warning:

      27/2/2014 12:51:13.165 INFO [Dialect.?:?] Using dialect: org.hibernate.dialect.OracleDialect
      27/2/2014 12:51:13.415 WARN [Oracle9Dialect.?:?] The Oracle9Dialect dialect has been deprecated; use either Oracle9iDialect or Oracle10gDialect instead
      27/2/2014 12:51:13.415 WARN [OracleDialect.?:?] The OracleDialect dialect has been deprecated; use Oracle8iDialect instead
      Is it possible that here is the error?

      Should I change the dialect to Oracle8iDialect ?

      Thanks for your help

      Best regards

      José Pascual