You need to add remapResults="true" to your select element. Larry
On 10/2/07, Bala (JIRA) <[EMAIL PROTECTED]> wrote: > > [ > https://issues.apache.org/jira/browse/IBATIS-406?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12531733 > ] > > Bala commented on IBATIS-406: > ----------------------------- > > Im also facing the same problem, with slightly diffrent requirement > > my sqlMap entry . > > <select id="querySearch" parameterClass="java.util.HashMap" > resultClass="java.util.HashMap"> > $query$ > </select> > > query ="select c.id from table_C c ,table_M m where m.id=c.id" > > second time im shifting the query like > > query ="select m.name from table_C c ,table_M m where m.id=c.id" > > im getting an error > > Exception:: THREAD: http-8084-Processor20; MSG: [Error getting items: > --- The error occurred in sqlSearch.xml. > --- The error occurred while applying a result map. > --- Check the sqlSearch.querySearch-AutoResultMap. > --- Check the result mapping for the 'ID' property. > --- Cause: java.sql.SQLException: Invalid column name] Error getting items: > --- The error occurred in sqlSearch.xml. > --- The error occurred while applying a result map. > --- Check the sqlSearch.querySearch-AutoResultMap. > --- Check the result mapping for the ID property. > > looks like ,still the old resultClass HasMap object used for new resultset. > > > > Bug in Ibatis sqlMap when one resultMap has multiple results that are > > resultMaps... then the groupby attribute only seems to work for the first > > embedded resultMap. > > --------------------------------------------------------------------------------------------------------------------------------------------------------------------- > > > > Key: IBATIS-406 > > URL: https://issues.apache.org/jira/browse/IBATIS-406 > > Project: iBatis for Java > > Issue Type: Bug > > Components: SQL Maps > > Reporter: Shirley > > Priority: Blocker > > Attachments: IBATIS-406.patch > > > > > > In Ibatis using sqlMaps, we are able to do embedded resultMaps within > > resultMaps. If we have one resultMap with only one result that is a > > resultMap (ie <result property="userAbsence" > > resultMap="User.UserAbsenceResult"/> )then using the groupby attribute has > > the effect of grouping by that property. > > However, if we have one resultMap that has multiple results that are > > resultMap then the group by only works for the first embedded item. > > Below is a working code snippet that uses embedded resultMaps. Adding the > > groupby attribute forces the class that is returned to only return one item > > per groupby key. > > <resultMap id="UserResult" > > class="com.principal.ris.expwf.domain.User" groupBy="userId"> > > <result property="userId" column="USER_ID"/> > > <result property="userName" column="USER_NM"/> > > <result property="authLevelCode" column="AUTH_LEVEL_CD"/> > > <result property="certPrinCode" column="CERT_PRIN_CD"/> > > <result property="locationCode" column="LOCATION_CD"/> > > <result property="currentRoleCode" column="CRNT_ROLE_CD"/> > > <result property="skillChangeFlag" column="SKL_CHG_FLG"/> > > <result property="reprocessFlag" column="RPRCS_FLG"/> > > <result property="productUseCode" column="PRDCT_USE_CD"/> > > <result property="skillOrderFlag" column="SKL_ORD_FLG"/> > > <result property="queueOrderFlag" column="QUEUE_ORD_FLG"/> > > <result property="workGroupOrderCode" column="WRK_GRP_ORD_CD"/> > > <result property="excludeLocationCode" column="EXCL_LOC_CD"/> > > <result property="workflowVersionCode" column="WF_VRNS_CD"/> > > <result property="userAbsence" resultMap="User.UserAbsenceResult"/> > > </resultMap> > > <resultMap id="UserAbsenceResult" > > class="com.principal.ris.expwf.domain.UserAbsence" > > groupBy="absenceBeginDate" > > > <result property="userId" > > column="USER_ID"/> > > <result property="absenceBeginDate" > > column="ABSN_BEG_DT"/> > > <result property="absenceEndDate" > > column="ABSN_END_DT"/> > > <result property="absenceReasonCode" column="ABSN_RSN_CD"/> > > <result property="backupUserId" > > column="BKUP_USER_ID"/> > > <result property="absenceBeginTimeCount" > > column="ABSN_BEG_TM_CNT"/> > > <result property="absenceEndTimeCount" > > column="ABSN_END_TM_CNT"/> > > </resultMap> > > <select id="retrieveUser" parameterClass="java.lang.String" > > resultMap="UserResult"> > > > > SELECT > > U.USER_ID, > > U.USER_NM , > > U.AUTH_LEVEL_CD, > > U.CERT_PRIN_CD, > > U.LOCATION_CD, > > U.CRNT_ROLE_CD, > > U.SKL_CHG_FLG, > > U.RPRCS_FLG, > > U.PRDCT_USE_CD, > > U.SKL_ORD_FLG, > > U.QUEUE_ORD_FLG, > > U.WRK_GRP_ORD_CD, > > U.EXCL_LOC_CD, > > U.WF_VRNS_CD, > > S.SKILL_LEVEL_CD, > > S.SKL_ORD_CD, > > S.SKL_STAT_CD, > > S.SKL_HOME_CD, > > S.SKL_ORD_GRP_CD, > > S.SKL_RORD_CD, > > S.CHK_USR_ID, > > A.ABSN_BEG_DT, > > A.ABSN_END_DT, > > A.ABSN_RSN_CD, > > A.BKUP_USER_ID, > > A.ABSN_BEG_TM_CNT, > > A.ABSN_END_TM_CNT > > from XPRS_WF_USER U > > left outer join XPRS_WF_USER_SKL S > > on U.USER_ID = S.USER_ID > > left outer join XPRS_WF_USER_ABSN A > > on U.USER_ID = A.USER_ID > > where U.USER_ID=#value# > > </select> > > However if there is more than one result that is resultMap then the groupby > > attribute only seems to work for the first embedded item. Example below > > only does the grouping for the first embedded property ie (userAbsence). > > When I switched it to have the userSkill before the userAbsence, then the > > group by only worked for the userSkill. > > <resultMap id="UserResult" class="com.principal.ris.expwf.domain.User" > > groupBy="userId"> > > <result property="userId" column="USER_ID"/> > > <result property="userName" column="USER_NM"/> > > <result property="authLevelCode" > > column="AUTH_LEVEL_CD"/> > > <result property="certPrinCode" > > column="CERT_PRIN_CD"/> > > <result property="locationCode" column="LOCATION_CD"/> > > <result property="currentRoleCode" > > column="CRNT_ROLE_CD"/> > > <result property="skillChangeFlag" column="SKL_CHG_FLG"/> > > <result property="reprocessFlag" column="RPRCS_FLG"/> > > <result property="productUseCode" > > column="PRDCT_USE_CD"/> > > <result property="skillOrderFlag" column="SKL_ORD_FLG"/> > > <result property="queueOrderFlag" > > column="QUEUE_ORD_FLG"/> > > <result property="workGroupOrderCode" column="WRK_GRP_ORD_CD"/> > > <result property="excludeLocationCode" column="EXCL_LOC_CD"/> > > <result property="workflowVersionCode" column="WF_VRNS_CD"/> > > <result property="userAbsence" resultMap="User.UserAbsenceResult"/> > > <result property="userSkill" > > resultMap="User.UserSkillResult"/> > > </resultMap> > > <resultMap id="UserAbsenceResult" > > class="com.principal.ris.expwf.domain.UserAbsence" > > groupBy="absenceBeginDate" > > > <result property="userId" column="USER_ID"/> > > <result property="absenceBeginDate" column="ABSN_BEG_DT"/> > > <result property="absenceEndDate" column="ABSN_END_DT"/> > > <result property="absenceReasonCode" column="ABSN_RSN_CD"/> > > <result property="backupUserId" column="BKUP_USER_ID"/> > > <result property="absenceBeginTimeCount" > > column="ABSN_BEG_TM_CNT"/> > > <result property="absenceEndTimeCount" > > column="ABSN_END_TM_CNT"/> > > </resultMap> > > <resultMap id="UserSkillResult" > > class="com.principal.ris.expwf.domain.UserSkill" groupBy="skillLevelCode"> > > <result property="userId" column="USER_ID"/> > > <result property="skillLevelCode" > > column="SKILL_LEVEL_CD"/> > > <result property="skillOrderCode" column="SKL_ORD_CD"/> > > <result property="skillStatCode" column="SKL_STAT_CD"/> > > <result property="skillHomeCode" column="SKL_HOME_CD"/> > > <result property="skillOrderGroupCode" > > column="SKL_ORD_GRP_CD"/> > > <result property="skillReorderCode" > > column="SKL_RORD_CD"/> > > <result property="checkUserID" column="CHK_USR_ID"/> > > </resultMap> > > > > Planning to get around this by creating separate SQLs for the userAbesence > > and the userSkill. Since we are only expecting 1 userID at a time we don't > > expect much overhead, then from the individual sqls for userSkill and > > userAbsence I can do the group by within the query. Does anyone have any > > other suggestions? > > -- > This message is automatically generated by JIRA. > - > You can reply to this email to add a comment to the issue online. > >
