cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Weddle, Anthony" <Anthony.Wed...@bluescopesteel.com>
Subject RE: General queries
Date Wed, 08 Apr 2009 23:24:02 GMT
Well, it looks like EJBQL could be the way to go. It's not as powerful
as HQL (or maybe it just doesn't have quite the same features) but it
seems to do a lot of what we might need. It does have some issues
though.

This is part of one HQL query that we use in one report:

select ios,
  (select nvl(sum(iosbu.curQty),0)
	from Iosbu as iosbu
	where iosbu.iosb.ios = ios 
	  and iosbu.iosb.iosbs.iosbStat = 'A'
	  and (iosbu.iosb.useByDt is null
	  or iosbu.iosb.useByDt > sysdate)) as currentPaintQty,
  (select nvl(sum(iosbu.curQty),0)
	from Iosbu as iosbu
	where iosbu.iosb.ios = ios
	and (iosbu.iosb.iosbs.iosbStat != 'A'
	  or iosbu.iosb.useByDt < sysdate)) as heldPaintQty
	  from Ios as ios "

EJBQL doesn't support subselects in the select clause or have something
equivalent to the NVL function, so we'd have to do things differently.
However, I wondered if it could cope with the first subselect and so
ended up with this EJBQL query (accounting for different relationship
names in Cayenne):

select iosbu.toIosb.toIos, sum(iosbu.curQty) 
  from Iosbu as iosbu, Ios as ios 
  where iosbu.toIosb.toIosbs.id = 'A'
    and (iosbu.toIosb.useByDt is null
    or iosbu.toIosb.useByDt > CURRENT_DATE)
  group by iosbu.toIosb.toIos

I know I didn't need the "from Ios" but cayenne didn't convert that
properly, anyway. It created joins for the various relationships, adding
the joined table between Iosbu and Ios, in the from clause, but without
adding another comma. So it ended up with:

SELECT t1.PART_CODE, SUM(t0.CUR_QTY) AS sc0 FROM IOSBU t0, INNER JOIN
IOSB t1 ON (t0.IOSB_ID = t1.IOSB_ID) INNER JOIN IOSBS t3 ON
(t1.IOSB_STAT = t3.IOSB_STAT) IOS t2 WHERE t3.IOSB_STAT = ? AND
t1.USE_BY_DT IS NULL OR t1.USE_BY_DT > {fn CURDATE()} GROUP BY
t1.PART_CODE

Notice the "IOS t2" without a preceeding comma. 

As I didn't need to specify Ios in the from clause, I took it out. Then
I got another problem. Note that selecting the relationship (ending in
"toIos") results in Cayenne selecting the key for that related table
(I'm assuming that Cayenne would subsequently use the key to retrieve
the whole object) but it gets the type wrong. The key is a String but
Cayenne seems to think it's a Long and tries to retrieve a Long from the
result set, which fails.

When I changed the query to select the id of the relationship, then it
worked fine. So this was the final query:

select iosbu.toIosb.toIos.id, sum(iosbu.curQty
  from Iosbu as iosbu
    where iosbu.toIosb.toIosbs.id = 'A'
      and (iosbu.toIosb.useByDt is null
      or iosbu.toIosb.useByDt > CURRENT_DATE)
group by iosbu.toIosb.toIos.id

I'm getting there!

Cheers,
Tony

-----Original Message-----
From: Andrus Adamchik [mailto:andrus@objectstyle.org] 
Sent: Wednesday, 8 April 2009 5:46 p.m.
To: user@cayenne.apache.org
Subject: Re: General queries

Cayenne 3.0 includes support for EJBQLQuery which seem like you what  
you need here:

   http://cayenne.apache.org/doc/ejbqlquery.html

We are following the JPA syntax per JSR-220
(http://jcp.org/aboutJava/communityprocess/final/jsr220/index.html 
). At does support aggregates, separate columns, subqueries, etc. 




NOTICE - This message and any attached files may contain information that is confidential,
legally privileged or proprietary.  It is intended only for use by the intended recipient.
If you are not the intended recipient or the person responsible for delivering the message
to the intended recipient, be advised that you have received this message in error. Any dissemination,
copying, use or re-transmission of this message or attachment, or the disclosure of any information
therein, is strictly forbidden. BlueScope Steel Limited does not represent or guarantee that
this message or attachment is free of errors, virus or interference. 
   
If you have received this message in error please notify the sender immediately and delete
the message.  Any views expressed in this email are not necessarily the views of BlueScope
Steel Limited.

Mime
View raw message