HQL query help

Hello there,

I have this HQL query which returns all the product details. But I want to get the latest prices only not the old one too. How can I do it.

String select = "SELECT p FROM  ProductPricing p where p.productId.supplierId.id=:id"; // nested condition based on the FK productpricing -> product -> supplier
Query query = hibernateSession.createQuery(select);
query.setParameter("id", Integer.parseInt(dsRequest.getFieldValue("supplier").toString()));

List<ProductPricing> models = query.list();

Open in new window

zolfAsked:
Who is Participating?
 
chaitu chaituConnect With a Mentor Commented:
why dont you go for nativeQUery..write actual table names and column names in the query.

hibernateSession.createNativeQuery("select pp1.id, pp1.priceDate, pp1.purchasePrice, pp1.salesPrice, pp1.consumerPrice
  from product p
        inner join productPricing pp1
    on pp1.productID = p.id
 where p.supplierID=3
   and exists (select pp2.ProductID, MAX(pp2.priceDate) as max_priceDate
                 from productPricing pp2
                where pp2.productID = pp1.productID
                group by pp2.ProductID
               having MAX(pp2.priceDate) = pp1.priceDate) ")
0
 
Jerry MillerCommented:
What other fields are in the ProductPricing table to determine which is the latest? Is there a unique ID for each record or a system entry date field? If so you could use the MAX function for them.

Something like:
String select = "SELECT p, MAX(ID) FROM  ProductPricing p where p.productId.supplierId.id=:id";
0
 
zolfAuthor Commented:
this is the table structure

id priceDate                   purchasePrice  salesPrice   consumerPrice  productID description

1  2012-11-15 00:00:00         1000          1200       1500                           1         qwqw        
2  2013-11-16 00:00:00         1600          1800       2100                           1         new price  
3  2012-10-15 00:00:00         1600          1500       1600                           2         erere  
4  2013-11-02 00:00:00         1600          1900       3400                           2         new price
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
zolfAuthor Commented:
I get this error when I run that query of yours

java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.AggregateNode
 \-[AGGREGATE] AggregateNode: 'MAX'
    \-[IDENT] IdentNode: 'ID' {originalText=ID}
0
 
chaitu chaituCommented:
"SELECT top 1 p FROM  ProductPricing p where p.productId.supplierId.id=:id order by p.productId desc"

ProductPricing pp= query.getSingleResult();
0
 
zolfAuthor Commented:
this HQL is not working "top 1 " is the cause. I get error when I run the hql in eclipse

this is my complete code. from my original post I should only get back the 2nd and 4th record

String select = "SELECT p FROM  ProductPricing p where p.productId.supplierId.id=:id"; // nested condition based on the FK productpricing -> product -> supplier

    Query query = hibernateSession.createQuery(select);
    query.setParameter("id", Integer.parseInt(dsRequest.getFieldValue("supplier").toString()));

    List<ProductPricing> models = query.list();

    for (int i = 0; i < models.size(); i++)
    {
        if (models.get(i).getProductId() != null)
        {
            models.get(i).getProductId().getProductCode();
            models.get(i).getProductId().getBrandName();
            models.get(i).getPurchasePrice();
            System.out.println(models.get(i).getProductId().getBrandName());
        }

    }

Open in new window

0
 
chaitu chaituCommented:
this HQL is not working "top 1 " is the cause. I get error when I run the hql in eclipse

what is the error getting?
0
 
chaitu chaituCommented:
which DB are you using?
0
 
zolfAuthor Commented:
I am using mssql 2008
0
 
zolfAuthor Commented:
the error I get is this

2013-11-25 10:21:25,717 ERROR main org.hibernate.hql.PARSER - line 3:12: unexpected token: 1
2013-11-25 10:21:25,720 WARN main org.hibernate.hql.ast.HqlParser - processEqualityExpression() : No expression to process!
2013-11-25 10:21:25,831 ERROR main org.hibernate.hql.PARSER - line 3:12: unexpected token: 1
2013-11-25 10:21:25,831 WARN main org.hibernate.hql.ast.HqlParser - processEqualityExpression() : No expression to process!
0
 
chaitu chaituCommented:
user query.setMaxResults(1) or query.setFirstResult(1) if you are using createQUery.
if you are using nativeQuery then you can use top 1 sql statement

tring select = "SELECT p FROM  ProductPricing p where p.productId.supplierId.id=:id order by p.productId desc "; // nested condition based on the FK productpricing -> product -> supplier
Query query = hibernateSession.createQuery(select);
query.setParameter("id", Integer.parseInt(dsRequest.getFieldValue("supplier").toString()));
query.setMaxResults(1);
List<ProductPricing> models = query.list();
0
 
zolfAuthor Commented:
thanks for your comments. But this query of yours only returns one record i.e. the first record in the list. but in my case I have many products which needs to be returned with their latest purchase price. I hope you get what is my requirement
0
 
chaitu chaituCommented:
as per the records.

this is the table structure

id priceDate                   purchasePrice  salesPrice   consumerPrice  productID description

1  2012-11-15 00:00:00         1000          1200       1500                           1         qwqw        
2  2013-11-16 00:00:00         1600          1800       2100                           1         new price  
3  2012-10-15 00:00:00         1600          1500       1600                           2         erere  
4  2013-11-02 00:00:00         1600          1900       3400                           2         new price

what is the output you expect from above records?
0
 
zolfAuthor Commented:
the output I expect is the records in bold.

id priceDate                   purchasePrice  salesPrice   consumerPrice  productID description

1  2012-11-15 00:00:00         1000          1200       1500                           1         qwqw        
2  2013-11-16 00:00:00         1600          1800       2100                           1         new price  
3  2012-10-15 00:00:00         1600          1500       1600                           2         erere  
4  2013-11-02 00:00:00         1600          1900       3400                           2         new price

just to make it clearer, the sql query looks like this but in HQL I have no clue to get this to work. I need to return the client the ProductPricing object

select pp1.id, pp1.priceDate, pp1.purchasePrice, pp1.salesPrice, pp1.consumerPrice
  from product p
        inner join productPricing pp1 
    on pp1.productID = p.id
 where p.supplierID=3 
   and exists (select pp2.ProductID, MAX(pp2.priceDate) as max_priceDate 
                 from productPricing pp2 
                where pp2.productID = pp1.productID 
                group by pp2.ProductID 
               having MAX(pp2.priceDate) = pp1.priceDate) 

Open in new window

0
 
zolfAuthor Commented:
thanks for your comments ,but if you see my code which I had added yesterday.one of my requirement is I need to send the ProductPricing object back to the client.

List<ProductPricing> models = query.list();

Open in new window

0
 
zolfAuthor Commented:
I have made some changes to my HQL query,but I get error Path expected for Join. I cannot figure it out where I am making a mistake in my query

from ProductPricing pp1 join Product p where pp1.productId.supplierId.id=2 
   and exists (select pp2.productId, MAX(pp2.priceDate) as max_priceDate
    from ProductPricing pp2 where pp2.productId = pp1.productId
                group by pp2.productId  having MAX(pp2.priceDate) = pp1.priceDate)

Open in new window

0
 
zolfAuthor Commented:
ok, I solved the problem!!.thanks anyway for your comments and help. that made me think more and get to the solution.cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.