Hi
I have two table.ops_rsn and ops_flt_leg
ops_rsn has one composite primary key consisting of six columns.There is no referential integrety established at back end between ops_flt_leg and ops_rsn table.I need to write a query with a outer join between two tables on column ops_flt_leg.irrops_rsn_cd=ops_rsn.rsn_cd.
Hibernate is not supporting oracle (+) sign of making outer join.
Apart from this it is also not supporting hql version following code
select * FROM ops_rsn left outer join Ops_Flt_Leg
on ops_flt_leg.irrops_rsn_cd=ops_rsn.rsn_cd
There is no relationship established in XML of the two table either.
XML of ops_flt_leg is
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Jun 26, 2006 10:09:12 AM by Hibernate Tools 3.1.0.beta5 -->
<hibernate-mapping>
<class name="com.ual.ods.hibernate.OpsFltLeg" table="OPS_FLT_LEG">
<composite-id name="id" class="com.ual.ods.hibernate.OpsFltLegId">
<key-property name="fltNbr" type="java.lang.String">
<column name="FLT_NBR" length="5" />
</key-property>
<key-property name="carrIataCd" type="java.lang.String">
<column name="CARR_IATA_CD" length="4" />
</key-property>
<key-property name="fltLclOrigDt" type="java.util.Date">
<column name="FLT_LCL_ORIG_DT" length="7" />
</key-property>
<key-property name="origArptCd" type="java.lang.String">
<column name="ORIG_ARPT_CD" length="3" />
</key-property>
<key-property name="destArptCd" type="java.lang.String">
<column name="DEST_ARPT_CD" length="3" />
</key-property>
<key-property name="origOccurNbr" type="java.lang.Short">
<column name="ORIG_OCCUR_NBR" precision="3" scale="0" />
</key-property>
<key-property name="destOccurNbr" type="java.lang.Short">
<column name="DEST_OCCUR_NBR" precision="3" scale="0" />
</key-property>
</composite-id>
<many-to-one name="acftEquip" class="com.ual.ods.hibernate.AcftEquip" fetch="select">
<column name="EQUIP_CD" length="4" />
</many-to-one>
<many-to-one name="opsChgRsnByIrropsRsnCd" class="com.ual.ods.hibernate.OpsChgRsn" fetch="select">
<column name="IRROPS_RSN_CD" length="4" />
</many-to-one>
<many-to-one name="opsChgRsnByArrDlyRsnCd" class="com.ual.ods.hibernate.OpsChgRsn" fetch="select">
<column name="ARR_DLY_RSN_CD" length="4" />
</many-to-one>
<many-to-one name="schedFltLeg" class="com.ual.ods.hibernate.SchedFltLeg" update="false" insert="false" fetch="select">
<column name="FLT_NBR" length="5" not-null="true" />
<column name="CARR_IATA_CD" length="4" not-null="true" />
<column name="FLT_LCL_ORIG_DT" length="7" not-null="true" />
<column name="SCHED_LEG_NBR" precision="3" scale="0" not-null="true" />
</many-to-one>
<many-to-one name="acftNose" class="com.ual.ods.hibernate.AcftNose" fetch="select">
<column name="NOSE_NBR" length="4" />
</many-to-one>
<many-to-one name="opsChgRsnByDepDlyRsnCd" class="com.ual.ods.hibernate.OpsChgRsn" fetch="select">
<column name="DEP_DLY_RSN_CD" length="4" />
</many-to-one>
<property name="fltTypeCd" type="java.lang.String">
<column name="FLT_TYPE_CD" length="2" />
</property>
<property name="opsSqnr" type="java.lang.Short">
<column name="OPS_SQNR" precision="3" scale="0" />
</property>
<property name="fltStat" type="java.lang.Integer">
<column name="FLT_STAT" precision="9" scale="0" />
</property>
<property name="origStat" type="java.lang.Integer">
<column name="ORIG_STAT" precision="5" scale="0" />
</property>
<property name="destStat" type="java.lang.Integer">
<column name="DEST_STAT" precision="5" scale="0" />
</property>
<property name="activeInd" type="java.lang.Integer">
<column name="ACTIVE_IND" precision="1" scale="0" />
</property>
<property name="rdoFltNbr" type="java.lang.String">
<column name="RDO_FLT_NBR" length="5" />
</property>
<property name="lclEstDepDtm" type="java.util.Date">
<column name="LCL_EST_DEP_DTM" length="7" />
</property>
<property name="lclEstArrDtm" type="java.util.Date">
<column name="LCL_EST_ARR_DTM" length="7" />
</property>
<property name="gmtEstDepDtm" type="java.util.Date">
<column name="GMT_EST_DEP_DTM" length="7" />
</property>
<property name="gmtEstArrDtm" type="java.util.Date">
<column name="GMT_EST_ARR_DTM" length="7" />
</property>
<property name="lclOutDtm" type="java.util.Date">
<column name="LCL_OUT_DTM" length="7" />
</property>
<property name="lclOffDtm" type="java.util.Date">
<column name="LCL_OFF_DTM" length="7" />
</property>
<property name="lclOnDtm" type="java.util.Date">
<column name="LCL_ON_DTM" length="7" />
</property>
<property name="lclInDtm" type="java.util.Date">
<column name="LCL_IN_DTM" length="7" />
</property>
<property name="gmtOutDtm" type="java.util.Date">
<column name="GMT_OUT_DTM" length="7" />
</property>
<property name="gmtOffDtm" type="java.util.Date">
<column name="GMT_OFF_DTM" length="7" />
</property>
<property name="gmtOnDtm" type="java.util.Date">
<column name="GMT_ON_DTM" length="7" />
</property>
<property name="gmtInDtm" type="java.util.Date">
<column name="GMT_IN_DTM" length="7" />
</property>
<property name="lclBridgeOnDtm" type="java.util.Date">
<column name="LCL_BRIDGE_ON_DTM" length="7" />
</property>
<property name="gmtBridgeOnDtm" type="java.util.Date">
<column name="GMT_BRIDGE_ON_DTM" length="7" />
</property>
<property name="plnEnrteTm" type="java.lang.Integer">
<column name="PLN_ENRTE_TM" precision="5" scale="0" />
</property>
<property name="depGateNbr" type="java.lang.String">
<column name="DEP_GATE_NBR" length="5" />
</property>
<property name="depGateBank" type="java.lang.Integer">
<column name="DEP_GATE_BANK" precision="5" scale="0" />
</property>
<property name="depSubgateNbr" type="java.lang.String">
<column name="DEP_SUBGATE_NBR" length="8" />
</property>
<property name="arrGateNbr" type="java.lang.String">
<column name="ARR_GATE_NBR" length="5" />
</property>
<property name="arrGateBank" type="java.lang.Integer">
<column name="ARR_GATE_BANK" precision="5" scale="0" />
</property>
<property name="arrSubgateNbr" type="java.lang.String">
<column name="ARR_SUBGATE_NBR" length="8" />
</property>
<property name="totPsgrCnt" type="java.lang.Integer">
<column name="TOT_PSGR_CNT" precision="5" scale="0" />
</property>
<property name="psgrFCnt" type="java.lang.Integer">
<column name="PSGR_F_CNT" precision="5" scale="0" />
</property>
<property name="psgrCCnt" type="java.lang.Integer">
<column name="PSGR_C_CNT" precision="5" scale="0" />
</property>
<property name="psgrYCnt" type="java.lang.Integer">
<column name="PSGR_Y_CNT" precision="5" scale="0" />
</property>
<property name="revnuPsgrCCnt" type="java.lang.Integer">
<column name="REVNU_PSGR_C_CNT" precision="6" scale="0" />
</property>
<property name="revnuPsgrFCnt" type="java.lang.Integer">
<column name="REVNU_PSGR_F_CNT" precision="6" scale="0" />
</property>
<property name="revnuPsgrYCnt" type="java.lang.Integer">
<column name="REVNU_PSGR_Y_CNT" precision="6" scale="0" />
</property>
<property name="revnuTotPsgrCnt" type="java.lang.Integer">
<column name="REVNU_TOT_PSGR_CNT" precision="6" scale="0" />
</property>
<property name="tailNbr" type="java.lang.String">
<column name="TAIL_NBR" length="6" />
</property>
<property name="thruPsgrCnt" type="java.lang.Integer">
<column name="THRU_PSGR_CNT" precision="5" scale="0" />
</property>
<property name="totCnctPsgrCnt" type="java.lang.Integer">
<column name="TOT_CNCT_PSGR_CNT" precision="5" scale="0" />
</property>
<property name="arrBagClaimUnit" type="java.lang.String">
<column name="ARR_BAG_CLAIM_UNIT" length="3" />
</property>
<property name="depBagSectNbr" type="java.lang.Integer">
<column name="DEP_BAG_SECT_NBR" precision="5" scale="0" />
</property>
<property name="arrFwt" type="java.lang.Integer">
<column name="ARR_FWT" precision="9" scale="0" />
</property>
<property name="depFwt" type="java.lang.Integer">
<column name="DEP_FWT" precision="9" scale="0" />
</property>
<property name="fnlTogw" type="java.lang.Integer">
<column name="FNL_TOGW" precision="9" scale="0" />
</property>
<property name="shtlInd" type="java.lang.Character">
<column name="SHTL_IND" length="1" />
</property>
<property name="priFltInd" type="java.lang.Integer">
<column name="PRI_FLT_IND" precision="1" scale="0" />
</property>
<property name="busOneInd" type="java.lang.Integer">
<column name="BUS_ONE_IND" precision="1" scale="0" />
</property>
<property name="starFltInd" type="java.lang.Integer">
<column name="STAR_FLT_IND" precision="1" scale="0" />
</property>
<property name="openLogRcdInd" type="java.lang.Integer">
<column name="OPEN_LOG_RCD_IND" precision="1" scale="0" />
</property>
<property name="openLogDisInd" type="java.lang.Integer">
<column name="OPEN_LOG_DIS_IND" precision="1" scale="0" />
</property>
<property name="odsCreateTmstmp" type="java.util.Date">
<column name="ODS_CREATE_TMSTMP" length="11" />
</property>
<property name="odsUpdTmstmp" type="java.util.Date">
<column name="ODS_UPD_TMSTMP" length="11" />
</property>
<property name="depDsprAreaCd" type="java.lang.String">
<column name="DEP_DSPR_AREA_CD" length="3" />
</property>
<property name="depDsprPosNbr" type="java.lang.Integer">
<column name="DEP_DSPR_POS_NBR" precision="5" scale="0" />
</property>
<property name="arrDsprAreaCd" type="java.lang.String">
<column name="ARR_DSPR_AREA_CD" length="3" />
</property>
<property name="arrDsprPosNbr" type="java.lang.Integer">
<column name="ARR_DSPR_POS_NBR" precision="5" scale="0" />
</property>
<property name="lclExptDcsnDtm" type="java.util.Date">
<column name="LCL_EXPT_DCSN_DTM" length="7" />
</property>
<property name="lclPushbackDtm" type="java.util.Date">
<column name="LCL_PUSHBACK_DTM" length="7" />
</property>
<property name="gmtPushbackDtm" type="java.util.Date">
<column name="GMT_PUSHBACK_DTM" length="7" />
</property>
<set name="opsFltCncts" inverse="true">
<key>
<column name="FLT_NBR" length="5" not-null="true" />
<column name="CARR_IATA_CD" length="4" not-null="true" />
<column name="FLT_LCL_ORIG_DT" length="7" not-null="true" />
<column name="ORIG_ARPT_CD" length="3" not-null="true" />
<column name="DEST_ARPT_CD" length="3" not-null="true" />
<column name="ORIG_OCCUR_NBR" precision="3" scale="0" />
<column name="DEST_OCCUR_NBR" precision="3" scale="0" />
</key>
<one-to-many class="com.ual.ods.hibernate.OpsFltCnct" />
</set>
<set name="opsFltLoads" inverse="true">
<key>
<column name="FLT_NBR" length="5" not-null="true" />
<column name="CARR_IATA_CD" length="4" not-null="true" />
<column name="FLT_LCL_ORIG_DT" length="7" not-null="true" />
<column name="ORIG_ARPT_CD" length="3" not-null="true" />
<column name="DEST_ARPT_CD" length="3" not-null="true" />
<column name="ORIG_OCCUR_NBR" precision="3" scale="0" not-null="true" />
<column name="DEST_OCCUR_NBR" precision="3" scale="0" not-null="true" />
</key>
<one-to-many class="com.ual.ods.hibernate.OpsFltLoad" />
</set>
<set name="opsLegSumms" inverse="true">
<key>
<column name="FLT_NBR" length="5" not-null="true" />
<column name="CARR_IATA_CD" length="4" not-null="true" />
<column name="FLT_LCL_ORIG_DT" length="7" not-null="true" />
<column name="ORIG_ARPT_CD" length="3" not-null="true" />
<column name="DEST_ARPT_CD" length="3" not-null="true" />
<column name="ORIG_OCCUR_NBR" precision="3" scale="0" not-null="true" />
<column name="DEST_OCCUR_NBR" precision="3" scale="0" not-null="true" />
</key>
<one-to-many class="com.ual.ods.hibernate.OpsLegSumm" />
</set>
<!-- ADDED BY RAGHAV ON 21-JULY-06 -->
<many-to-one name="arrGate" class="com.ual.ods.hibernate.ArptGate" fetch="select" insert="false" update="false">
<column name="ARR_GATE_NBR"></column>
<column name="DEST_ARPT_CD"></column>
</many-to-one>
<many-to-one name="depGate" class="com.ual.ods.hibernate.ArptGate" fetch="select" insert="false" update="false">
<column name="DEP_GATE_NBR"></column>
<column name="ORIG_ARPT_CD"></column>
</many-to-one>
<many-to-one name="acftTail" class="com.ual.ods.hibernate.AcftTail" fetch="select" insert="false" update="false">
<column name="TAIL_NBR"></column>
</many-to-one>
</class>
</hibernate-mapping>
XML of ops_rsn is
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated May 24, 2007 11:39:50 AM by Hibernate Tools 3.2.0.beta7 -->
<hibernate-mapping>
<class name="com.ual.ods.hibernate.OpsRsn" table="OPS_RSN">
<composite-id name="id" class="com.ual.ods.hibernate.OpsRsnId">
<key-property name="rsnCd" type="java.lang.String">
<column name="RSN_CD" length="4" />
</key-property>
<key-property name="opsRsnDivId" type="java.lang.Short">
<column name="OPS_RSN_DIV_ID" precision="3" scale="0" />
</key-property>
<key-property name="opsRsnGrpId" type="java.lang.Short">
<column name="OPS_RSN_GRP_ID" precision="3" scale="0" />
</key-property>
<key-property name="cnclInd" type="java.lang.Integer">
<column name="CNCL_IND" precision="1" scale="0" />
</key-property>
<key-property name="effDt" type="java.util.Date">
<column name="EFF_DT" length="7" />
</key-property>
<key-property name="exprDt" type="java.util.Date">
<column name="EXPR_DT" length="7" />
</key-property>
</composite-id>
<many-to-one name="opsRsnGrp" class="com.ual.ods.hibernate.OpsRsnGrp" update="false" insert="false" fetch="select">
<column name="OPS_RSN_GRP_ID" precision="3" scale="0" not-null="true" />
</many-to-one>
<many-to-one name="opsRsnDiv" class="com.ual.ods.hibernate.OpsRsnDiv" update="false" insert="false" fetch="select">
<column name="OPS_RSN_DIV_ID" precision="3" scale="0" not-null="true" />
</many-to-one>
<property name="rsnDesc" type="java.lang.String">
<column name="RSN_DESC" length="100" not-null="true" />
</property>
<property name="odsCreateTmstmp" type="java.util.Date">
<column name="ODS_CREATE_TMSTMP" length="11" />
</property>
<property name="odsUpdTmstmp" type="java.util.Date">
<column name="ODS_UPD_TMSTMP" length="11" />
</property>
</class>
</hibernate-mapping>
Pls help me how to make the relationship
Thanks in advance
Saswata
|