IBM webMethods Hybrid Integration

IBM webMethods Hybrid Integration

Join this online group to communicate across IBM product users and experts by sharing advice and best practices with peers and staying up to date regarding product enhancements.


#TechXchangePresenter
 View Only
Back to discussions
Expand all | Collapse all

Is it possible to use group by function in jdbc SELECT TEMPLATE adapter service

  • 1.  Is it possible to use group by function in jdbc SELECT TEMPLATE adapter service

    Posted Wed March 16, 2016 11:51 AM

    Hi all,

    I created a jdbc SELECT TEMPLATE adapter service in which i want to use a group by function.
    While i try the below error is displayed.

    =====

    Could not run ‘Test’
    com.wm.pkg.art.error.DetailedServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service TestPack.adapterServices.jdbc:Test.
    [ADA.1.316] Cannot execute the SQL statement "SELECT DISTINCT t1.CREATED_BY,t1.CREATED_BY_ID,SUM(t1.QUANTITY) FROM TBC_ENQ_PRD_DTLS t1 WHERE (t1.STATUS = 1) ". "
    (42000/937) ORA-00937: not a single-group group function
    "
    ORA-00937: not a single-group group function

    Is it possible by using SELECT TEMPLATE adapter service or should i use a CUSTOM or DYNAMIC adapter.


    #Adapters-and-E-Standards
    #Integration-Server-and-ESB
    #webMethods


  • 2.  RE: Is it possible to use group by function in jdbc SELECT TEMPLATE adapter service

    Posted Wed March 16, 2016 02:06 PM

    Hi Daniel,

    as you are selecting with DISTINCT option grouping will not work.

    Can you provide a screen shot of the adapter service as well as a sample of the expetced result?

    Regards,
    Holger


    #Adapters-and-E-Standards
    #Integration-Server-and-ESB
    #webMethods


  • 3.  RE: Is it possible to use group by function in jdbc SELECT TEMPLATE adapter service

    Posted Wed March 16, 2016 11:36 PM

    Hi Daniel,
    I believe the SQL syntax which you are trying is not correct. You can check this by running the same query from any of the SQL client tools.

    Use Custom SQL template for utilizing GROUP BY clause.

    Thanks,
    Manohara


    #Adapters-and-E-Standards
    #Integration-Server-and-ESB
    #webMethods


Related Content