11:39 a.m.
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#...
Reply to the post :
http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&a...