0 Replies Latest reply on Mar 16, 2003 12:41 PM by Srinivas

    Effective Date and CMR / EJB-QL / JBOSS-QL ???

    Srinivas Newbie

      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