Hi,

Below is my HQL and translated SQL.

[select man from Manifest man
join man.manifestPorts as m_port
join man.voyage as voyage1
join man.voyage as voyage2
where voyage1.svcLoop =?
and voyage1.vesselCertificate = ?
and voyage1.voyId = ?
and voyage1.voyDir = ?
and man.callNum =?
and man.customsPort= ?
and voyage2.svcLoop =?
and voyage2.vesselCertificate = ?
and voyage2.voyId = ?
and voyage2.voyDir = ?
and m_port.voyage = voyage2.voyId]

SQL:
[select
manifest0_.MNFST_ID as MNFST1_21_,
manifest0_.DSCH_VOY_ID as DSCH2_21_,
manifest0_.DSCH_PORT_CD as DSCH3_21_,
manifest0_.CALL_NUM as CALL4_21_,
manifest0_.AAO_NUM as AAO5_21_,
manifest0_.UVRN_NUM as UVRN6_21_,
manifest0_.LST_UPD_USR as LST24_21_,
manifest0_.LST_UPD_TS as LST25_21_
from
CanadaDb.dbo.MANIFEST manifest0_
inner join
CanadaDb.dbo.MANIFEST_PORT manifestpo1_
on manifest0_.MNFST_ID=manifestpo1_.MNFST_ID
inner join
CanadaDb.dbo.VOYAGE voyage2_
on manifest0_.DSCH_VOY_ID=voyage2_.VOY_ID
where
voyage2_.SVC_LOOP=?
and voyage2_.VSL_CD=?
and voyage2_.VOY_ID=?
and voyage2_.VOY_DIR=?
and manifest0_.CALL_NUM=?
and manifest0_.DSCH_PORT_CD=?
and voyage2_.SVC_LOOP=?
and voyage2_.VSL_CD=?
and voyage2_.VOY_ID=?
and voyage2_.VOY_DIR=?
and manifestpo1_.LOAD_VOY_ID=voyage2_.VOY_ID]

But i want my sql to be translated like this.

[select
manifest0_.MNFST_ID as MNFST1_21_,
manifest0_.DSCH_VOY_ID as DSCH2_21_,
manifest0_.DSCH_PORT_CD as DSCH3_21_,
manifest0_.CALL_NUM as CALL4_21_,
manifest0_.AAO_NUM as AAO5_21_,
manifest0_.UVRN_NUM as UVRN6_21_,
manifest0_.LST_UPD_USR as LST24_21_,
manifest0_.LST_UPD_TS as LST25_21_
from
CanadaDb.dbo.MANIFEST manifest0_
inner join
CanadaDb.dbo.MANIFEST_PORT manifestpo1_
on manifest0_.MNFST_ID=manifestpo1_.MNFST_ID
inner join
CanadaDb.dbo.VOYAGE voyage1
on manifest0_.DSCH_VOY_ID=voyage2_.VOY_ID
inner join
CanadaDb.dbo.VOYAGE voyage2_
on manifest0_.DSCH_VOY_ID=voyage3_.VOY_ID
where
voyage1_.SVC_LOOP=?
and voyage1_.VSL_CD=?
and voyage1_.VOY_ID=?
and voyage1_.VOY_DIR=?
and manifest0_.CALL_NUM=?
and manifest0_.DSCH_PORT_CD=?
and voyage2_.SVC_LOOP=?
and voyage2_.VSL_CD=?
and voyage2_.VOY_ID=?
and voyage2_.VOY_DIR=?
and manifestpo1_.LOAD_VOY_ID=voyage2_.VOY_ID]

Please advise me on this, can i achieve this or not. If it is possible how can i change my HQL query. Or even in the SQL query its not showing the second reference of voyage, my HQL query still works the way i want?[/code]