Hi,
I am trying to setup my database design so that i can have effective dating on my fields. This means the table has three fields that defines which record is latest.
Field are:
ORDER_KEY [ Primary Key, Numeric]
EFFDT [ Date, Effective Date]
EFFSTATS [ char, 'A' for Active & 'D' for deleted]
ORDER_ID [ Parameter to find a Order]
my finder query will be something like :
findByOrderId(int id)
and
findByOrderIdAndDate(int id, java.util.Date inDate)
Both of these queries can return a result based on the criterias that should match. In general SQL statements, I can get these results by running a query like:
select order_key from order
where order_key =
(
select max(order_key) from order
where (effstatus, effdate) = (select 'A', max(effdate) from order)
and effdate < sysdate
and order_id = id
)
I do not understand, how can i proceed with such an implementation in CMR. Anyone has implemented such an thing by converting into non-subquery.
I am using jboss 3.0.6. I dont know how much support i have to write such an query. Are there any workarounds, by which i can implement myself in a query. What are different options? Any help will be great!
Thanks in Advance!
Srini
