none
How is it possible a sum Group by select using wcf sql oracle adapter? RRS feed

  • Question

  • Dear all,

    I  need to do a Group by query to an oracle db server. 

    I reach to make simple query, but if I use a more complex query whit sum and group by I have not good results. 

    This is the message I try to send to the adapter port: 

    <ns0:Select xmlns:ns0="http://Microsoft.LobServices.OracleDB/2007/03/DBNAME/Table/INVOICES">
      <ns0:COLUMN_NAMES> ACCOUNT ,  SUM (AMOUNT)</ns0:COLUMN_NAMES>
      <ns0:FILTER> DOCUMENT_ID IN (20000001, 20000002, 20000003) GROUP BY (ACCOUNT)
    </ns0:FILTER>
    </ns0:Select>

    The result that I see in the administration console is like this:

    It will be retransmitted after the retry interval specified for this Send Port.

    Details:"Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-00979: not a GROUP BY expression --->

    Oracle.DataAccess.Client.OracleException: ORA-00979: not a GROUP BY expression



    What's wrong?

    Thank you in Advance

    Sunday, November 8, 2015 8:03 PM

Answers

  • Only a limited number of keywords are supported by the WCF Oracle Adapter for Table Operations.

    If you need anything more complex than a SELECT with a WHERE clause, use a Stored Procedure.  There are no limits on what you can to in the PL-SQL code.

    • Proposed as answer by Maheshkumar S Tiwari Monday, November 9, 2015 7:44 AM
    • Marked as answer by Angie Xu Monday, November 16, 2015 3:02 AM
    Monday, November 9, 2015 4:15 AM