Getting aggregates in single SQL

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
Report Content as Inappropriate

Getting aggregates in single SQL

To all
I am new to the forum. We have been trying to populate an object model in java where Porct -> prod Details -> multiple metrics. Corresponding tables for each exist too. My understanding was, if I define as given in below example, it will work, but somehow this approach is failing. What I get returned in m *n number of rows. i.e. If there 5 products- > 5*4 (20) prod details - > 20 *50 (1000) metrics , I get 1000 Products in an arraylist, one for each metric. I actually need 5 products, each containing list of prod details which contains list of metrics. but it is failing. I followed all the requirements of code in this example (including outer join). but no use.. Any help is appreciated... Thanking in advance,

This example I followed and approach fails...
<sqlMap namespace="ProductCategory">

  <resultMap id="Category-result" class="Category" groupBy="Id">
    <result property="Id" column="CAT_ID"/>
    <result property="Description" column="CAT_DESCRIPTION"/>
    <result property="ProductList" resultMapping="ProductCategory.Product-result"/>

  <resultMap id="Product-result" class="Product">
    <result property="Id" column="PRD_ID"/>
    <result property="Description" column="PRD_DESCRIPTION"/>


  <statement id="SelectCategory" parameterClass="int" resultMap="Category-result">
    from CATEGORY C
    left outer join PRODUCT P
    on C.CAT_ID = P.PRD_CAT_ID
    where CAT_ID = #value#

When you call...

IList myList = sqlMap.QueryForList("SelectCategory", 1002);