• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Joining tow tables with aggregate functions

 
Arunesh Gupta
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have following two tables that have many to one relationship.

Candidate_summary:
REGN_CD
SALE_ST_CD
DIST_CD
META_STAGE_ID
SUMMARY_MONTH_DT
CANDIDATE_ACTIVE_CNT
CANDIDATE_ENTER_CNT
CANDIDATE_COMPL_CNT
CANDIDATE_DISQUAL_CNT
TOT_DAYS_COMPL_CNT

and sale_regn:
REGN_CD
REGN_NM
REGN_ABBR
CRTE_ROW_TS

Following are the mapping files and have persistance classes with same variables:
<hibernate-mapping package="com.amfam.isagent.agtplnrpt.databeans">
<class
name="CandidateSummary"
table="CANDIDATE_SUMMARY"
schema="agentpipe"
lazy="false"
>
<id name="meta_stage_id">
<generator class="assigned" />
</id>
<many-to-one name="regn_cd" class="SaleRegions" insert="false"/>
<property name="summary_month_dt" insert="false"/>
<property name="candidate_active_cnt"/>
<property name="regn_nm"/>
<property name="candidate_enter_cnt"/>
<property name="candidate_compl_cnt"/>
<property name="tot_days_compl_cnt"/>

</class>

</hibernate-mapping>


and


<hibernate-mapping package="com.amfam.isagent.agtplnrpt.databeans">
<class
name="SaleRegions"
table="SALE_REGN"
schema="asir"
lazy="false"
>
<id name="regn_cd">
<generator class="assigned" />
</id>

<property name="regn_nm" />
</class>
</hibernate-mapping>

I am running the follong query:
session.createQuery("select sr.regn_cd, sr.regn_nm, cs.meta_stage_id, sum(cs.candidate_compl_cnt), sum(cs.candidate_enter_cnt), sum(cs.tot_days_compl_cnt) from CandidateSummary cs, SaleRegions sr where cs.summary_month_dt >= to_date('May, 2005', 'MON, YYYY') and cs.regn_cd = sr.regn_cd group by cs.regn_cd, sr.regn_nm, cs.meta_stage_id, sr.regn_cd").list();

The query is running fine, only problem is that I am not able to store it into persistence class. The List I get is list of Objects that I am not able to cast into java class.

Any help would be appreciated.

Thank you.
 
Jignesh Patel
Ranch Hand
Posts: 626
Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
register with hibernate.org.
And try to see the link
http://forum.hibernate.org/viewtopic.php?p=2253953#2253953
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic