Getting aggregates in single SQL

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

Getting aggregates in single SQL

sbrahme
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">
<resultMaps>

  <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>

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

<statements>

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




When you call...

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