[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