CODEWITHSUNDEEP
javasql-serverhibernatespring-data-jpa
Hoshi
Hoshi

Reputation: 607

How can I use constants in Hibernate native query?

I am trying to do a simple query against SQLServer in a Spring Data JPA repository using the @Query annotation to create a native query. Hibernate is getting hold of it and cannot seem to parse out the constants (I think).

The error is: org.hibernate.loader.custom.NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias [] during auto-discovery of a native-sql query

The query is:

select convert(varchar,((a.achan - a.freq) / 100))
       +'_'+
       convert(varchar,((a.bchan - a.freq) / 100))
     , convert(varchar,((a.bchan - a.freq) / 100)) 
from channel_src as a 
where a.discriminator = ?

Since every actual field is aliased to the actual table, and the complaint is about a duplicate NULL alias, I'm assuming that it doesn't like the 100's? If not, is there any way to divine what it's choking on? There is no log line between resolution of the parameter and the rollback statement.

Edit: Here is the query in the context of the repository

public interface ChannelMatrixRepository extends JpaRepository<ChannelMatrix,Integer>
{
    @Query(value = "select convert(varchar,((a.achan - a.freq) / 100)) +'_'+ convert(varchar,((a.bchan - a.freq) / 100)) , convert(varchar,((a.bchan - a.freq) / 100)) from channel_src as a where a.discriminator = ?1", nativeQuery = true)
    Map<String, String> findAllBySquelchLevel(int sk);
}

Upvotes: 1

Views: 1990

Answers (1)

John
John

Reputation: 3996

I believe this error: Encountered a duplicated sql alias []

Is caused by the fact that you have multiple columns in your select statement that are non-table-column-names. Giving each of these columns an alias should work. In other words, something like this should work:

select 
  convert(varchar,((a.achan - a.freq) / 100)) +'_'+
       convert(varchar,((a.bchan - a.freq) / 100)) COLUMN_ONE
     , convert(varchar,((a.bchan - a.freq) / 100)) COLUMN_TWO
from channel_src as a 
where a.discriminator = ?

Upvotes: 1

Related Questions