NHibernate DetachedCriteria - how to use something like Oracle's NVL on outer-joined set

I'd like to create DetachedCriteria which would perform something similar to the following SQL code:

select *
FROM PRICELIST pl
LEFT OUTER JOIN PRICELISTDURATIONSHIFT sh ON sh.PRICELISTID = pl.ID
WHERE sh.CUSTOMERID = :cust
  AND nvl(sh.DATEFROM, pl.DATEFROM) <= :dt
ORDER BY nvl(sh.DATEFROM, pl.DATEFROM) DESC

I'm able to left-outer-join the PriceListDurationShift class/table and add the Customer constraint, but I'm not able to figure out how to add something like the nvl(sh.DATEFROM, pl.DATEFROM) <= :dt restriction.

I'd appreciate any advice for this sample and also any advice on NHibernate materials on advanced DetachedCriteria querying.

---

hopefully this would work:

var priceLists = priceListRepo.FindAll(DetachedCriteria.For<PriceList>("pl")
    .CreateCriteria((PriceList x) => x.PriceListDurationShifts, () => shift,
        JoinType.LeftOuterJoin)
    .Add<PriceListDurationShift>(x => x.Customer == cust)
    .Add(Expression.Or(
        Expression.And(Restrictions.IsNull("shift.DateFrom"),
            Restrictions.Le("pl.DateFrom", dt.DateTo)),
        Expression.And(Restrictions.IsNotNull("shift.DateFrom"),
            Restrictions.Le("shift.DateFrom", dt.DateTo))
        ))
    )
    .ToList();

From stackoverflow

• I've looked for something like this as well, but haven't found a solution to it.

  In the meantime, I've solved this by using HQL instead of the Criteria API.

  HQL has a method coalesce which can be used for this purpose.

  from PriceList as pl
  left join pl.PriceListDurationShift as shift
  where coalesce (shift.DateFrom, pl.DateFrom) <= :dt
  order by coalesce (shift.DateFrom, pl.DateFrom)
  

  Buthrakaur : it could be probably solved with Projections.SQLFunction like in this case: http://stackoverflow.com/questions/793166/how-to-user-year-and-month-functions-in-nh-criteria-api