How to simulate NVL in HQL

Question

I tried this:

from Table where (:par1 is null or col1 = :par1)

but it happens that

from Table where :par1 is null

always returns all the rows of the table, even if the :par1 is not null.

while

 select * from table where col1 = 'asdf'

does not return any row.

I can't use native grammars because my application is supposed to run on different database engines

Answer 1

If your underlying database is Oracle then you can use the nvl function, I tried it and it worked for me.

Query query = session.createQuery(
                    " select ft from FeatureToggle ft left outer join ft.featureToggleCustomerMap "
                    + " ftcm where nvl(ftcm.custId,:custId) = :custId");

query.setParameter("custId", Long.valueOf(custId));

Your use case can be diffrent and you can use the nvl function as per your requirement if the database is nvl, not sure about the implecation of the other databases, as I have used this code only for Oracle. Hope it helps.

Answer 2

The equivalent to the nvl command in HQL is the coalesce command. coalesce(a,b) will return a if a is not null, otherwise b.

So you would want something on the lines of:

from Table where col1 = coalesce(:par1, 'asdf')