1

I have a table(employee) with a column value something like below:

SERIAL_NO         EMPLOYEE_ID
~~~~~~~~~         ~~~~~~~~~
01                         1234, 1235, 1236
02                         1238, 1232, 1234
03                         1223, 1233, 1254
04                         1212, 1212, 1211

I would like to fetch this particular value and pass it in the IN clause of another query - in iBatis: say,

select address_line1 from address where employee_id in (select employee_id from employee where serial_no=#SERIALNO#)

I am able to fetch the values from inner query and pass it as a list separately in another ibatis query. Can I achieve this in a single query in ibatis?

How I tried this in ibatis is - I got the output of the inner query and made it as a list and passed into the second ibatis query:

        <resultMap id="ADDRESS_RESULT_MAP" class="java.util.HashMap">
    <result property="ADDRESS_LINE" nullValue="" column="ADDRESS_LINE1" javaType="java.lang.String" jdbcType="VARCHAR"/>
        </resultMap>

            <select id="ADDRESS_SELECT_DETAILS"  resultMap="ADDRESS_RESULT_MAP">
                select address_line1 from address where employee_id in 
    <iterate property="EMPLOYEE_ID" open="(" close=")" conjunction=",">
        #EMPLOYEE_ID[]#
    </iterate>
</select>
bitemouth
  • 123
  • 8
  • Why do you not store only one 'EMPLOYEE_ID' in column? – Evgeniy K. Jul 20 '16 at 10:37
  • @EvgeniyK. This is a data fetched from an external system's report which does not store much details but groups the employee ids based on a criteria. – bitemouth Jul 20 '16 at 10:39
  • Then see how delimiting string: http://stackoverflow.com/questions/2942052/parsing-pipe-delimited-string-into-columns. – Evgeniy K. Jul 20 '16 at 10:43

0 Answers0