Executing aggregate function in ibatis

amy

Hi All,

I am trying to run the following query in the ibatis :

Code:
SELECT price_list_id,COUNT(*) as customer_id       FROM bill_invoice_line       GROUP BY price_list_id
As you can see that it is returning price_list_id along with the customer numbers in each price list. I have a doubt in mapping this query's output to my resultmap class. My result map class is billing_invoice _line which is price_list_id and customer_id as bean properties.
So my mapping looks like:Code:
lt;resultMap id=quot;billingInvoiceLineResultquot; class=quot;BillingInvoiceLinequot;gt;       lt;result property=quot;billingCustomerquot; column=quot;CUSTOMER_IDquot; select=quot;BillingCustomer.getByIdquot;/gt;       lt;result property=quot;billingPriceListquot; column=quotRICE_LIST_IDquot; select=quot;BillingPriceList.getByIdquot;/gt;
   lt;/resultMapgt;
I just want to know that the way i am mapping it is it right or not?
I am doubtful about the customer_id mapping as it is returning the count(*).Is there any other way out of doing this?
Any help will be greatly appreciated.

Thanks and Regards,
Khushwinder

I am getting the following exception in this:Code:
BillingCustomer.getByIdnot found in the sql map file.

If any body has any idea plz help me out.

this work for myCode:
lt;resultMap id=quot;mapGetNotMenuquot; class=quot;cl.sf.dto.NotMenuDTOquot;gt;
lt;result column=quot;ID_MENUquot; property=quot;id_menuquot;jdbcType=quot;INTEGERquot; columnIndex=quot;1quot; /gt;
lt;result column=quot;SUBMENUquot; property=quot;padrequot;jdbcType=quot;INTEGERquot; columnIndex=quot;2quot; /gt;
lt;result column=quot;TEXTOquot; property=quot;nombrequot;jdbcType=quot;VARCHARquot; columnIndex=quot;3quot; /gt;
lt;/resultMapgt;
and the query
Code:
lt;select id=quot;getNoMenusByRolquot; resultMap=quot;mapGetNotMenuquot; gt;
SELECT   DISTINCT menu.ID_MENU,menu.SUBMENU,menu.TEXTO
FROM   safetower.menu_rol   INNER JOIN safetower.menu ON (menu_rol.ID_MENU = menu.ID_MENU)
where menu.ID_MENU  not in(
SELECT menu.ID_MENU
FROM   safetower.menu_rol   INNER JOIN safetower.menu ON (menu_rol.ID_MENU = menu.ID_MENU)
where id_rol =#idrol#)
lt;/selectgt;
I hope this help  works for you

Thanks a lot for your reply JoseBovet.
But my problem is this:
In my query i am returning count(*) of the coustomer_id wrt to each price list.
and count(*) does not represent any column type.
So this query is a mixture of bean property and non-bean property.
I hope i made myself clear.
and while executing its giving err:
Cause: com.ibatis.sqlmap.client.SqlMapException: There is no statement named BillingCustomer.getById in this SqlMap.

Any idea how to do it?

//DTD SQL Map 2.0//ENquot; quot;dtd/sql-map-2.dtdquot;gt;
lt;sqlMap namespace=quot;BillingCustomerquot;gt;   lt;select id=quot;getByIdquot;gt;       ......   lt;/selectgt;
lt;/sqlMapgt;
lt;?xml version=quot;1.0quot; encoding=quot;UTF-8quot; standalone=quot;noquot;?gt;
lt;!DOCTYPE sqlMap PUBLIC quot;-//iBATIS.com//DTD SQL Map 2.0//ENquot; quot;dtd/sql-map-2.dtdquot;gt;
lt;sqlMap namespace=quot;BillingPriceListquot;gt;   lt;select id=quot;getByIdquot;gt;       ......   lt;/selectgt;
lt;/sqlMapgt;
then you can refer select with namespaceGiven.nameofselect.

Hi m_auro1,

It is already true in my case....

any other idea...

try this
Code:
   lt;resultMap id=quot;billingInvoiceLineResultquot; class=quot;BillingInvoiceLinequot;gt;    lt;result property=quot;billingPriceListquot;   column=quotRICE_LIST_IDquot;    columnIndex=quot;1quot;/gt;    lt;result property=quot;billingCustomerquot;     column=quot;CUSTOMER_IDquot;    columnIndex=quot;2quot; /gt;
   lt;/resultMapgt;
the columnIndex is theprice_list_id -gt; column1customer_id -gt; column2

Code:
SELECT price_list_id,COUNT(*) as customer_id       FROM bill_invoice_line       GROUP BY price_list_id