Help to write a specific HQL

moonlight · **Joined:** Mon Aug 29, 2005 9:46 am **Posts:** 102

Hibernate version: 3.0

Hello guys,

I'm having a difficult to write a specific HQL. Here's what I want:

I have a table MATERIAL_QTT with the following fields: date, material, color, quantity
And a table MATERIAL with: material, desc, minQuantity.
I wanna select from the material_qtt all the materials which have the quantity less than the specified in material.minQuantity. So far so good. The problem is that I have tuples in material_qtt with same material and color, only different date. Like this:

--> Date = 05/05/2005, Material = "MA", Color = "Red", Quantity= 10
--> Date = 05/07/2005, Material = "MA", Color = "Red", Quantity= 11

And I wanna select only the one with the date closer to the actual date.

Here's what I'm doing:

Code:

 Query select = session.createQuery("from EstoqueMP e where " +
 " e.qtde < e.materia.estoqueMinimo " +
 " and e.data = (select max(data) from EstoqueMP e1" +
 "                where e1.materia = e.materia" +
 "                  and e1.cor = e.cor)" );

But I'm getting the error:

Code:

java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.AggregateNode 
 \-[AGGREGATE] AggregateNode: 'max'
    \-[IDENT] IdentNode: 'data' {originalText=data}

So I suppose max isn't the correct way to do it.

Any help? thanks

Varienaja · **Posted:** Thu Oct 20, 2005 8:05 am

Maybe it's just a typo, but the first thing I would do is change

...and e.data = (select max(data) from EstoqueMP e1...
to
...and e.data = (select max(e1.data) from EstoqueMP e1...

moonlight · **Joined:** Mon Aug 29, 2005 9:46 am **Posts:** 102

Thank you. It did help: now I don't get the same error. But it still isn't giving me the expected result.

I have this rows that have the quantity less than the specified in material.minQuantity:

Code:

                    DATE MATERIAL        COLOR                     QTT
========================= ============== ====== =====================

2005-05-02 00:00:00.0000  1              01                  -10.0000
2005-10-06 00:00:00.0000  1              1                    -6.0000
2005-10-06 00:00:00.0000  3              1                   -23.0000
2005-10-07 00:00:00.0000  1              1                    -5.0000
2003-12-09 00:00:00.0000  1              01                  -10.0000

when I do the HQL above, I get:

Code:

                    DATE MATERIAL        COLOR                     QTT
========================= ============== ====== =====================

2005-10-06 00:00:00.0000  3              1                   -23.0000
2005-10-07 00:00:00.0000  1              1                    -5.0000

and I wanted to get:

Code:

                    DATE MATERIAL        COLOR                     QTT
========================= ============== ====== =====================

2005-05-02 00:00:00.0000  1              01                  -10.0000
2005-10-06 00:00:00.0000  3              1                   -23.0000
2005-10-07 00:00:00.0000  1              1                    -5.0000

See the difference: it returns me one tuple of the material 1, independent of the color. I wanted it to return one for each color. What am I doing wrong?

thanks

pksiv · **Posted:** Thu Oct 20, 2005 9:26 am

What's the datatype of your COLOR column ? It looks to me like it's finding 01=1

moonlight · **Joined:** Mon Aug 29, 2005 9:46 am **Posts:** 102

It shouldn't be, 'cause it's a string. Well, actually... EstoqueMP is a view and not a table. And although it is getting the Color field from a table where it is a String for sure, it could be making some kind of cast... at least is the only thing I can think of now.

How can I cast it to String, or force it do work as a String?

Thanks

moonlight · **Joined:** Mon Aug 29, 2005 9:46 am **Posts:** 102

Anyone?

moonlight · **Joined:** Mon Aug 29, 2005 9:46 am **Posts:** 102

Apparently, one cannot cast using Hibernate :(
If someone knows that it's possible, please tell me

moonlight · **Joined:** Mon Aug 29, 2005 9:46 am **Posts:** 102

Hey, I just found out that my problem is not with String casting.
In sql, when I do like this:

Code:

select e.* from estoquemp e, materiasprima m
where m.materia = e.materia
and m.estoqueminimo > e.qtde
and e.data = (select max(e1.data) from estoquemp e1
               where e.materia = e1.materia
                and cast(e.cor as varchar(02))
                =cast( e1.cor as varchar(02)))

I also get the unexpected results. So I suppose I'm writing the wrong query. Anyway, if someone could help me writing this HQL.. would be good

pksiv · **Posted:** Fri Oct 21, 2005 9:28 am

Have you tried running this against the Table (instead of the view) do you get the same results ?

What about if you run just the sub-select

Code:

Select max(data) from EstoqueMP e1" +
 "                where e1.materia = e.materia" +
 "                  and e1.cor = e.cor)" 

What results do you get ?

moonlight · **Joined:** Mon Aug 29, 2005 9:46 am **Posts:** 102

I can't, because the view is composed of many tables, and "data" is from one while "materia" and "cor" are from others. So wouldn't work like. But in the table it's a varchar, for sure, I've checked. I suppose the view has made some kind of cast and I must cast it back.