Need help on nested N + 1 query

Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 11765 invoked from network); 27 Nov 2006 17:39:46 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 27 Nov 2006 17:39:46 -0000 Received: (qmail 41056 invoked by uid 500); 27 Nov 2006 17:39:52 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 40880 invoked by uid 500); 27 Nov 2006 17:39:51 -0000 Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user-java@ibatis.apache.org Delivered-To: mailing list user-java@ibatis.apache.org Received: (qmail 40869 invoked by uid 99); 27 Nov 2006 17:39:51 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 27 Nov 2006 09:39:51 -0800 X-ASF-Spam-Status: No, hits=2.0 required=10.0 tests=HTML_MESSAGE,UPPERCASE_25_50 X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: local policy) Received: from [202.91.136.20] (HELO igatemx3.igate.com) (202.91.136.20) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 27 Nov 2006 09:39:40 -0800 X-SEF-Processed: 5_0_0_910__2006_11_27_23_09_10 Received: from Bangalore BHD [192.168.112.111] by igatemx3 - SurfControl E-mail Filter (5.2.0); Mon, 27 Nov 2006 23:09:10 +0530 Received: from igtechnexc01.igatecorp.com ([192.168.177.113]) by igteblrowa01.igatecorp.com with Microsoft SMTPSVC(6.0.3790.1433); Mon, 27 Nov 2006 23:05:56 +0530 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C7124A.7F1694E3" Subject: Need help on nested N + 1 query Date: Mon, 27 Nov 2006 23:09:15 +0530 Message-ID: <8CA0DB1C6F90AD4FA258843E456E0B5304D2FE63@igtechnexc01.igatecorp.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Need help on nested N + 1 query Thread-Index: AccSSvT3Cmli2j/OQC+L1q29qPD6+g== From: "Tamilselvan Radha Krishnan" To: X-OriginalArrivalTime: 27 Nov 2006 17:35:56.0915 (UTC) FILETIME=[7EE4C430:01C7124A] X-Virus-Checked: Checked by ClamAV on apache.org This is a multi-part message in MIME format. ------_=_NextPart_001_01C7124A.7F1694E3 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi,=0D=0AI've written a N + 1 query in Ibatis such a way that it will retur= n the=0D=0Anested results as below :=0D=0A=0D=0A Customer - First object=0D= =0A DealFacility - An array list of objects within Customer=0D=0A= Facility - An array list of objects within=0D=0ADealFa= cility=0D=0A LoanInfo - An array list of objects wit= hin=0D=0AFacility=0D=0A =20=0D=0A=0D=0AThe Customer object is having a 's= trCustomerId' as an attribute. I need=0D=0Ato use this 'strCustomerId' in = the WHERE clause of the query used for=0D=0Apopulating 'LoanInfo' (=20=0D=0A =0D=0A =0D=0A =0D=0A=0D=0A =0D=0A =20=0D=0A =20=0D=0A =20=0D=0A =20=0D=0A= =20=0D=0A =09=20=0D=0A =09=20=0D=0A =09 =0D=0A =09= =0D=0A =0D=0A =0D=0A =20=0D=0A =20=0D=0A= =09=0D=0A =20=0D=0A =09 =20=0D= =0A =09=09SELECT DISTINCT f.facility_code, f.facility_desc,=0D=0Af.facilit= y_type_desc,=0D=0A f.facility_com_amount, f.facility_balance= , f.bbc_id,=0D=0Ad.status=0D=0A FROM tmplte_master_status d,=0D=0A = (SELECT DISTINCT a.facility_code facility_code,=0D=0A = facility_desc facility_desc,=0D=0A = facility_type_desc facility_type_desc,=0D=0A = facility_com_amount=0D=0Afacility_com_amount,=0D=0A = facility_balance facility_balance,=0D=0A = bbc_id bbc_id, cur_status_id=0D=0Acur_status_id=0D= =0A FROM =09=09=09 stg_intm_mem_facility a,=0D=0A = stg_intm_deal_facility b,=0D=0A = tmplte_master c=0D=0A WHERE =09=09=09 a.deal_fa= cility_code =3D=0D=0A#strDealFacilityId#=0D=0A AND =09=09=09= a.facility_code =3D=0D=0Ac.facility_code(+)=0D=0A AND =09=09= =09 a.is_deleted =3D 'N') f=0D=0A WHERE d.status_id(+) =3D f.cur_sta= tus_id=0D=0A=0D=0A=09=0D=0A=09=0D=0A=09=0D=0A=09 =0D= =0A =09=0D=0A= =0D=0A =20=0D=0A =0D=0A =09=0D=0A= =09=0D=0A =09=0D=0A =09=0D=0A=0D=0A=0D=0A=0D=0A=0D=0A=0D= =0A=0D=0A=0D=0A_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ = _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _=0D= =0A=0D=0AInformation transmitted by this EMAIL is proprietary to iGATE Grou= p of Companies and is intended for use only by the individual=20=0D=0Aor en= tity to whom it is addressed and may contain information that is privileged= , confidential, or exempt from disclosure under=20=0D=0Aapplicable law. If = you are not the intended recipient of this EMAIL immediately notify the sen= der at iGATE or mailadmin@igate.com=20=0D=0Aand delete this EMAIL including= any attachments=0D=0A_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _= _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ = _ _ _=0D=0A ------_=_NextPart_001_01C7124A.7F1694E3 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable =0D=0A=0D=0A=0D= =0A=0D=0A=0D=0ANeed help on nested N + 1 query =0D=0A=0D=0A=0D=0A=0D=0A=0D=0A

