Hello,

I have a one question to ask about using the upper() function in HQL.
I have a hql like this:

from SampleModel where upper(lastName)=upper(:lastName)

When i try to execute this query, i get the exception as mentioned in debug level hibernate log at the bottom of this post.

Is the above hql not a valid one??

I executed the following on the DB2 console :

SELECT * FROM ADMINISTRATOR.SAMPLE_TABLE where upper(last_name)=upper('Gohel');

and i am getting proper result.

And the generated SQL as mentioned below is similar.

So can anybody please help explaining me the reason behind the exception occuring??


Hibernate version: hibernate 3.0.3

Database name : IBM DB2 version 8.0

Generated SQL :
select samplemode0_.sample_table_id as sample1_0_, samplemode0_.first_name as first2_0_, samplemode0_.last_name as last3_0_ from administrator.sample_table samplemode0_ where upper(samplemode0_.last_name)=upper(?)



Debug level hibernate log:
15:05:25,671 WARN JDBCExceptionReporter:77 - SQL Error: -418, SQLState: 42610
15:05:25,671 ERROR JDBCExceptionReporter:78 - DB2 SQL error: SQLCODE: -418, SQLSTATE: 42610, SQLERRMC: null
15:05:25,671 WARN JDBCExceptionReporter:77 - SQL Error: -727, SQLState: 56098
15:05:25,687 ERROR JDBCExceptionReporter:78 - DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-418;42610;
15:05:25,687 WARN JDBCExceptionReporter:77 - SQL Error: -727, SQLState: 56098
15:05:25,687 ERROR JDBCExceptionReporter:78 - DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-418;42610;
HibernateException occured.Details are as follows : could not execute query




Thanks,
Jignesh

Maybe this way:

No, the query mentioned above also is not working.
Can anybody please let me know, if it can be done, and if yes, then how?

Thanks,
Jignesh