[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - How to do a Max in a sub query...
arnieOag
do-not-reply at jboss.com
Thu Jan 10 12:39:25 EST 2008
I need to select records that are linked together based on two parameters, one in table a the other in table b. The named query below is the item in question.
| <class name="us.tx.state.oag.olrap.db.InfoApplication" table="info_application">
|
| <id name="applicationId" type="java.math.BigDecimal">
| <column name="application_id" precision="18" scale="0" />
| <generator class="assigned" />
| </id>
| <many-to-one name="codeFiscalYear" class="us.tx.state.oag.olrap.db.CodeFiscalYear"
| fetch="select" lazy="false">
| <column name="fiscal_year_id" precision="18" scale="0" not-null="true" />
| </many-to-one>
| <many-to-one name="codeEvalStatus" class="us.tx.state.oag.olrap.db.CodeEvalStatus"
| fetch="select" lazy="false">
| <column name="evaluation_id" precision="18" scale="0" not-null="true" />
| </many-to-one>
| <many-to-one name="codeStatus" class="us.tx.state.oag.olrap.db.CodeStatus"
| fetch="select" lazy="false">
| <column name="status_id" precision="18" scale="0" not-null="true" />
| </many-to-one>
| <property name="baseAward" type="java.math.BigDecimal">
| <column name="base_award" precision="11" not-null="true" />
| </property>
| <property name="closedDate" type="java.sql.Timestamp">
| <column name="closed_date" length="23" />
| </property>
| <property name="createdDate" type="java.sql.Timestamp">
| <column name="created_date" length="23" not-null="true" />
| </property>
| <property name="emplid" type="java.lang.String">
| <column name="emplid" length="11" not-null="true" />
| </property>
| <property name="hireDate" type="java.sql.Timestamp">
| <column name="hire_date" length="23" not-null="true" />
| </property>
| <property name="jobCode" type="java.lang.String">
| <column name="job_code" length="6" not-null="true" />
| </property>
| <property name="lastEvalDate" type="java.sql.Timestamp">
| <column name="last_eval_date" length="23" />
| </property>
| <property name="monthsOfService" type="java.math.BigDecimal">
| <column name="months_of_service" precision="4" scale="0" not-null="true" />
| </property>
| <property name="okWithBar" type="java.lang.Integer">
| <column name="ok_with_bar" not-null="true" />
| </property>
| <property name="submittedDate" type="java.sql.Timestamp">
| <column name="submitted_date" length="23" not-null="false" />
| </property>
| <property name="calculatedAward" type="java.math.BigDecimal">
| <column name="calculated_award" precision="11" not-null="true" />
| </property>
| <property name="actualAward" type="java.math.BigDecimal">
| <column name="actual_award" precision="18" not-null="true" />
| </property>
| <property name="awardDate" type="java.sql.Timestamp">
| <column name="award_date" length="23" />
| </property>
| <property name="crWho" type="java.lang.String">
| <column name="cr_who" length="20" not-null="true" />
| </property>
| <property name="crWhen" type="java.sql.Timestamp">
| <column name="cr_when" length="23" not-null="true" />
| </property>
| <property name="upWho" type="java.lang.String">
| <column name="up_who" length="20" not-null="true" />
| </property>
| <property name="upWhen" type="java.sql.Timestamp">
| <column name="up_when" length="23" not-null="true" />
| </property>
| <set name="infoLogs" inverse="true" lazy="true" fetch="select" cascade="all-delete-orphan">
| <key>
| <column name="application_id" precision="18" scale="0" not-null="true" />
| </key>
| <one-to-many class="us.tx.state.oag.olrap.db.InfoLog" />
| </set>
| <set name="infoLenders" inverse="true" lazy="true" fetch="select" cascade="all-delete-orphan">
| <key>
| <column name="application_id" precision="18" scale="0" not-null="true" />
| </key>
| <one-to-many class="us.tx.state.oag.olrap.db.InfoLender" />
| </set>
| <set name="infoWorkflows" inverse="true" lazy="false" fetch="select" cascade="all-delete-orphan">
| <key>
| <column name="application_id" precision="18" scale="0" not-null="true" />
| </key>
| <one-to-many class="us.tx.state.oag.olrap.db.InfoWorkflow" />
| </set>
| <set name="infoLoans" inverse="true" lazy="true" fetch="select" cascade="all-delete-orphan">
| <key>
| <column name="application_id" precision="18" scale="0" not-null="true" />
| </key>
| <one-to-many class="us.tx.state.oag.olrap.db.InfoLoan" />
| </set>
| <set name="infoEmployees" inverse="true" lazy="false" fetch="select" cascade="all-delete-orphan" >
| <key>
| <column name="application_id" precision="18" scale="0" not-null="true" />
| </key>
| <one-to-many class="us.tx.state.oag.olrap.db.InfoEmployee"/>
| </set>
| </class>
|
| <query name="ApplicationsByHrEmployee">
| from us.tx.state.oag.olrap.db.InfoApplication as apps
| where apps.codeStatus.statusId = :codeStatus
| and apps.infoWorkflows.emplid = :emplId
| order by apps.createdDate asc
| </query>
|
The workflow definition is:
| <?xml version="1.0" encoding="utf-8"?>
| <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
| "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
| <!--
| Mapping file autogenerated by MyEclipse Persistence Tools
| -->
| <hibernate-mapping>
| <class name="us.tx.state.oag.olrap.db.InfoWorkflow" table="info_workflow">
| <id name="workflowId" type="java.math.BigDecimal">
| <column name="workflow_id" precision="18" scale="0" />
| <generator class="assigned" />
| </id>
| <many-to-one name="infoApplication" class="us.tx.state.oag.olrap.db.InfoApplication"
| fetch="select" lazy="proxy">
| <column name="application_id" precision="18" scale="0" not-null="true" />
| </many-to-one>
| <many-to-one name="codeStatus" class="us.tx.state.oag.olrap.db.CodeStatus"
| fetch="select" lazy="false">
| <column name="status_id" precision="18" scale="0" not-null="true" />
| </many-to-one>
| <property name="commentary" type="java.lang.String">
| <column name="commentary" not-null="true" />
| </property>
| <property name="emplid" type="java.lang.String">
| <column name="emplid" length="11" not-null="true" />
| </property>
| <property name="entryDate" type="java.sql.Timestamp">
| <column name="entry_date" length="23" not-null="true" />
| </property>
| <property name="crWho" type="java.lang.String">
| <column name="cr_who" length="20" not-null="true" />
| </property>
| <property name="crWhen" type="java.sql.Timestamp">
| <column name="cr_when" length="23" not-null="true" />
| </property>
| <property name="upWho" type="java.lang.String">
| <column name="up_who" length="20" not-null="true" />
| </property>
| <property name="upWhen" type="java.sql.Timestamp">
| <column name="up_when" length="23" not-null="true" />
| </property>
| </class>
|
| <sql-query name="getCurrentWorkflowForApplication" callable="true">
| <return alias="workflow" class="us.tx.state.oag.olrap.db.InfoWorkflow"/>
| { call get_crnt_wkfl_for_app_spr(?) }
| </sql-query>
| </hibernate-mapping>
|
The problem is, the named query needs to include a max() on entry_date from the workflow table, basically, this the SQL I need:
| select * from info_application a, info_workflow b
| where a.application_id = b.application_id
| and b.entry_date = (select max(entry_date) from info_workflow c
| where a.application_id = c.application_id)
| and a.status_id = :codeStatus and b.emplid = :emplId
|
The SQL works, in that it gives me the most recent record for that employee and type. But I cannot figure out how to write that in HQL.
Can anyone help me?
View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4118765#4118765
Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4118765
More information about the jboss-user
mailing list