Hi,

=0D=0A=0D=0A

I’ve written a N + 1 query in Ibatis such a wa= y that it will return the nested results as below :

=0D=0A=0D= =0A

=0D=0A=0D=0A

Customer –<= /SPAN> First object

=0D=0A=0D=0A

&nb= sp; DealFacility – An array list of objects within Customer=

=0D=0A=0D=0A

&nb= sp; Facility – An array list of objects within DealFacil= ity=0D=0A=0D=0A

= LoanInfo= – An array list of objects within = Facility

=0D=0A=0D=0A

The Customer object = is having a ‘st= rCustomerId<= FONT SIZE=3D2 FACE=3D"Arial">’ as an attribute.&nbs= p; I need to use this ‘strCustomer= Id’ in the WHER= E clause of the query used for populating ‘LoanInfo’= (<select id=3D"getFa= cilityLoans" ) <= /SPAN> i.e, 3 level below the Customer, in addition t= o ‘FacilityR= 17; class above.

=0D=0A=0D=0A

I’<= SPAN LANG=3D"en-us">ve placed my Login.xml qu= ery below. Any suggestion would be much helpful.

=0D=0A=0D=0A

=0D=0A
=0D=0A=0D=0A

<resultM= ap id=3D"customersResults" class=3D"com.ge.cf.myaccounts.dom= ain.Customer">

=0D=0A=0D=0A

= ; <result property= =3D"strCustomerId" &nb= sp; col= umn=3D"CUSTOMER_CODE"/>

=0D=0A=0D=0A

&n= bsp; <result property=3D"strCustomerName" &n= bsp; column=3D"= ;CUSTOMER_NAME"/>

=0D=0A=0D=0A

&= nbsp; <result property=3D"alDealFacilities" &= nbsp; column=3D"CUSTO= MER_CODE" select=3D&q= uot;Login.getDealFacilities"/> &= nbsp; &nbs= p;

=0D=0A=0D=0A

</resultMap&g= t;

=0D=0A=0D=0A

<selec= t id=3D"getCustomerDetails" resultMap=3D"customersResu= lts" parameterClass=3D"com.ge.cf.myaccounts.domain.Customer= ">

=0D=0A=0D=0A

<= FONT SIZE=3D2 FACE=3D"Arial"> SELECT&n= bsp; CUSTOMER_CODE,CUSTOMER_NAME

=0D=0A=0D=0A

&= nbsp; FROM = ; STG_INTM_CUSTOMER_INFO

=0D=0A=0D=0A = WH= ERE CUSTOMER_CODE=3D#strCustomerId# &nb= sp; = ;

=0D=0A=0D=0A

</select>

=0D=0A=0D=0A

=0D=0A=0D= =0A

&n= bsp;

=0D=0A=0D=0A

<resultMap= id=3D"dealFacilityResults" class=3D"com.ge.cf.myaccounts.do= main.DealFacility">

=0D=0A=0D=0A

&nb= sp;

=0D=0A=0D=0A

&n= bsp; <result property=3D"strCustomerId" &nbs= p; = ; = column=3D"CUSTOMER_CODE&qu= ot;/>

=0D=0A=0D=0A

&nbs= p; <result property=3D"strDealFacilityId" &n= bsp; &nb= sp; = ; column=3D"DEAL_FACILITY_C= ODE"/>

=0D=0A=0D=0A

= <result property=3D"strlType" = &= nbsp; &nb= sp; &nbs= p; column=3D"DEAL_FACILITY_TYPE"/>

