Sql aggregation function with Axl

Version 1
    This document was generated from CDN thread

    Created by: Gionata Navarra on 22-06-2010 03:50:40 PM
    Hi all.
     
    I've used Axl on CUCM 7.0 to retrieve data about EndUsers.
    With a simple query I obtained informations requested but trying to aggregate results with GROUP_CONCAT I have following exception:
     
    javax.xml.ws.soap.SOAPFaultException: Routine (group_concat) can not be resolved.
        at com.sun.xml.ws.fault.SOAP11Fault.getProtocolException(SOAP11Fault.java:189)
        at com.sun.xml.ws.fault.SOAPFaultBuilder.createException(SOAPFaultBuilder.java:130)
        at com.sun.xml.ws.client.sei.SyncMethodHandler.invoke(SyncMethodHandler.java:119)
        at com.sun.xml.ws.client.sei.SyncMethodHandler.invoke(SyncMethodHandler.java:89)
        at com.sun.xml.ws.client.sei.SEIStub.invoke(SEIStub.java:118)
     
    this is the query:
     
    "SELECT EU.userid, D.name, GROUP_CONCAT(NP.DnOrPattern) from Enduser EU, Device D, DeviceNumPlanMap DNPM, NumPlan NP WHERE D.fkEnduser==EU.pkid AND D.pkid==DNPM.fkDevice AND DNPM.fkNumPlan==NP.pkid GROUP BY EU.pkid, D.pkid"
     
    I've added GROUP_CONCAT to obtain a single instance of each EndUser with a list of multiple lines, and not an instance of EndUser for each line.
     
    E.g: User A with 3 lines
    Expected res:
     
    User A --> line1, line2, line3
     
    and not:
     
    User A --> line 1
    User A --> line 2
    User A --> line 3
     
    Is there any other way to aggregate fields?
     
    Thank you
    Gionata Navarra

    Subject: RE: Sql aggregation function with Axl
    Replied by: David Staudt on 22-06-2010 05:45:56 PM
    As far as I can tell GROUP_CONCAT is not supported by the UCM database engine, Informix Dynamic Server 10.
     
    SQL reference for IDS here: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqlr.doc/sqlrmst03.htm
     
     

    Subject: RE: Sql aggregation function with Axl
    Replied by: Gionata Navarra on 02-07-2010 06:56:44 AM
    thank you.