Hi,
I am trying to write a custom <sql-insert>.

<sql-insert>
<![CDATA[

insert into Table_Name
( STG_EMPLY_ALLCTN_TRNSCTN_KY, TRD_KY_ID, TRD_KY, BTCH_ID, FCT_TRD_TRNSCTN_KY, ALLCTN_PRSN_PRCNTG, ORGNTNG_DSK_NM, ACTV_FLG,
MRKTS_EMPLY_KY_ID, MRKTS_EMPLY_KY, EMPLY_ID, EMPLY_RL_KY_ID, EMPLY_RL_KY, EMP_ROLE, LGL_ENTTY_KY, LGL_ENTTY_KY_ID,
ORGNZTN_UNT_KY, ORGNZTN_UNT_KY_ID, FCT_EMPLY_ALLCTN_TRNSCTN_KY,EMPLY_KY,EMPLY_KY_ID)
values
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
( SELECT EMPLY_KY FROM DIM_MARKETS_EMPLOYEE WHERE ACTV_FLG='Y' AND ROWNUM=1 AND MRKTS_EMPLY_ID_EMPL=EMPLY_ID),
( SELECT EMPLY_KY_ID FROM DIM_MARKETS_EMPLOYEE WHERE ACTV_FLG='Y' AND ROWNUM=1 AND MRKTS_EMPLY_ID_EMPL=EMPLY_ID) )

]]>
</sql-insert>

Object saves without any error but no values stored in column EMPLY_KY,EMPLY_KY_ID (all other columns are fine).
These 2 are populated from this subselect sql
( SELECT EMPLY_KY FROM DIM_MARKETS_EMPLOYEE WHERE ACTV_FLG='Y' AND ROWNUM=1 AND MRKTS_EMPLY_ID_EMPL=EMPLY_ID),
( SELECT EMPLY_KY_ID FROM DIM_MARKETS_EMPLOYEE WHERE ACTV_FLG='Y' AND ROWNUM=1 AND MRKTS_EMPLY_ID_EMPL=EMPLY_ID)

where EMPLY_ID is the one of the mapping column and it has a value (EMPLY_ID value is set using setter of the mapping property in java).

Is there any way i can tell hibernate to use one of the mapping column to get the subselect value.

Basically when the sql executes it should show as
SELECT EMPLY_KY_ID FROM DIM_MARKETS_EMPLOYEE WHERE ACTV_FLG='Y' AND ROWNUM=1 AND MRKTS_EMPLY_ID_EMPL=?
instead of
SELECT EMPLY_KY_ID FROM DIM_MARKETS_EMPLOYEE WHERE ACTV_FLG='Y' AND ROWNUM=1 AND MRKTS_EMPLY_ID_EMPL=EMPLY_ID

here EMPLY_ID is the mapping column and hibernate should assign a value from the java property column (getter of EMPLY_ID column mapping property).

Appreciate any help.

Thanks
Ramesh