Solved

HQL query help

Posted on 2013-11-22
18
484 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
  • 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 500 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
This video teaches viewers about errors in exception handling.

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now