=0D=0A=0D=0A

=0D=0A=0D=0A

<= SPAN LANG=3D"en-us"> &= nbsp; <result property=3D"dobBalance" = ; nullValue=3D"0"= ; column=3D"DEAL_FACILITY_BA= LANCE"/>

=0D=0A=0D=0A

&nb= sp; <result property=3D"alFacilities" &= nbsp; &nbs= p; column=3D"DEAL_FACI= LITY_CODE" select=3D"Login.getFacilities"/> &n= bsp;

=0D=0A=0D=0A

</resultMap>

=0D=0A=0D=0A

= <select id=3D"getDealFacilities" resultMap=3D"deal= FacilityResults" parameterClass=3D"java.lang.String"&g= t;

=0D=0A=0D=0A

SELECT

=0D=0A=0D=0A

&n= bsp; &nbs= p; = ; &= nbsp; ci.CUSTOMER_CODE, cf.DEAL_FACILITY_CODE,cf.DEAL_FACILITY_TYPE,cf.DEAL= _FACILITY_COM_AMOUNT,cf.DEAL_FACILITY_BALANCE

=0D=0A=0D=0A=

= ; = FROM STG_INTM_CUSTO= MER_INFO ci ,STG_INTM_DEAL_FACILITY cf

=0D=0A=0D=0A

= ; WHERE = ci.CUSTOMER_CODE =3D #str= CustomerId#

=0D=0A=0D=0A

AND = ci.CUSTOMER_CODE =3D cf.CUSTOMER_CODE

=0D= =0A=0D=0A

&n= bsp; &nbs= p; AND cf.IS_DELETED=3D= 'N' =0D=0A=0D=0A

</select>

=0D= =0A=0D=0A

&n= bsp;

=0D=0A=0D=0A

=0D=0A=0D=0A &= nbsp; <resultMap id=3D"facilityResults" class=3D"co= m.ge.cf.myaccounts.domain.Facility">

=0D=0A=0D= =0A

&n= bsp; <result property=3D= "strFacilityId" &= nbsp; &= nbsp; &n= bsp; column=3D"FACILITY_CODE"/>&= nbsp; <= /FONT>

=0D=0A=0D=0A

=0D=0A=0D=0A

<= SPAN LANG=3D"en-us"> = <result property=3D"strDescription" &= nbsp; &= nbsp; &n= bsp; column=3D"FACILITY_DESC"= />

=0D=0A=0D=0A

=0D=0A=0D=0A

= <result property=3D"dobBalance" &nbs= p; nullValue=3D"0" column=3D&= quot;FACILITY_BALANCE"/>

=0D=0A=0D=0A

= = <result property=3D"strBbcId" &= nbsp; &= nbsp; &n= bsp; col= umn=3D"BBC_ID"/>

=0D=0A=0D=0A

&= nbsp; <result property=3D"strStatus" &= nbsp; &n= bsp; &nb= sp; &nbs= p; column=3D"STATUS"/> &nbs= p;

=0D=0A=0D=0A

&nbs= p; <result property=3D"alLoanInfos" &nb= sp; = column=3D"FACIL= ITY_CODE" select=3D"Login.getFacilityLoans"/> = &nb= sp; = &nb= sp;

=0D=0A=0D=0A

<= FONT SIZE=3D2 FACE=3D"Arial"> &nb= sp;

=0D=0A=0D=0A

&nb= sp; </resultMap>

=0D=0A=0D=0A

&nb= sp;

=0D=0A=0D=0A

&nb= sp; <select id=3D"getFacilities" resultMap=3D"facil= ityResults" parameterClass=3D"java.lang.String">&nb= sp;

=0D=0A=0D=0A

&n= bsp; SELECT DISTINCT f.facility_code, f.facility_de= sc, f.facility_type_desc,

=0D=0A=0D=0A

&= nbsp; f.facility_com_= amount, f.facility_balance, f.bbc_id, d.status

=0D=0A=0D=0A=

= ; FROM tmplte_master_status d,<= /P>=0D=0A=0D=0A

