Re: [Urgent] Problem retrieving self referencial table details IBATIS

[Jeff Butler <je...@gmail.com>]

Replying to the user list - this is not appropriate for the dev list.

When you do a self join, you will need to rename some of the columns so they
are unique.  For example:

select a.name, b.name as boss_name
from employees a join employees b
on a. boss_id = b.employee_id

This will also mean that you cannot reuse the result maps as you are
attempting to do.

Another option is to not rename the columns, and use column indexes in your
result maps instead of column names.  But again, you will need two different
result maps.  And I would strongly advise against using column indexes when
using "select *".

Jeff Butler

On Tue, Apr 1, 2008 at 6:03 AM, Rajive <ra...@thbs.com> wrote:

>
> 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_OLCwhere
>                (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 :)
> --
> View this message in context:
> http://www.nabble.com/-Urgent--Problem-retrieving-self-referencial-table-details-IBATIS-tp16417881p16417881.html
> Sent from the iBATIS - Dev mailing list archive at Nabble.com<http://nabble.com/>
> .
>
>