On Apr 9, 2009, at 2:24 AM, Weddle, Anthony wrote:
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.
Correct.
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
...
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.
Looks like a bug. Care to open a Jira with this example?
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.
I guess I can comment on why this happened if you provide a sample
DataMap that includes mapping of all these relationships. I won't
exclude a possibility of a bug. So maybe you can attach a (subset of)
DataMap to the Jira above and mention this problem as well.
Thanks,
Andrus