= ; (SELECT DISTINCT a.facility_code facility_code,<= /SPAN>

=0D=0A=0D=0A

&nbs= p; &= nbsp; facility_desc facilit= y_desc,

=0D=0A=0D=0A

&= nbsp; &nbs= p; facili= ty_type_desc facility_type_desc,

=0D=0A=0D=0A

= = &nb= sp; = facility_com_amount facility_com_amount,=0D=0A=0D=0A

= &nb= sp; facility_balance facility_bal= ance,

=0D=0A=0D=0A

&nb= sp; = bbc_id b= bc_id, cur_status_id cur_status_id

=0D=0A=0D=0A

&nbs= p; = FROM &n= bsp; stg_intm_mem_facility a,

=0D=0A=0D= =0A

&n= bsp; = ; &n= bsp; stg_intm_deal_facility b,<= /P>=0D=0A=0D=0A

= ; &n= bsp; tmplte_master c

=0D=0A=0D=0A

&nb= sp; WHERE = ; a.deal_facility_co= de =3D #strDealFacilityId#

=0D=0A=0D=0A

= AND &nbs= p; = ; a.facility_code =3D c.facility_code(+)

=0D=0A=0D=0A

&= nbsp; AND &nbs= p; a.is_delete= d =3D 'N') f

=0D=0A=0D=0A

W= HERE d.status_id(+) =3D f.cur_status_id

=0D=0A=0D=0A

</select>

=0D=0A=0D=0A

&n= bsp;

=0D=0A=0D=0A

=0D=0A=0D=0A=

= ; <result property=3D"strLoanId" = ; &= nbsp; &n= bsp; &nbs= p; column=3D"LOAN_CODE"/>

=0D=0A=0D=0A<= P DIR=3DLTR> = <result property=3D"strLoanIntRateTy= pe" &nbs= p; = ; column=3D"LOAN_INT_RATE_TYPE&quo= t;/>

=0D=0A=0D=0A

<re= sult property=3D"dobBalance" null= Value=3D"0" column=3D"LOAN_BALANCE&= quot;/>

=0D=0A=0D=0A

&nb= sp; <result property=3D"strSectionId" = &= nbsp; &nb= sp; &nbs= p; column=3D"SECTION_CD"/>

=0D=0A=0D= =0A

&n= bsp; <result property=3D"strLoanProdu= ctGroup" = &= nbsp; column=3D"LOAN_PRODUCT_GROUP= "/>

=0D=0A=0D=0A

</resultMap>

=0D=0A=0D=0A

=0D= =0A=0D=0A

&n= bsp; <select id=3D"getFacilityLoans&= quot; resultMap=3D"facilityLoanResults" parameterClas= s=3D"java.lang.String">

=0D=0A=0D=0A = SELECT&= nbsp; LOAN_CODE, LOAN_INT_RATE_TYPE, LOAN_BALANCE , SECTION_CD, LOAN_PRODUC= T_GROUP

=0D=0A=0D=0A

= ; FROM STG_INTM= _LOAN_INFO

=0D=0A=0D=0A

= &n= bsp; WHERE FACILITY_CODE=3D= #strFacilityId#

=0D=0A=0D=0A

&nb= sp; AND &n= bsp; trim(SECTION_CD) IS NULL

=0D=0A=0D=0A

&= nbsp; AND = IS_DELETED=3D'N'

=0D=0A=0D=0A

&= nbsp; ORD= ER BY LOAN_CODE

=0D=0A=0D=0A

<= ;/select>=

=0D=0A=0D=0A

=0D=0A
=0D=0A
=0D=0A
=0D=0A
=0D=0A=0D=0A

=0D=0A=0D=0A

_ _ _ _ _&nb= sp;_ _ _ _ _ _ _ _ _ _ _&= nbsp;_ _ _ _ _ _ _ _ _ _ = _ _ _ _ _ _ _ _ _ _ _&nbs= p;_ _ _ _ _ _ _ _ _ _ _&n= bsp;_ _ _ _ _ _ _ _ _ _ _= _ _ _ _ _ _ _ _
=0D=0A
=0D=0A= Information transmitted by this EMAIL is prop= rietary to iGATE Group of Companies and = is intended for use only by the individu= al
=0D=0Aor entity to whom it is add= ressed and may contain information that is&nb= sp;privileged, confidential, or exempt from disclo= sure under
=0D=0Aapplicable law. If you a= re not the intended recipient of this EM= AIL immediately notify the sender at iGATE&nb= sp;or mailadmin@igate.com
=0D=0Aand delete this&nbs= p;EMAIL including any attachments
=0D=0A_ _ _&n= bsp;_ _ _ _ _ _ _ _ _ _ _= _ _ _ _ _ _ _ _ _ _ = ;_ _ _ _ _ _ _ _ _ _ _&nb= sp;_ _ _ _ _ _ _ _ _ _ _&= nbsp;_ _ _ _ _ _ _ _ _ _ = _ _ _ _ _ _ _ _ _ _ _

=0D=0A ------_=_NextPart_001_01C7124A.7F1694E3--