I'm trying to write this in HQL:

select invitemid, sum(onOrder)
from (
select
invitemid,
jobid,
sum(case when typeid=1 then quantity else 0 end) -
sum(case when typeid=2 then quantity else 0 end) -
sum(case when typeid=8 then quantity else 0 end) as onOrder
from inv_transaction
where typeid in (1,2,8)
group by invitemid, jobid
) as jobOnOrder
where onOrder>0
group by invitemid

What I essentially want to do, is add up the details on each job (ordered-received-cancelled) and then ignore all those that are negative (sometimes inventory might be received that wasn't ordered) and sum up the rest, to find out how many are on order for a given item.

Via hibernate, it seems to not allow selects in a from(). I've been looking everywhere and asking the Hibernate guru here, to no avail. Is there any way to get around this?

I believe you can achieve that using nested Projections.
Check the reference documantation, 15.7. Projections, aggregation and grouping

Thanks, but do you know any way using HQL queries?

That's just a small part in a larger query where it's easier to use HQL to pull exactly what I need.

In principle, Hibernate supports both simple case ... when constructs and subqueries (if the underlying database supports them). So your query as it is should work.

My advice is, try to simplify the query by creating a view with the case...when part, and o from there. In that way, you will know if the problem is on that nested group specifically, or is part of the larger query to which you say it belongs.