locked
SQL Connector - aggregate query RRS feed

  • Question

  • Hello,

    It's really frustrating trying trying to find out the correct way to use various options in Logic Apps connectors:

    I couldn't find any specific documentation about syntax used for Azure Logic apps Odata aggregate queries - do you have any definitive documentation listing all the possibilities to use in the various filter, select, aggregate, extension boxes?

    This is the equivalent sql query I am trying to recreate in the connector:

    Select top 10 
    CC_Person_Id
    , count(sales_id) d1
    From FVLOnline_CC.Sale
    Group by
    CC_Person_Id
    Order by 
    d1 desc

    After much googling I tried this OData syntax, but it came up with an error:

    groupby((CC_Person_Id), aggregate(sales_id with count as d))

        "body": {
            "status"400,
            "message""Unrecognized with 'count' at '48' in 'groupby((CC_Person_Id), aggregate(sales_id with count as d))'.\r\n     inner exception: Unrecognized with 'count' at '48' in 'groupby((CC_Person_Id), aggregate(sales_id with count as d))'.\r\nclientRequestId: 551e764e-48a8-41f6-897d-9bc734adddd8",
            "error": {
                "message""Unrecognized with 'count' at '48' in 'groupby((CC_Person_Id), aggregate(sales_id with count as d))'.\r\n     inner exception: Unrecognized with 'count' at '48' in 'groupby((CC_Person_Id), aggregate(sales_id with count as d))'."
            },
            "source""sql-uks.logic-ase-uksouth.p.azurewebsites.net"
        }


    Then after looking Logic App SQL connector page, it said countdistinct was only valid syntax closest to what I wanted. When I tried this aggregate I got the following error:

    groupby((CC_Person_Id), aggregate(sales_id with countdistinct as d1))

    "error": {

            
                "code"502,
                "source""logic-apis-uksouth.azure-apim.net",
                "clientRequestId""968f2b8c-8f09-4e44-b075-ff42b979ff87",
                "message""BadGateway",
                "innerError": {
                    "status"502,
                    "message""Unexpected Exception : System.NotImplementedException: The method or operation is not implemented.\r\n   at Microsoft.Azure.Connectors.Common.Mashup.Extensions.QueryOptionsExtensions.ToApplyQueryString(IEnumerable`1 transformationNodes) in X:\\bt\\1070882\\repo\\src\\source\\common\\Mashup\\Extensions\\QueryOptionsExtensions.cs:line 162\r\n   at Microsoft.Azure.Connectors.Common.Mashup.Extensions.QueryOptionsExtensions.ToMashupString(QueryOptions options, Int64 pageSize) in X:\\bt\\1070882\\repo\\src\\source\\common\\Mashup\\Extensions\\QueryOptionsExtensions.cs:line 38\r\n   at Microsoft.Azure.Connectors.Common.Mashup.Adapter.MashupAdapter.<ListItemsStreamAsync>d__23.MoveNext() in X:\\bt\\1070882\\repo\\src\\source\\common\\Mashup\\Adapter\\MashupAdapter.cs:line 515\r\n     inner exception: The method or operation is not implemented.\r\nclientRequestId: 968f2b8c-8f09-4e44-b075-ff42b979ff87",
                    "error": {
                        "message""Unexpected Exception : System.NotImplementedException: The method or operation is not implemented.\r\n   at Microsoft.Azure.Connectors.Common.Mashup.Extensions.QueryOptionsExtensions.ToApplyQueryString(IEnumerable`1 transformationNodes) in X:\\bt\\1070882\\repo\\src\\source\\common\\Mashup\\Extensions\\QueryOptionsExtensions.cs:line 162\r\n   at Microsoft.Azure.Connectors.Common.Mashup.Extensions.QueryOptionsExtensions.ToMashupString(QueryOptions options, Int64 pageSize) in X:\\bt\\1070882\\repo\\src\\source\\common\\Mashup\\Extensions\\QueryOptionsExtensions.cs:line 38\r\n   at Microsoft.Azure.Connectors.Common.Mashup.Adapter.MashupAdapter.<ListItemsStreamAsync>d__23.MoveNext() in X:\\bt\\1070882\\repo\\src\\source\\common\\Mashup\\Adapter\\MashupAdapter.cs:line 515\r\n     inner exception: The method or operation is not implemented."
                    },
                    "source""sql-uks.logic-ase-uksouth.p.azurewebsites.net"
                }

    Can somebody tell what the correct syntax should be - the trial and error method to using Azure components is driving me crazy?

    Monday, May 25, 2020 12:38 PM

All replies