How to do a Max in a sub query...
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?