title: Problems to get the difference of days between two days with queryDsl on Oracle 11
body: <p>I´m making a query with queryDsl.</p>
  
  <p>I used this to create a BooleanExpresion</p>
  
  <pre><code>public BooleanBuilder getPredicate(BooleanBuilder pBuilderBusquePerso){
  
  int dias = 30;
  
  QEntity1 qEntity = QEntity1.entity;
  
  pBuilderBusquePerso = pBuilderBusquePerso.and(qEntity.date1 != null
  ? (SQLExpressions.datediff(DatePart.day, qEntity.date2, qEntity.date1).lt(dias) ) :null );
  
  return pBuilderBusquePerso;
  }
  </code></pre>
  
  <p>And in another procedure I call and execute this as:</p>
  
  <pre><code>BooleanBuilder pBuilderBusquePerso = new BooleanBuilder();
  
  Predicate filter =getPredicate(pBuilderBusquePerso);
  
  Page&lt;Entity&gt; iLista = myRepository.findAll(getMyPredicate(usr, filter, tipoListado, null, estados), paginacion);
  </code></pre>
  
  <p>So the sql query result is:</p>
  
  <pre><code>select table1 ta1
  ......
  exists (
  select 1 
  from
  table2 ta2
  where
     ta1.inv_id=ta2.inv_id 
   and diff_days(ta1.inv_exp_date, ta2.exp_date)&lt;?
  )
  </code></pre>
  
  <p>Giving the following error:                 </p>
  
  <blockquote>
    <p>Caused by: java.sql.SQLException: ORA-00904: "DIFF_DAYS":  invalid identifier     </p>
  </blockquote>
  
  <p>So the querysql translation for the ddbb oracle is wrong. Is there a way to translate from queryDsl to oracle functions? What should I need?</p>
  
  <p>I also tried with DATETRUNC</p>
  
  <pre><code>pBuilderBusquePerso = pBuilderBusquePerso.and(qEntity.date1 != null
  ? (SQLExpressions.datetrunc(DatePart.day, qEntity.date1).castToNum(Integer.class).subtract(SQLExpressions.datetrunc(DatePart.day, qEntity.date2).castToNum(Integer.class))).lt(dias) :null );
  
  
  
  
  exists (
  select 1 
  from
  table2 ta2
   where
    ta1.inv_id=ta2.inv_id 
     and cast(trunc_day(ta2.exp_date) as number(10,0))-cast(trunc_day(ta1.inv_exp_date) as number(10,0))&lt;?
  </code></pre>
  
  <p>Giving a similar error: </p>
  
  <blockquote>
    <p>Caused by: java.sql.SQLException: ORA-00904: "TRUNC_DAY":  invalid identifier</p>
  </blockquote>
  
  <p>And addDays</p>
  
  <pre><code>pBuilderBusquePerso = pBuilderBusquePerso.and(qEntity.date1 != null
  ? ((SQLExpressions.addDays(qEntity.date2, dias)).after(qEntity.date1)):null );
  
  
  exists (
  select 1 
  from
  table2 ta2
  where
    ta1.inv_id=ta2.inv_id 
     and add_days(ta1.inv_exp_date, ?)&gt;ta1.exp_date
  )
  </code></pre>
  
  <p>Giving another similar error:   </p>
  
  <blockquote>
    <p>Caused by: java.sql.SQLException: ORA-00904: "ADD_DAYS":   invalid identifier         </p>
  </blockquote>
  
  <p>I was trying to follow this test to build my query
  <a href="https://searchcode.com/codesearch/view/17765673/" rel="nofollow">https://searchcode.com/codesearch/view/17765673/</a></p>
  
  <p>Thanks in advance.</p>
  
  <p>Edit: this is the full error trace.</p>
  
  <pre><code>org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
      at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:231)
      at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:214)
      at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417)
      at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
      at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
  .....................
  
  
  ......................
  Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
      at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
      at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
      at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
      at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
  
  ......................
  ......................
  ......................
  
  (And here comes the ORA error)
  
  Caused by: java.sql.SQLException: ORA-00904: "TRUNC_DAY": invalid identifier
  
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
  
  .....
  </code></pre>