Hi All,

I've declared a Hibernate column as type "text" and internally we created the column as "longtext" in MySQL, "nvarchar" in SQL Server and "CLOB" in Oracle.

I've defined a view in subselect in hbm file and it works fine for mysql but it failed in other databases since i've selected the above clob field and used in group by clause too.

I need to write a generic SQL view query and puts in <subselect> element which works for all databases, atleast for the three databases.

Ex: to_char() it doesn't works in MySQL but works fine in SQL Server and Oracle.
cast() and varchar works fine in SQL Server and Oracle but MySQL doesn't support varchar in cast() or
convert() method.

Please guide with a generic query which supports all the databases, atleast the above three databases.

Also, is it possible to define DB specific subselect in hibernate hbm file?

Can you please let me know your thoughts in detail?

Thanks,
Kathir.