Can HQL join subqueries?

[marducci]

Can someone please help me turn the following sql statement into an
nhibernate HQL query?

select * from mmt_storage_product m inner join
(select storage_ID, max(asofdatetime) as asofdatetime from
mmt_storage_product s group by storage_ID ) s
on m.asofdatetime = s.asofdatetime

This is my attempt so far: The naming is a bit different for my HQL
because of changes in class names and paramters.

session.CreateQuery("from StorageProduct m inner join (select
storageID, max(asOfDateTime) as asOfDateTime from StorageProduct s) on
m.asOfDateTime = s.asOfDateTime")
.List<BLL.Storage.StorageProduct>();

Thanks so much.

[marducci]

I just found another query that satisfies what i need. The one below
might be easier to write in HQL.

SELECT * FROM mmt_storage_product
WHERE asofdatetime IN (SELECT MAX(asofdatetime) FROM
mmt_Storage_product GROUP BY storage_id)

For the above query I came up with this below HQL: It runs but
doesn't return the correct number of results.

session.CreateQuery("from StorageProduct m where m.asOfDateTime in
(select max(s.asOfDateTime) from StorageProduct s)")
.List<BLL.Storage.StorageProduct>();

This is the query NHibernate produces for me: It is a mouth full.

select storagepro0_.Storage_Product_ID as Storage1_31_,
storagepro0_.Volume as Volume31_, storagepro0_.AsOfDateTime as
AsOfDate3_31_, storagepro0_.CreationName as Creation4_31_,
storagepro0_.CreationDate as Creation5_31_, storagepro0_.RevisionName
as Revision6_31_, storagepro0_.RevisionDate as Revision7_31_,
storagepro0_.Storage_ID as Storage8_31_, storagepro0_.Product_ID as
Product9_31_, storagepro0_.Volume_Unit_ID as Volume10_31_,
storagepro0_.Product_Movement_ID as Product11_31_ from
MMT_Storage_Product storagepro0_ where (storagepro0_.AsOfDateTime in
(select max(storagepro1_.AsOfDateTime) from MMT_Storage_Product
storagepro1_))

[sirrocco]

You forgot to put the Group By in the HQL query.

[sirrocco]

You forgot the Group By in the HQL query.

[Jan Benny Thomas]

Why should he need a Group By clause?

[marducci]

Wow I can't believe I missed the group by in the HQL. It was a late
night. So I added the group by and below is what it looks like,
however Nhibernate throws this: NHibernate.QueryException: in
expected: [HQL goes here] Still googling this error message, any
ideas? I pasted the full error at the bottom of this post.

//my HQL

session.CreateQuery("from StorageProduct m inner join (select

s.storageID, max(s.asOfDateTime) as asOfDateTime from StorageProduct s
group by s.storageID) on m.asOfDateTime =

s.asOfDateTime") .List<BLL.Storage.StorageProduct>();

//the error
NHibernate.QueryException: in expected: select [from
MMT.Core.BLL.Storage.StorageProduct m inner join (select s.storageID,
max(s.asOfDateTime) as asOfDateTime from
MMT.Core.BLL.Storage.StorageProduct s group by s.storageID) on
m.asOfDateTime = s.asOfDateTime]

[sirrocco]

It's a bit strange mate :)

Here's your first HQL :

"from StorageProduct m where m.asOfDateTime in (select max
(s.asOfDateTime) from StorageProduct s)"

and should be :

"from StorageProduct m where m.asOfDateTime in (select max

(s.asOfDateTime) from StorageProduct s group by s.storage_id)"

But now you have :

"from StorageProduct m inner join (select s.storageID, max
(s.asOfDateTime) as asOfDateTime from StorageProduct s group by
s.storageID) on m.asOfDateTime = s.asOfDateTime"

I am refering to your seccond post.

[Fabio Maulo]

HQL probably yes.... but not in NH.

2008/11/26 sirrocco <xavi...@gmail.com>

--
Fabio Maulo

[Koushik Paul]

There was only one column in the IN clause, when I have to match with multiple column, in SQL I use join with ON clause " ON a.x=b.x and a.y=b.y)

Is it possible in HQL also ?