Hi

I am writing a HQL, in which I have the following requirement.

Say there are two tables Student and Department.

in which crseDept, crseLevel and crseOption attributes values should be equal.

crseOption can have null values.

HQL:

from Student stu, Department dept where
stu.course = dept.course

generates the following SQL.

select * from Student stu, Department dept where
stu.Dept_ID = dept.Dept_ID
and stu.crse_level = dept.crse_level
and stu.crse_option = dept.crse_option

Since the crse_level can be be null. I want the SQL to be generated as

select * from Student stu, Department dept where
stu.Dept_ID = dept.Dept_ID
and nvl(stu.crse_level,'X') = nvl(dept.crse_level,'X')
and stu.crse_option = dept.crse_option

How should I tell in HQL, that this particular field should be compared with nvl function?

Thanks
Sedhu

Use the coalesce function which should help you achieve the same result.
http://docs.jboss.org/hibernate/core/3. ... xpressions

Coalesce, IIRC, returns the first non-null value from an expression list or in other words nvl is coalesce with only two parameters.