Hi,
I am using Hibernate 3 with SQL Server 2000.
On one of the screens in my application, the User can sort by clicking on any of the column headers on the displayed table. One of the columns is of the type "ntext" in the database. I am appending and forming the HQL on runtime based on the User input to sort the result set

String getCompleteList = "from Ktp ktp left join ktp.ktpContact contact ORDER BY ";
StringBuffer queryString = new StringBuffer(getCompleteList);

//When the User clicks on the column in question, cast it to varchar
if("ktp.column2".equalsIgnoreCase(sortColumn))
{
sortColumn = "cast(ktp.column2 as varchar(8000))";
}

queryString.append(sortColumn);
queryString.append(" ");
queryString.append(" DESC ");

query = session.createQuery(queryString.toString());
result = query.list();
tx.commit();

However, I get the following error when I execute the above code. Any help is greatly appreciated.

SystemOut O QUERY IS from Ktp ktp left join ktp.ktpContact contact ORDER BY cast(ktp.column2 as varchar(8000)) DESC
00000044 PARSER E org.hibernate.hql.ast.ErrorCounter reportError line 1:178: expecting CLOSE, found '('
00000044 HqlParser W org.hibernate.hql.ast.HqlParser processEqualityExpression processEqualityExpression() : No expression to process!

Hi,
i have a similar problem as this one "http://forum.hibernate.org/viewtopic.php?p=2386961" from trying to sort column fields of nText data type. But that post has no reply yet.
Checking the forums, i cast the nText to varchar(4000) with cast(Cat as varchar(4000)). But this returned a syntax error..
Some other forums have suggest this: "cast(description, varchar(4000))"
But this returned a null exception...
I've included my .hbm mapping file below if it helps...

This is the line that cost the exception: description is nText
getHibernateTemplate().find("from Cat order by cast(description as varchar(4000) ASC);

Output:
nested exception is org.hibernate.hql.ast.QuerySyntaxException: expecting CLOSE, found '(' near line 1, column 81 [from Cat order by cast(description as varchar(4000)) ASC]

Hibernate mapping file:

<class name="Cat" table="tbl_cat" lazy="true">
<id name="id" >
<generator class="native"/>
</id>

<property name="description"/>

<!-- Added a many-to-one for website -->
<many-to-one name="website" class="Website" column="websiteId" not-null="false" not-found="ignore"
unique="true" cascade="none" lazy="false" />
</class>

Cat.java:
public class Cat extends BaseObject implements Serializable {
. . .
private String description;
...
}