[Urgent] Problem retrieving self referencial table details IBATIS

Hi all,

Please help in solving this problem.

I have a employeeprofile table in which all employee details are stored . but it refers to other tables like appointment status , designation table . So employee profile  table contains IDs which refer to corresponding  status & designation table to fetch Objects of status and designation .
TiLL now it works fine .

But i have an other constraint i.e Authorized signatory(u can think this as reporting manager) which refers to Emp Code in same table (employee profile).

So like the way i achieved results for status and designation objects (Information from their respective tables for the selected record in form of Objects) and it also worked fine .But when i try to obtain auth signatory details it does not work.

What i have done is i have created a Employee profile BO inside  Employee profile BO(Contains relationship)
(same way i had created for status and designation and it was working fine).

wat i obatain as result is all data filled with status BO ,Designation BO , But in Child Employee profile BO data comes as that of parent itself not that of signatory.


Heres a mapping snap :
the code in bold and italic i have added to get signatory details .. else every thing is working fine.

also i have tested the qurey .. query is fetching me results.

I think  problem is in the mapping OR i DONT KNOW WAT TO DO.


<sqlMap namespace="report">

        <resultMap class="com.ing.ofl.bo.dataform.EmployeeProfileBO"
                id="emp_appointment_desg"
                extends="tblEmployeeProfile.employeeProfileBOResult">
                <result property="appointmentStatus"
                        resultMap="tblAppointmentStatus.appointmentStatusBOResult" />
                <result property="designation"
                        resultMap="tblDesignation.designationBOResult" />
               <result property="authSign"
                        resultMap="tblEmployeeProfile.employeeProfileBOResult" />
        </resultMap>

<select id="get_report_by_joinee_name"
                resultMap="emp_appointment_desg"
                parameterClass="com.ing.ofl.bo.AppointmentReportBO">
                select * from tblEmployeeProfile EP LEFT OUTER JOIN
                tblAppointmentStatus APP ON EP.emp_appointment_status =
                APP.appoint_stat_no LEFT OUTER JOIN tblDesignation DEG ON
                EP.emp_designation = DEG.designation_no LEFT OUTER JOIN
                tblEmployeeProfile ASEP ON EP.emp_auth_sign=ASEP.emp_OLC where
                (UPPER(EP.emp_fname) LIKE UPPER(#name:VARCHAR#) or
                UPPER(EP.emp_lname) LIKE UPPER(#name:VARCHAR#) or
                UPPER(EP.emp_fname+EP.emp_lname) LIKE UPPER(#name:VARCHAR#))
        </select>


Please help me with your valuable solutions :)