Hi All, Can you please help on this N+1 query below. Regards
Tamil _____________________________________________ From: Tamilselvan Radha Krishnan Sent: Monday, November 27, 2006 11:09 PM To: '[email protected]' Subject: Need help on nested N + 1 query Hi, I've written a N + 1 query in Ibatis such a way that it will return the nested results as below : Customer - First object DealFacility - An array list of objects within Customer Facility - An array list of objects within DealFacility LoanInfo - An array list of objects within Facility The Customer object is having a 'strCustomerId' as an attribute. I need to use this 'strCustomerId' in the WHERE clause of the query used for populating 'LoanInfo' (<select id="getFacilityLoans" ) i.e, 3 level below the Customer, in addition to 'Facility' class above. I've placed my Login.xml query below. Any suggestion would be much helpful. <resultMap id="customersResults" class="com.ge.cf.myaccounts.domain.Customer"> <result property="strCustomerId" column="CUSTOMER_CODE"/> <result property="strCustomerName" column="CUSTOMER_NAME"/> <result property="alDealFacilities" column="CUSTOMER_CODE" select="Login.getDealFacilities"/> </resultMap> <select id="getCustomerDetails" resultMap="customersResults" parameterClass="com.ge.cf.myaccounts.domain.Customer"> SELECT CUSTOMER_CODE,CUSTOMER_NAME FROM STG_INTM_CUSTOMER_INFO WHERE CUSTOMER_CODE=#strCustomerId# </select> <resultMap id="dealFacilityResults" class="com.ge.cf.myaccounts.domain.DealFacility"> <result property="strCustomerId" column="CUSTOMER_CODE"/> <result property="strDealFacilityId" column="DEAL_FACILITY_CODE"/> <result property="strlType" column="DEAL_FACILITY_TYPE"/> <result property="dobCommitAmount" nullValue="0" column="DEAL_FACILITY_COM_AMOUNT"/> <result property="dobBalance" nullValue="0" column="DEAL_FACILITY_BALANCE"/> <result property="alFacilities" column="DEAL_FACILITY_CODE" select="Login.getFacilities"/> </resultMap> <select id="getDealFacilities" resultMap="dealFacilityResults" parameterClass="java.lang.String"> SELECT ci.CUSTOMER_CODE, cf.DEAL_FACILITY_CODE,cf.DEAL_FACILITY_TYPE,cf.DEAL_FACILITY_COM_AMOUNT, cf.DEAL_FACILITY_BALANCE FROM STG_INTM_CUSTOMER_INFO ci ,STG_INTM_DEAL_FACILITY cf WHERE ci.CUSTOMER_CODE = #strCustomerId# AND ci.CUSTOMER_CODE = cf.CUSTOMER_CODE AND cf.IS_DELETED='N' </select> <resultMap id="facilityResults" class="com.ge.cf.myaccounts.domain.Facility"> <result property="strFacilityId" column="FACILITY_CODE"/> <result property="strTypeDescription" column="FACILITY_TYPE_DESC"/> <result property="strDescription" column="FACILITY_DESC"/> <result property="dobCommitAmount" nullValue="0" column="FACILITY_COM_AMOUNT"/> <result property="dobBalance" nullValue="0" column="FACILITY_BALANCE"/> <result property="strBbcId" column="BBC_ID"/> <result property="strStatus" column="STATUS"/> <result property="alLoanInfos" column="FACILITY_CODE" select="Login.getFacilityLoans"/> </resultMap> <select id="getFacilities" resultMap="facilityResults" parameterClass="java.lang.String"> SELECT DISTINCT f.facility_code, f.facility_desc, f.facility_type_desc, f.facility_com_amount, f.facility_balance, f.bbc_id, d.status FROM tmplte_master_status d, (SELECT DISTINCT a.facility_code facility_code, facility_desc facility_desc, facility_type_desc facility_type_desc, facility_com_amount facility_com_amount, facility_balance facility_balance, bbc_id bbc_id, cur_status_id cur_status_id FROM stg_intm_mem_facility a, stg_intm_deal_facility b, tmplte_master c WHERE a.deal_facility_code = #strDealFacilityId# AND a.facility_code = c.facility_code(+) AND a.is_deleted = 'N') f WHERE d.status_id(+) = f.cur_status_id </select> <resultMap id="facilityLoanResults" class="com.ge.cf.myaccounts.domain.LoanInfo"> <result property="strLoanId" column="LOAN_CODE"/> <result property="strLoanIntRateType" column="LOAN_INT_RATE_TYPE"/> <result property="dobBalance" nullValue="0" column="LOAN_BALANCE"/> <result property="strSectionId" column="SECTION_CD"/> <result property="strLoanProductGroup" column="LOAN_PRODUCT_GROUP"/> </resultMap> <select id="getFacilityLoans" resultMap="facilityLoanResults" parameterClass="java.lang.String"> SELECT LOAN_CODE, LOAN_INT_RATE_TYPE, LOAN_BALANCE , SECTION_CD, LOAN_PRODUCT_GROUP FROM STG_INTM_LOAN_INFO WHERE FACILITY_CODE=#strFacilityId# AND trim(SECTION_CD) IS NULL AND IS_DELETED='N' ORDER BY LOAN_CODE </select> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ Information transmitted by this EMAIL is proprietary to iGATE Group of Companies and is intended for use only by the individual or entity to whom it is addressed and may contain information that is privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient of this EMAIL immediately notify the sender at iGATE or [EMAIL PROTECTED] and delete this EMAIL including any attachments _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
