<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping package="com.optum.allied.model" schema="dbo" catalog="scs_haz">

<class name="Consumer" table="consumer">

<id name="consumerId" column="consumer_id">
<generator class="native" />
</id>


<set name="consumerDetails" lazy="true">
<key column="consumer_id"/>
<one-to-many class="ConsumerDetailView" />

</set>

</class>

<class name="ConsumerDetailView">



<subselect>
select msg_id as id,'Message' as Category, m.subject as Description ,m.status, m.create_date as DateOpened,
m.response_date as DateClosed, m.source_location_id as Requestor,
recipient_location_id as Recipient,outcome, consumer_id from scs_haz.dbo.message m where m.consumer_id=?
union all
select referral_id as id,'Provider Referral' as Category, pv.name as Description,ref.status,ref.request_date as DateOpened,
ref.disposition_date as DateClosed, ref.source_location_id as Requestor,
ref.recipient_location_id as Recipient, ref.outcome, consumer_id
from scs_haz.dbo.referral ref join scs_haz.dbo.provider pv on ref.provider_id = pv.provider_id where ref.consumer_id =? and ref.type='P'
union all
select referral_id as id, 'Health Center Referral' as Category, 'Health Center' as Description, status,request_date as DateOpened,
disposition_date as DateClosed, source_location_id as Requestor,
recipient_location_id as Recipient, outcome, consumer_id
from scs_haz.dbo.referral ref where ref.consumer_id=? and ref.type='H'


</subselect>
<synchronize table="message"/>
<synchronize table="provider"/>
<synchronize table="location"/>
<synchronize table="referral"/>
<composite-id>
<key-property name="id" column="id"/>
<key-property name="category" column="Category"/>
</composite-id>

<property name="category"/>
<property name="description"/>
<property name="status"/>
<property name="dateOpened"/>
<property name="dateClosed"/>
<property name="requestor"/>
<property name="recipient"/>
<property name="outcome"/>
</class>


In sql server if query is as below works with static consumer id.

select msg_id as id,'Message' as Category, m.subject as Description ,m.status, m.create_date as DateOpened,
m.response_date as DateClosed, m.source_location_id as Requestor,
recipient_location_id as Recipient,outcome, consumer_id from scs_haz.dbo.message m where m.consumer_id=209
union all
select referral_id as id,'Provider Referral' as Category, pv.name as Description,ref.status,ref.request_date as DateOpened,
ref.disposition_date as DateClosed, ref.source_location_id as Requestor,
ref.recipient_location_id as Recipient, ref.outcome, consumer_id
from scs_haz.dbo.referral ref join scs_haz.dbo.provider pv on ref.provider_id = pv.provider_id where ref.consumer_id =209 and ref.type='P'
union all
select referral_id as id, 'Health Center Referral' as Category, 'Health Center' as Description, status,request_date as DateOpened,
disposition_date as DateClosed, source_location_id as Requestor,
recipient_location_id as Recipient, outcome, consumer_id
from scs_haz.dbo.referral ref where ref.consumer_id=209 and ref.type='H'




Please let me know how to do in hibernate, I tried ? instead of static value. It failed

Please help..........









Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:


Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html