Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
3.1

Mapping documents:
CertCertificatesTrns.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<!--
Auto-generated mapping file from
the hibernate.org cfg2hbm engine
-->
<class name="com.sitx.common.certinventory.dto.CertCertificatesTrnsDTO" table="CERT_CERTIFICATES_TRNS" >
<id name="crnSysNo" type="big_decimal">
<column name="CRN_SYS_NO" precision="22" scale="0" />
<generator class="assigned" />
</id>
<many-to-one name="certCertificates" class="com.sitx.common.certinventory.dto.CertCertificatesDTO" fetch="select">
<column name="CRN_CERT_NO" length="10" />
</many-to-one>
<many-to-one name="RLocationTypes" class="com.sitx.common.certinventory.dto.RLocationTypesDTO" fetch="select">
<column name="CRN_LOCATION_TO" length="9" />
</many-to-one>
<many-to-one name="RStateReasonCodes" class="com.sitx.common.certinventory.dto.RStateReasonCodesDTO" fetch="select">
<column name="CRN_REASON_CODE" length="3" />
</many-to-one>
<many-to-one name="RExceptions" class="com.sitx.common.certinventory.dto.RExceptionsDTO" fetch="select">
<column name="CRN_EXCEPTION_CODE" precision="22" scale="0" />
</many-to-one>
<many-to-one name="RCertTypes" class="com.sitx.common.certinventory.dto.RCertTypesDTO" fetch="select">
<column name="CRN_CERT_TYPE" length="3" />
</many-to-one>
<many-to-one name="RLocationTypes_1" class="com.sitx.common.certinventory.dto.RLocationTypesDTO" fetch="select">
<column name="CRN_LOCATION_FROM" length="9" />
</many-to-one>
<many-to-one name="RStateReasonCodes_1" class="com.sitx.common.certinventory.dto.RStateReasonCodesDTO" fetch="select">
<column name="CRN_STATUS_CODE" length="3" />
</many-to-one>
<many-to-one name="certBooks" class="com.sitx.common.certinventory.dto.CertBooksDTO" fetch="select">
<column name="CRN_BOOK_NO" length="12" />
</many-to-one>
<property name="crnIssueDate" type="date">
<column name="CRN_ISSUE_DATE" length="7" />
</property>
<property name="crnCreateLogin" type="string">
<column name="CRN_CREATE_LOGIN" length="30" />
</property>
<property name="crnCreateDt" type="date">
<column name="CRN_CREATE_DT" length="7" />
</property>
<property name="crnUpdateLogin" type="string">
<column name="CRN_UPDATE_LOGIN" length="30" />
</property>
<property name="crnUpdateDt" type="date">
<column name="CRN_UPDATE_DT" length="7" />
</property>
<property name="crnComment" type="string">
<column name="CRN_COMMENT" length="80" />
</property>
<property name="crnCovert" type="string">
<column name="CRN_COVERT" length="4" />
</property>
<property name="crnHeldForEvidence" type="string">
<column name="CRN_HELD_FOR_EVIDENCE" length="4" />
</property>
</class>
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():


I have the following sql:-


SELECT certCertificatesTrnsOuter.certCertificates.ctCertNo,certCertificatesTrnsOuter.RStateReasonCodes_1.RCode, certCertificatesTrnsOuter.crnUpdateDt from CertCertificatesTrnsDTO certCertificatesTrnsOuter
WHERE certCertificatesTrnsOuter.crnUpdateDt in (select max(certCertificatesTrnsInner.crnUpdateDt)
FROM CertCertificatesTrnsDTO certCertificatesTrnsInner WHERE certCertificatesTrnsInner.certCertificates.ctCertNo >
startNo AND certCertificatesTrnsInner.certCertificates.ctCertNo < endNo GROUP BY certCertificatesTrnsInner.certCertificates.ctCertNo AND certCertificatesTrnsOuter.certCertificates.ctCertNo >
startNo AND certCertificatesTrnsOuter.certCertificates.ctCertNo < endNo;


How to convert this same code in Hibernate Projections with DetachedCriteria???


Please do provide a solution for this. Since this is quite urgent.



Name and version of the database you are using:

Oracle 9i




Thanks,
HibermateCrazyLoverr

No replies as yet..

What is it that you're trying to do? Your mapping and SQL are too hard to read (try using code tags), and I don't want to read SQL just to figure out what you want. Especially unformatted SQL, apparently in some dialect I don't understand (why is there a where before the from? What are those two ands doing in a group by clause?)

For even slightly complex SQL, it's best not to translate it to HQL or criteria. Instead, you should solve the same problem from scratch, ignoring the SQL you've been working with up to now. If you explain the problem that your current SQL solves and post sufficient mapping details, I'll have a go at answering your question.

Note that you can leave out unused bits, and if your mapping is large, please do leave out unused bits.

<b>
SELECT certCertificatesTrnsOuter.certCertificates.ctCertNo,certCertificatesTrnsOuter.RStateReasonCodes_1.RCode, certCertificatesTrnsOuter.crnUpdateDt from CertCertificatesTrnsDTO certCertificatesTrnsOuter
WHERE certCertificatesTrnsOuter.crnUpdateDt IN
</b>

is the Outer Query which actually checks whether the crnUpdateDt is IN
max(certCertificatesTrnsInner.crnUpdateDt) where the certificateno.
is > startno and <endno groupby the certficateno.


<b>
(select max(certCertificatesTrnsInner.crnUpdateDt)
FROM CertCertificatesTrnsDTO certCertificatesTrnsInner WHERE certCertificatesTrnsInner.certCertificates.ctCertNo >
startNo AND certCertificatesTrnsInner.certCertificates.ctCertNo < endNo GROUP BY certCertificatesTrnsInner.certCertificates.ctCertNo);
</b>


Now just tell me how to convert this SQL into hibernate projections
with DetachedCriteria support.

I will be waiting for ur reply..



Thanks,

HibernateCrazyLoverr

You forgot to proof-read your tags :) You could use the buttons above the text area, if you prefer. And the preview button is very useful.

Right, now to your query. First, the seond part: you need a subquery with a MAX projection. You can ignore the group by part, the max projection is a grouping projection.Next, the first part: you want all CertCertificatesTrnsDTO instances where the update date is in that subselect:You'll probably have to tweak that a fair bit, I'm working off the javadocs here, I'm not testing as I go :)

If you only want the three columns that your SQL query mention, you'll probably have to switch to HQL or SQL. I don't think Criteria return scalars.

Thanks..tenwit. It worked!!!

Oh good. You could hit the "It helped" link in my post then. Ta!