[
https://issues.apache.org/jira/browse/IBATIS-486?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12571186#action_12571186
]
John Geiglein commented on IBATIS-486:
--------------------------------------
OK, the problem is the use of indexed parameters within the SQL fragments, the
values can't be resolved because the current index value for the iterator is
not referenced and you get a format exception trying to parse the index value
back from an empty string.
ie "codeTypeList[].codes[].codeId" instead of "codeTypeList[0].codes[1].codeId"
If the fragments could look up the stack to the preceding iterator, this could
be fixed. To make it work now, I need to collapse all the fragments that are
descendants of the first iterator, into the same SQL fragment.
I did this and it works. But the resultant map will be impossible to maintain.
A different way to fix this would be to inline all the fragments when the map
is first parsed.
> com.ibatis.sqlmap.engine.mapping.sql.dynamic.DynamicSql.getParameterMap()
> does not build a complete ParameterMap for complex maps
> ---------------------------------------------------------------------------------------------------------------------------------
>
> Key: IBATIS-486
> URL: https://issues.apache.org/jira/browse/IBATIS-486
> Project: iBatis for Java
> Issue Type: Bug
> Components: SQL Maps
> Affects Versions: 2.3.0
> Environment: Windows XP; JDK 1.4.2_13; Apache Tomcat 5.0.19; Oracle
> 10.2.0
> Reporter: John Geiglein
>
> map:
> <sql id="ct_join_fragment">
> <isEqual property="codeTypeList[].join"
> compareValue="AND">AND</isEqual><isEqual property="codeTypeList[].join"
> compareValue="OR">OR</isEqual>
> </sql>
> <sql id="ct_min_max_fragment">
> <isEmpty property="codeTypeList[].max">
> p.appl_id in (select appl_id from indexer_appl_codes_info_vw acd,
> indexer_codes_info_vw cde where acd.cde_id = cde.id and cde.ctp_id =
> #codeTypeList[].codeTypeId# and acd.percent_relevance <![CDATA[ >= ]]>
> #codeTypeList[].min#)
> </isEmpty>
> <isNotEmpty property="codeTypeList[].max">
> p.appl_id in (select appl_id from indexer_appl_codes_info_vw
> acd, indexer_codes_info_vw cde where acd.cde_id = cde.id and cde.ctp_id =
> #codeTypeList[].codeTypeId# and acd.percent_relevance between
> #codeTypeList[].min# and #codeTypeList[].max#)
> </isNotEmpty>
> </sql>
> <sql id="ct_max_fragment">
> p.appl_id in (select appl_id from indexer_appl_codes_info_vw acd,
> indexer_codes_info_vw cde where acd.cde_id = cde.id and cde.ctp_id =
> #codeTypeList[].codeTypeId# and acd.percent_relevance <![CDATA[ <= ]]>
> #codeTypeList[].max#)
> </sql>
> <sql id="ct_coded_fragment">
> <isEqual property="codeTypeList[].coded" compareValue="yes">
> p.appl_id in (select appl_id from indexer_appl_codes_info_vw acd,
> indexer_codes_info_vw cde where acd.cde_id = cde.id and cde.ctp_id =
> #codeTypeList[].codeTypeId# )
> </isEqual>
> <isEqual property="codeTypeList[].coded" compareValue="no">
> p.appl_id not in (select appl_id from indexer_appl_codes_info_vw
> acd, indexer_codes_info_vw cde where acd.cde_id = cde.id and cde.ctp_id =
> #codeTypeList[].codeTypeId# )
> </isEqual>
> <isNotEmpty property="codeTypeList[].min">
> <include refid="ct_join_fragment"/> <include
> refid="ct_min_max_fragment"/>
> </isNotEmpty>
> <isEmpty property="codeTypeList[].min">
> <isNotEmpty property="codeTypeList[].max">
> <include refid="ct_join_fragment"/> <include
> refid="ct_max_fragment"/>
> </isNotEmpty>
> </isEmpty>
> </sql>
> <sql id="code_min_max_fragment">
> <isEmpty property="codeTypeList[].codes[].max">
> <![CDATA[ and p.appl_id in (select appl_id from
> indexer_appl_codes_info_vw where cde_id = #codeTypeList[].codes[].codeId# and
> percent_relevance >= #codeTypeList[].codes[].min#) ]]>
> </isEmpty>
> <isNotEmpty property="codeTypeList[].codes[].max">
> and p.appl_id in (select appl_id from
> indexer_appl_codes_info_vw where cde_id = #codeTypeList[].codes[].codeId# and
> percent_relevance between #codeTypeList[].codes[].min# and
> #codeTypeList[].codes[].max#)
> </isNotEmpty>
> </sql>
> <sql id="code_max_fragment">
> <![CDATA[ and p.appl_id in (select appl_id from
> indexer_appl_codes_info_vw where cde_id = #codeTypeList[].codes[].codeId# and
> percent_relevance <= #codeTypeList[].codes[].max#) ]]>
> </sql>
> <sql id="code_coded_fragment">
> <isEqual property="codeTypeList[].codes[].coded" compareValue="yes">
> p.appl_id in (select appl_id from indexer_appl_codes_info_vw
> where cde_id = #codeTypeList[].codes[].codeId# )
> </isEqual>
> <isEqual property="codeTypeList[].codes[].coded" compareValue="no">
> p.appl_id not in (select appl_id from indexer_appl_codes_info_vw
> where cde_id = #codeTypeList[].codes[].codeId# )
> </isEqual>
> <isNotEmpty property="codeTypeList[].codes[].min">
> <include refid="ct_join_fragment"/> <include
> refid="code_min_max_fragment"/>
> </isNotEmpty>
> <isEmpty property="codeTypeList[].codes[].min">
> <isNotEmpty property="codeTypeList[].codes[].max">
> <include refid="ct_join_fragment"/> <include
> refid="code_max_fragment"/>
> </isNotEmpty>
> </isEmpty>
> </sql>
> <sql id="cl_fragment">
> <isNotEmpty property="codeTypeList[].codes[].coded">
> <include refid="code_coded_fragment"/>
> </isNotEmpty>
> <isEmpty property="codeTypeList[].codes[].coded">
> <isNotEmpty property="codeTypeList[].codes[].min">
> <include refid="code_min_max_fragment"/>
> </isNotEmpty>
> <isEmpty property="codeTypeList[].codes[].min">
> <isNotEmpty property="codeTypeList[].codes[].max">
> <include refid="code_max_fragment"/>
> </isNotEmpty>
> </isEmpty>
> </isEmpty>
> </sql>
> <sql id="codeList_fragment">
> <isNotEqual property="codeTypeList[].join" compareValue="OR">
> <iterate property="codeTypeList[].codes" conjunction="AND">
> <include refid="cl_fragment"/>
> </iterate>
> </isNotEqual>
> <isEqual property="codeTypeList[].join" compareValue="OR">
> <iterate property="codeTypeList[].codes" conjunction="OR">
> <include refid="cl_fragment"/>
> </iterate>
> </isEqual>
> </sql>
> <sql id="codeTypeList_fragment">
> <iterate prepend="AND" property="codeTypeList" open="(" close=")"
> conjunction="AND">
> (
> <isNotEmpty property="codeTypeList[].coded">
> <include refid="ct_coded_fragment"/>
> <isNotEmpty property="codeTypeList[].codes">
> <include refid="ct_join_fragment"/> <include
> refid="codeList_fragment"/>
> </isNotEmpty>
> </isNotEmpty>
> <isEmpty property="codeTypeList[].coded">
> <isNotEmpty property="codeTypeList[].min">
> <include refid="ct_min_max_fragment"/>
> <isNotEmpty property="codeTypeList[].codes">
> <include refid="ct_join_fragment"/> <include
> refid="codeList_fragment"/>
> </isNotEmpty>
> </isNotEmpty>
> <isEmpty property="codeTypeList[].min">
> <isNotEmpty property="codeTypeList[].max">
> <include refid="ct_max_fragment"/>
> <isNotEmpty property="codeTypeList[].codes">
> <include refid="ct_join_fragment"/> <include
> refid="codeList_fragment"/>
> </isNotEmpty>
> </isNotEmpty>
> <isEmpty property="codeTypeList[].max">
> <isNotEmpty property="codeTypeList[].codes">
> <include refid="codeList_fragment"/>
> </isNotEmpty>
> </isEmpty>
> </isEmpty>
> </isEmpty>
> )
> </iterate>
> </sql>
> <statement id="getApplsforIndexer" resultMap="result" >
> select p.appl_id,decode((select count(*) from
> indexer_appl_codes_info_vw iad
> where iad.appl_id = p.appl_id),0,'N','Y') as
> coded_flag
> from $grantInfoTableName$
> <dynamic prepend="WHERE">
> <isNotEmpty prepend="AND" property="fy">
> p.FY =#fy#
> </isNotEmpty>
> <isEmpty prepend="AND" property="fy">
> <isNotEmpty property="council">
> p.COUNCIL_MEETING_DATE =#council#
> </isNotEmpty>
> </isEmpty>
> <isNotEmpty prepend="AND" property="typeCode">
> p.APPL_TYPE_CODE =#typeCode#
> </isNotEmpty>
> <isNotEmpty prepend="AND" property="activityCode">
> p.ACTIVITY_CODE =#activityCode#
> </isNotEmpty>
> <isNotEmpty prepend="AND" property="adminCode">
> p.ADMIN_PHS_ORG_CODE=#adminCode#
> </isNotEmpty>
> <isNotEmpty prepend="AND" property="supportYear">
> p.SUPPORT_YEAR=#supportYear#
> </isNotEmpty>
> <isNotEmpty prepend="AND" property="suffixCode">
> p.SUFFIX_CODE like #suffixCode#
> </isNotEmpty>
> <isNotEmpty prepend="AND" property="cancerActivity">
> p.appl_id in (select appl_id from appl_cancer_activities_t
> where cay_code = #cancerActivity# and end_date is null)
> </isNotEmpty>
> <isNotEmpty prepend="AND" property="pdName">
> p.appl_id in (select appl_id from application_pds_t app,
> nci_person_org_roles_t npe where app.npe_id = npe.id and npe.epn_id =#pdName#)
> </isNotEmpty>
> <isNotEmpty prepend="AND" property="pdCode">
> p.appl_id in (select appl_id from application_pds_t app,
> nci_person_org_roles_t npe where app.npe_id = npe.id and npe.role_usage_code
> =#pdCode#)
> </isNotEmpty>
> <isNotEmpty prepend="AND" property="division">
> nci_util.f_get_appl_pd_orgtid(appl_id,'DIVISION') =#division#
> </isNotEmpty>
> <isNotEmpty prepend="AND" property="rfaPaNum">
> p.RFA_PA_NUMBER like #rfaPaNum#
> </isNotEmpty>
> <isNotEmpty prepend="AND" property="selectFunding">
> NCI.APPL_PAYCODE_PKG.get_pay_status(p.appl_id,'D') = 'Y'
> </isNotEmpty>
> <isNotEmpty prepend="AND" property="piLastName">
> p.LAST_NAME like #piLastName#
> </isNotEmpty>
> <isNotEmpty prepend="AND" property="piFirstName">
> p.FIRST_NAME like #piFirstName#
> </isNotEmpty>
> <isNotEmpty prepend=" AND " property="institution">
> p.ORG_NAME like #institution#
> </isNotEmpty>
> <isNotEmpty prepend="AND" property="stateCode">
> p.STATE_CODE =#stateCode#
> </isNotEmpty>
> <isNotEmpty prepend="AND" property="serialNum">
> p.SERIAL_NUM =#serialNum#
> </isNotEmpty>
> <isEqual property="subprojects" compareValue="P" prepend="AND">
> p.SUBPROJECT_ID is null
> </isEqual>
> <isEqual property="subprojects" compareValue="S" prepend="AND">
> p.SUBPROJECT_ID is not null
> </isEqual>
> <isNotEmpty property="codeTypeList">
> <include refid="codeTypeList_fragment"/>
> </isNotEmpty>
> </dynamic>
> order by admin_phs_org_code ASC, serial_num ASC, support_year ASC,
> suffix_code ASC NULLS FIRST
> <dynamic>
> <isNotEqual property="grantInfoTableName"
> compareValue="NCI.NCI_BASIC_APPL_VW p">
> , subproject_id ASC NULLS FIRST
> </isNotEqual>
> </dynamic>
> </statement>
> This is the map for a complex search screen. not all of the parameters from
> the "code_min_max_fragment" are found. Specifically only the params from the
> first conditional branch.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
