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:[email protected]]
Sent: Wednesday, 8 April 2009 5:46 p.m.
To: [email protected]
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.
