About Question enthuware.oce-jpad.v6.2.481 :

Moderator: admin

Post Reply
himaiMinh
Posts: 358
Joined: Fri Nov 29, 2013 8:26 pm
Contact:

About Question enthuware.oce-jpad.v6.2.481 :

Post by himaiMinh » Wed Jul 05, 2017 9:40 am

Hi, I used the application program, ToyAppForJPA
to try this:

"select co.id , li.quantity from CustOrder co join co.lineItems li where li.quantity =
(select max(li.quantity) from li)"

It returns the same result as:

select co.id, li.quantity from CustOrder co join co.lineItems li where li.quanity=
(Select max(line.quantity) from OrderLineItem line)

So, in this case, the last option can be correct as well.

__JJ__
Posts: 125
Joined: Thu Jul 05, 2018 6:44 pm
Contact:

Re: About Question enthuware.oce-jpad.v6.2.481 :

Post by __JJ__ » Wed Sep 19, 2018 11:07 am

himaiMinh wrote:
Wed Jul 05, 2017 9:40 am
Hi, I used the application program, ToyAppForJPA
to try this:

"select co.id , li.quantity from CustOrder co join co.lineItems li where li.quantity =
(select max(li.quantity) from li)"

It returns the same result as:

select co.id, li.quantity from CustOrder co join co.lineItems li where li.quanity=
(Select max(line.quantity) from OrderLineItem line)

So, in this case, the last option can be correct as well.

Code: Select all

CriteriaQuery<CustOrder> cq = cb.createQuery(CustOrder.class); 
Root<CustOrder> custOrderRoot = cq.from(CustOrder.class); 
Join<CustOrder, OrderLineItem> oliJoin = custOrderRoot.join("lineItems");             
cq.select(custOrderRoot).distinct(true); 
Subquery<Integer> sq = cq.subquery(Integer.class); 
Join<CustOrder, OrderLineItem> sqRoot = sq.correlate(oliJoin); 
sq.select(cb.max(sqRoot.get(OrderLineItem_.quantity))); 
cq.where( cb.equal(oliJoin.get(OrderLineItem_.quantity),  cb.all(sq)) );

It looks to me like this code finds the max quantity per CustOrder in the main query rather than the max quantity for all OrderLineItems.

Post Reply

Who is online

Users browsing this forum: No registered users and 4 guests