Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

HQL query help

Posted on 2013-11-22
18
Medium Priority
?
537 Views
Last Modified: 2013-11-26
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

0
Comment
Question by:zolf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
18 Comments
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 39671548
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
 

Author Comment

by:zolf
ID: 39672217
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
 

Author Comment

by:zolf
ID: 39672218
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
AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 39673768
"SELECT top 1 p FROM  ProductPricing p where p.productId.supplierId.id=:id order by p.productId desc"

ProductPricing pp= query.getSingleResult();
0
 

Author Comment

by:zolf
ID: 39673774
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
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 39673780
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
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 39673781
which DB are you using?
0
 

Author Comment

by:zolf
ID: 39673785
I am using mssql 2008
0
 

Author Comment

by:zolf
ID: 39673793
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
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 39673813
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
 

Author Comment

by:zolf
ID: 39673837
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
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 39673845
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
 

Author Comment

by:zolf
ID: 39673860
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
 
LVL 20

Accepted Solution

by:
chaitu chaitu earned 1500 total points
ID: 39673945
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
 

Author Comment

by:zolf
ID: 39676652
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
 

Author Comment

by:zolf
ID: 39676673
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
 

Author Comment

by:zolf
ID: 39676684
ok, I solved the problem!!.thanks anyway for your comments and help. that made me think more and get to the solution.cheers
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question