I am battling putting a subquery together with Appfuse. I am looking at
using either 2 Pojos and mapping them (probably the best way if I can
get some guideline as to the mapping) or alternatively use the xdoclet
@hibernate.property formula=" (select ...etc.. ) on one pojo to generate
the subquery. My problem is in understanding how the things should map
to each other. If I have 2 tables,
Calls with fields cc_callNumber, cc_callDuration
And
CallActivity with fields ca_callNumber, ca_firstUsedDate,
ca_lastUsedDate
There are no foreign key relationships for the callNumber fields in the
2 tables.
I need to perform something like :
Select (c1.cc_callNumber, c1.calls, c1.duration, ca1.firstDate,
ca2.lastDate) from
(select c1.cc_callNumber,count(*) calls, (c1.cc_callDuration/60)
duration
From calls where callNumber = ? c1
Group by c1.cc_callNumber,
(select ca_callNumber, min(ca_firstUsedDate) firstDate from
CallActivity where
Ca_CallNumber = ?
Group by ca_CallNumber) ca1,
(select ca_callNumber, max(ca_lastUsedDate) lastDate from
CallActivity where
Ca_callNumber = ?
Group by ca_CallNumber) ca2
Where
Ca2.ca_callNumber = c1.cc_callNumber and
c1.cc_callNumber = ca1.ca_call_number;
I need to map the results of the above to a Pojo with properties id,
callNumber, calls, duration, firstDateUsed, lastDateUsed to be
eventually displayed as a listForm. I have tried using one Pojo for
calls using the columns for the calls table and then using xdoclet
hibernate tag for the columns of the table CallActivity - something like
* @hibernate.property formula=" (select ca1.ca_callNumber,
to_char(min(ca1.ca_firstUsedDate),'dd/mm/yyyy hh24:mi:ss') from
ca_callActivity ca1 where ca1.ca_callNumberi=cc_callNumber group
ca1.ca_callNumber) ca1"
For the property firstUsedDate
With the above approach, my list returns properly for the properties
mapped directly to the Calls columns, but the mapping to the
callActivity calculated fields are not returned?
What is the best way of doing this in Appfuse, or should I skip
hibernate and use straight jdbc ?
Travers
