title: Calling User Defined SQL function in JPQL with hibernate
body: <p>I need to migrate My Spring Data JPA/Hibernate Application to </p>
  
  <pre><code>MS SQL Server (Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
  Enterprise Edition (64-bit) on Windows NT 6.1 &lt;X64&gt; (Build 7601: Service Pack 1) (Hypervisor)) 
  </code></pre>
  
  <p>from </p>
  
  <pre><code>Oracle(Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production).
  </code></pre>
  
  <p><strong>Environment</strong>:</p>
  
  <pre><code>org.springframework.data:spring-data-jpa:2.0.9.RELEASE
  org.hibernate.javax.persistence:hibernate-jpa-2.1-api:1.0.2.Final
  org.hibernate:hibernate-entitymanager:5.2.17.Final
  Java version: 1.8.0_91, vendor: Oracle Corporation
  OS name: "windows 10", version: "10.0", arch: "amd64", family: "windows"
  </code></pre>
  
  <p>Now in many places in orm.xml, we have used <strong>to_char</strong>. For example, to generate monthly aggregated reports etc.</p>
  
  <p>In the SQL Server schema, we have defined <strong>to_char</strong> expecting either Hibernate will register this or will leave some hook(s) to get user defined functions registered:</p>
  
  <pre><code>CREATE FUNCTION to_char(@dateVal DATE, @dateFormat varchar) RETURNS VARCHAR(20) 
    BEGIN       
      RETURN CONVERT(varchar, date, 1)
    END  
  </code></pre>
  
  <p>I have studied <a href="https://stackoverflow.com/questions/6647582/write-call-user-defined-function-in-jpql">Write &amp; Call user-defined function in JPQL?</a>.  But unfortunately, I am not allowed to re-define one dialect.</p>
  
  <p>For the JPQL,</p>
  
  <pre><code>&lt;query&gt;
           select new com.entity.MyEntity(
              c.Id,
              to_char(c.transactionDate,'Mon/yyyy'),
              sum(c.disputeAmount))
              FROM   ChargeBack c join c.chargebackReason rcd
              GROUP BY c.Id, to_char(c.transactionDate,'Mon/yyyy'),
  &lt;/query&gt;
  </code></pre>
  
  <p>I am getting the following error as the SQL function is not registered.</p>
  
  <pre><code>Caused by: java.lang.NullPointerException
  at org.hibernate.hql.internal.NameGenerator.generateColumnNames(NameGenerator.java:27)
  at org.hibernate.hql.internal.ast.util.SessionFactoryHelper.generateColumnNames(SessionFactoryHelper.java:434)
  at org.hibernate.hql.internal.ast.tree.SelectClause.initializeColumnNames(SelectClause.java:269)
  at org.hibernate.hql.internal.ast.tree.SelectClause.finishInitialization(SelectClause.java:259)
  at org.hibernate.hql.internal.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:254)
  at org.hibernate.hql.internal.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:1013)
  at org.hibernate.hql.internal.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:781)
  at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:677)
  at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:313)
  at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:261)
  at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:266)
  at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189)
  at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:141)
  at org.hibernate.engine.query.spi.HQLQueryPlan.&lt;init&gt;(HQLQueryPlan.java:115)
  at org.hibernate.engine.query.spi.HQLQueryPlan.&lt;init&gt;(HQLQueryPlan.java:77)
  at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153)
  at org.hibernate.query.spi.NamedQueryRepository.checkNamedQueries(NamedQueryRepository.java:157)
  at org.hibernate.internal.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:533)
  at org.hibernate.internal.SessionFactoryImpl.&lt;init&gt;(SessionFactoryImpl.java:323)
  at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:462)
  at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:892)
  at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:57)
  at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365)
  at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:390)
  ... 67 more
  </code></pre>